Need a forumula for counting a list of servers, but not the duplicates
Hi all,
I have a tight deadline and was wondering if anyone knows how to write a forumula for me.
I have a spreadsheet containing 62,293 lines of data. In column A I have the names of servers. I need a count of these servers, but some are duplicates. Obviously I don't want to count them. Is there a simple way to do this? And, yes I have filtered this sheet to only show data for the month of September, but that shouldn't make any difference.
Try this array formula =SUM(IF(MATCH(List1, List1, 0)>=(ROW(List1)-MIN(ROW(List1))+1), 1, 0)) where your range is named List1
Pepe,
So I tried your formula using the following because my range is A2:A62293:
=SUM(IF(MATCH(A2:A62293, A2:A62293, 0)>=(ROW(A2:A62293)-MIN(ROW(A2:A62293))+1), 1, 0))
I get the following error: #VALUE!
Did you commit with Ctrl+Shift+Enter ?Originally Posted by Stormy4757
On the ribbon click on Data, there is an icon for remove duplicates. Use this and then you will have your count of rows. If you need to keep the original data and the new data, save as a new file.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Alan, I tried your suggestion, but the remove duplicates is greyed out.
Suggest you post a sample worksheet for analysis.
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Edit: Question: Did you have multiple worksheets selected? Need to only have a single worksheet selected.
Last edited by alansidman; 11-01-2013 at 11:03 AM.
sorry Alan, but I am not comfortable uploading the spreadsheet. I am only using one worksheet.
In that case, I suggest you google why the Icon is greyed out. I apologize, but I cannot offer any further assistance based upon what you have provided.
also did you not see that it is an array formula..did you confirm with ctrl+shift+enter
you could also try this normally entered formula
=SUMPRODUCT((A1:A62293<>"")/COUNTIF(A1:A62293,A1:A62293&""))
Last edited by martindwilson; 11-01-2013 at 11:46 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Check my thread from yesterday - I got help doing exactly what you seem to want to do.
Sorry Pepe, my bad!
I have solved this issue using "Remove Duplicates". It was greyed out because the spreadsheet was being shared. After I turned that off, I was able to use this function. This was the quickest way to get my answer. I will, however, try the other suggestion metioned by Pepe when I get time. Thank you everyone for your efforts in helping me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks