Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ranking names
I have a list a1.a1000 first names, b1.b1000 last names, that are sometimes
duplicative, sometimes not: A B Joe Smith Joe Smith Joe Jones Frank Jones Sally Doe I want to sort these in rank order with a second column (D) that shows how many times they show up: C D Joe Smith 2 Joe Jones 1 Frank Jones 1 etc. Thank you Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ranking names
1) Use Chip's method to extract the unique names: see
http://www.cpearson.com/excel/duplic...tractingUnique 2) Copy and Paste Special -Values these names to a new column 3) Use COUNTIF to find how many times each name in the unique column occurs in the original data. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave" wrote in message ... I have a list a1.a1000 first names, b1.b1000 last names, that are sometimes duplicative, sometimes not: A B Joe Smith Joe Smith Joe Jones Frank Jones Sally Doe I want to sort these in rank order with a second column (D) that shows how many times they show up: C D Joe Smith 2 Joe Jones 1 Frank Jones 1 etc. Thank you Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ranking names
Thanks, Bernard. Working well with one hitch:
The first and last names are in two different columns. So, using the example below, when I do the extracting formula in two columns, I keep, say, Joe Jones, but eliminate Joe Smith. "Joe" S. cancels "Joe" J. in the first column. If that makes sense, do you have a way around it? Thanks Dave "Bernard Liengme" wrote: 1) Use Chip's method to extract the unique names: see http://www.cpearson.com/excel/duplic...tractingUnique 2) Copy and Paste Special -Values these names to a new column 3) Use COUNTIF to find how many times each name in the unique column occurs in the original data. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave" wrote in message ... I have a list a1.a1000 first names, b1.b1000 last names, that are sometimes duplicative, sometimes not: A B Joe Smith Joe Smith Joe Jones Frank Jones Sally Doe I want to sort these in rank order with a second column (D) that shows how many times they show up: C D Joe Smith 2 Joe Jones 1 Frank Jones 1 etc. Thank you Dave |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ranking names
Well, now I'm answering myself. Solved it by concatenating the names into
one cell. "Dave" wrote: Thanks, Bernard. Working well with one hitch: The first and last names are in two different columns. So, using the example below, when I do the extracting formula in two columns, I keep, say, Joe Jones, but eliminate Joe Smith. "Joe" S. cancels "Joe" J. in the first column. If that makes sense, do you have a way around it? Thanks Dave "Bernard Liengme" wrote: 1) Use Chip's method to extract the unique names: see http://www.cpearson.com/excel/duplic...tractingUnique 2) Copy and Paste Special -Values these names to a new column 3) Use COUNTIF to find how many times each name in the unique column occurs in the original data. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave" wrote in message ... I have a list a1.a1000 first names, b1.b1000 last names, that are sometimes duplicative, sometimes not: A B Joe Smith Joe Smith Joe Jones Frank Jones Sally Doe I want to sort these in rank order with a second column (D) that shows how many times they show up: C D Joe Smith 2 Joe Jones 1 Frank Jones 1 etc. Thank you Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ranking names
Well done
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave" wrote in message ... Well, now I'm answering myself. Solved it by concatenating the names into one cell. "Dave" wrote: Thanks, Bernard. Working well with one hitch: The first and last names are in two different columns. So, using the example below, when I do the extracting formula in two columns, I keep, say, Joe Jones, but eliminate Joe Smith. "Joe" S. cancels "Joe" J. in the first column. If that makes sense, do you have a way around it? Thanks Dave "Bernard Liengme" wrote: 1) Use Chip's method to extract the unique names: see http://www.cpearson.com/excel/duplic...tractingUnique 2) Copy and Paste Special -Values these names to a new column 3) Use COUNTIF to find how many times each name in the unique column occurs in the original data. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave" wrote in message ... I have a list a1.a1000 first names, b1.b1000 last names, that are sometimes duplicative, sometimes not: A B Joe Smith Joe Smith Joe Jones Frank Jones Sally Doe I want to sort these in rank order with a second column (D) that shows how many times they show up: C D Joe Smith 2 Joe Jones 1 Frank Jones 1 etc. Thank you Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NAMES DEFINED | Excel Discussion (Misc queries) | |||
Have 2 columns of names - need to filter out names not duplicated | Excel Worksheet Functions | |||
copying cell names | Excel Discussion (Misc queries) | |||
Replace range names with cell references? | Excel Worksheet Functions | |||
Finding Duplicate Names from Different Lists... | Excel Discussion (Misc queries) |