Microsoft Excel Help?

[Daruya]

Since Elgee got some help by posting a request here, I thought I'd do the same!

 

I have a spreadsheet in which I am tracking maintenance data of a specific component.  The component undergoes maintenance every 2.5 years so a great many of the serial numbers have duplicate entries.  I want to be able to input a formula that will calculate the number of UNIQUE entries. IOW, if my list is:

 

123

123

123

123

456

456

789

789

789

 

the formula would return 3 because, although there are 9 entries, there are only 3 unique numbers.

 

My spreadsheet has over 7000 entries so I shudder at the thought of trying to count them manually. There HAS to be a way to have Excel do it for me!

 

I know how to COUNTIF for a specific condition (I do so by failure mode already) but if someone can point me in the right direction for counting the number of unique components, it would save me ever so much time!

 

Pretty please with cookies on top?

[WildTaltos]

select/highlight the range of numbers, go to "Advanced" under the Sort/Filter option in Data, once the box of that pops up, select "copy to another location" , select an unused column where you want the results to go in the "Copy to:" option, then check "UNIQUE RECORDS ONLY" and then hit ok. that will pull out all the unique numbers in the column you copied them to, then at the bottom of that column (or wherever else on the spreadsheet), use the ROWS formula to calculate how many of those unique entries there are. 

 

=ROWS(beginning cell of unique numbers:ending cell of unique numbers)

 

So if there are only 7 unique numbers pulled out like in the test i tried it with, once you enter the formula it should say 7.

[Daruya]

Thanks! I'll give that a try once I pull out all the components that are currently not in RFI condition (ready for issue). 

[Daruya]

Hey, that works quite well. I don't even need to do the row count, necessarily, because all I have to do is look at the row the last # is in and subtract 1 for the column header.

 

It would be nice if I could do a count type of function where I wouldn't have copy the unique #s to another column but I'll take what I can get!

 

Thanks, Taltos!

[Clovdyx]

Try this function:

 

=SUM(IF(FREQUENCY(A1:B10,A1:B10)>0,1))

 

This is for two columns of data, with numbers in cells A1 - A10 and B1 - B10.  Using the example data set you provided, it would be A1:A9,A1:A9, unless you needed a second row.  Of course, you can make your dataset as expansive as you want in the formula and affect nothing, if the columns are blank (or strings).

 

For example:

 

 

As you can see, it returns a total of three for the sample dataset you provided and ignores everything in the B column.  Adding a number to the bottom of column B increases the freq. sum in A12.

 

 

This is with the same formula in A12, of course.  I just clicked off the cell before my second screenshot.  

 

I hope this helps.  If it doesn't work quite right, let me know and I'll see if I can tweak it for you.

[Daruya]

I'll have to try it tomorrow as I've already left work.

 

My spreadsheet has multiple columns.

 

Columns include S/N, Lot #, P/F, Point of Failure, Date, Report #, Failure Mode (I populate the last cell based on the PoF cell). 

 

Since the first 2 columns both have numbers, will it still work? Or do I need to rearrange the spreadsheet to move the Lot # column to the right of the P/F (pass/fail) column?

[Clovdyx]

Just specify the arrays for what you want to count.  If you only want to count the first column, for example, it should just be A1:A7000, A1:A7000 (or whatever the last row you want to count is).  If everything you want is in Column C, replace A with C.  

[Clovdyx]

[Daruya]

Here is another wrinkle . . . several of the units had missing or illegible serial numbers so are annotated with "Unknown" or "None."

 

I can distinguish between the None units by using None1, None2, etc., but I don't think they'll get picked up, will they? (This issue was probably alleviated via assigning them replacement serial numbers but I have no way of knowing what # they were assigned; I may just toss these from the mix for this exercise as I'm mostly trying to get a nearly accurate count and these 6 won't skew the result too much.) 

 

 BTW, some serial numbers have letters in them (e.g., 231R, 6160R, etc.); will those get picked up since they are not strictly numbers?

 

I did try the formula you suggested but with 6700 rows, the return was only 59 and I know there are more than 59!  Using the method Taltos suggested, the number was 3134.

[Daruya]

This is the formula I was using:

 

=SUM(IF(FREQUENCY(A2:A6700,A2:A6700)>0,1))

 

(Note: the first row contains the column headings so I started with A2)

[Daruya]

I went to this article:

 

https://support.office.com/en-sg/article/count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273

 

and tried using their formula (changing the range to mine) and it returned either a VALUE error or N/A error, depending on which one I used.

 

I did get the answers I needed from the method Taltos suggested but I really would prefer using a formula because when new data is added, it'll automatically recalculate for me with only minor tweaks to the formula (to extend the range).

[Clovdyx]

Here is another wrinkle . . . several of the units had missing or illegible serial numbers so are annotated with "Unknown" or "None."

 

I can distinguish between the None units by using None1, None2, etc., but I don't think they'll get picked up, will they? (This issue was probably alleviated via assigning them replacement serial numbers but I have no way of knowing what # they were assigned; I may just toss these from the mix for this exercise as I'm mostly trying to get a nearly accurate count and these 6 won't skew the result too much.) 

 

 BTW, some serial numbers have letters in them (e.g., 231R, 6160R, etc.); will those get picked up since they are not strictly numbers?

 

I did try the formula you suggested but with 6700 rows, the return was only 59 and I know there are more than 59!  Using the method Taltos suggested, the number was 3134.

 

No, the formula is only designed to count numbers.  So while 4, 78643623214, and 42.4444 would get picked up, 231R will not.  If you have alphanumeric characters that you're trying to count, such as the serials 231R and 6160, try this formula instead.  

 

=SUM(IF(FREQUENCY(MATCH(A2:A6700,A2:A6700,0),MATCH(A2:A6700,A2:A6700,0))>0,1))

 

 

As you can see, it returns 7 unique values - "doesn't" "really2" "matter" "what4" "Iwritehere" "4" and "5".  The only issue is there MUST be something in each cell for your data range.  In other words, if you put A2:A6700, all 6,699 cells must not be empty.  You can put a serial number, a period, a blank space...whatever you'd like...but it cannot be blank or you'll get a #NAME? error.

[Clovdyx]

Probably should have read your more recent post before responding, but as indicated above, the error is likely caused by blank cells. 

[Daruya]

I tried the one formula on the smaller spreadsheet and it worked so odds are there is a blank cell or two in the larger spreadsheet. I'll try it again on Monday.  Thanks for the help!

[Daruya]

It worked!  Yay!

 

And now that I have the formula in the spreadsheet, whenever I add new data, all I have to do is ensure the range is updated in the formula (although, I don't anticipate many serial numbers making their way back into the pool until after repairs are initiated which will require procurement of lower level components/assemblies).

 

THANK YOU, Taltos and Clovdyx, for your help!