Daruya

Microsoft Excel Help?

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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:

 

28DTlUz.png

 

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.

 

3MlTBd3.png

 

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.  

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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))

 

XykhHy9.png

 

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.

Edited by Clovdyx

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Posts

    • I just did a practice Hurricane on Turin and I think I killed him.   So... Green Vs Blue?   Lol
    •   That's the only one I could find too, though to be fair I dont see how she could have a read outside the 3 of us besides Darthe, who she has also covered.   You mention BG. You've been able to garner a read from what he's said so far? Cuz I sure haven't. 
    • Oh look   https://dmbt.000webhostapp.com/Archived/doresaidin.php   I found the links in an old Tainted Times   There really needs to be an edit to the links in the off-site board. Also the links in the how-to threads.   But it seems to be alive. Is this he version where you only put in your numbers for the elements you are using?
    • I meant different sides of reading dice, not opposing points.   So dice is town iyo because of that phrase alone?   I haven't seen him use terminology like that in either alignment, and don't see how it attributes to 99.999999996% town.   Eldrick2 
    • This is the only thing i found. It takes care of me. Which I addressed already. Your using an old meta for your vote. Weak.    I asked for your reads on other players not named dice/eldrick. If you use meta what do you think of the players who seem unusually quiet right now? Could scum be using the opportunity to lay low? How bout BG he had some posts since this one what are you thoughts on them? How bout Darthe he did as well.   Basically dont say im ignoring posts and then act confused when you do the same?