Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to delete duplicates and also count them
I'm in dire need of help
I have a column of 2500 entries that account for no more than 10 duplicates. So I know there has to be at least 250 unique entries. I'd like to account for every item but I don't want to have the duplicates listed. So Column A might look like this: a a a a a a b b c c c c d d d I'd ultimately want col a to just have a b c d and then col b have how many times that instance showed up a - 6 b - 2 etc etc |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to delete duplicates and also count them
Eddie,
Select your range of data including the header record then Data|Filter|Advanced filter Select copy to another location Check Unique records only Select a range to copy to which should be a single cell OK and you get a list of unique items. I piu my list in C1 down In C2 enter this formula =COUNTIF($A$2:$A$16,C2) Drag down to the length of the filtered list. Mike "Eddie" wrote: I'm in dire need of help I have a column of 2500 entries that account for no more than 10 duplicates. So I know there has to be at least 250 unique entries. I'd like to account for every item but I don't want to have the duplicates listed. So Column A might look like this: a a a a a a b b c c c c d d d I'd ultimately want col a to just have a b c d and then col b have how many times that instance showed up a - 6 b - 2 etc etc |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to delete duplicates and also count them
Mike,
Thanks so much for the reply. I did the first part in that I got a list of the items without duplication. I'd like to now count how many times those showed up on the original list. I got sort of lost in the second part of your reply. "Mike H" wrote: Eddie, Select your range of data including the header record then Data|Filter|Advanced filter Select copy to another location Check Unique records only Select a range to copy to which should be a single cell OK and you get a list of unique items. I piu my list in C1 down In C2 enter this formula =COUNTIF($A$2:$A$16,C2) Drag down to the length of the filtered list. Mike "Eddie" wrote: I'm in dire need of help I have a column of 2500 entries that account for no more than 10 duplicates. So I know there has to be at least 250 unique entries. I'd like to account for every item but I don't want to have the duplicates listed. So Column A might look like this: a a a a a a b b c c c c d d d I'd ultimately want col a to just have a b c d and then col b have how many times that instance showed up a - 6 b - 2 etc etc |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to delete duplicates and also count them
Hi,
You now have a filtered list of unique items starting in (say) C2 and your original data are in column A starting in A2. Put the formula I gave you in D2 and it will count the instances of the value in C2. Drag the formula down as long as the list in column C Mike "Eddie" wrote: Mike, Thanks so much for the reply. I did the first part in that I got a list of the items without duplication. I'd like to now count how many times those showed up on the original list. I got sort of lost in the second part of your reply. "Mike H" wrote: Eddie, Select your range of data including the header record then Data|Filter|Advanced filter Select copy to another location Check Unique records only Select a range to copy to which should be a single cell OK and you get a list of unique items. I piu my list in C1 down In C2 enter this formula =COUNTIF($A$2:$A$16,C2) Drag down to the length of the filtered list. Mike "Eddie" wrote: I'm in dire need of help I have a column of 2500 entries that account for no more than 10 duplicates. So I know there has to be at least 250 unique entries. I'd like to account for every item but I don't want to have the duplicates listed. So Column A might look like this: a a a a a a b b c c c c d d d I'd ultimately want col a to just have a b c d and then col b have how many times that instance showed up a - 6 b - 2 etc etc |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to delete duplicates and also count them
That worked excellent.
Thanks again for your help. "Mike H" wrote: Hi, You now have a filtered list of unique items starting in (say) C2 and your original data are in column A starting in A2. Put the formula I gave you in D2 and it will count the instances of the value in C2. Drag the formula down as long as the list in column C Mike "Eddie" wrote: Mike, Thanks so much for the reply. I did the first part in that I got a list of the items without duplication. I'd like to now count how many times those showed up on the original list. I got sort of lost in the second part of your reply. "Mike H" wrote: Eddie, Select your range of data including the header record then Data|Filter|Advanced filter Select copy to another location Check Unique records only Select a range to copy to which should be a single cell OK and you get a list of unique items. I piu my list in C1 down In C2 enter this formula =COUNTIF($A$2:$A$16,C2) Drag down to the length of the filtered list. Mike "Eddie" wrote: I'm in dire need of help I have a column of 2500 entries that account for no more than 10 duplicates. So I know there has to be at least 250 unique entries. I'd like to account for every item but I don't want to have the duplicates listed. So Column A might look like this: a a a a a a b b c c c c d d d I'd ultimately want col a to just have a b c d and then col b have how many times that instance showed up a - 6 b - 2 etc etc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete both duplicates | Excel Discussion (Misc queries) | |||
Delete duplicates in a row | Excel Worksheet Functions | |||
Delete duplicates? | Excel Discussion (Misc queries) | |||
Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
count a group of numbers but do not count duplicates | Excel Worksheet Functions |