1
1
Need some help figuring out how to do this in Excel.
I have a long list of names split up into 2 columns - the first and the last name. I'd like to figure out what are the 5 most popular full names but - Some last names and first names are reversed, and so I'd like "John | Smith" to also count toward "Smith | John." How can I do this in excel?
I tried putting both columns into a pivot table, and putting First name and Last name into the row labels and the count of First Name into the values, but this gives me a long list of every name mixed with every other name, and the only way to figure out the most popular name is to scroll down through the entire table and count the highest number that I see. When I add a "top 10" value filter to the results, the top values are returned by the first name that has the highest frequency, regardless of what the last name is.
For example. I can have 5 names:
John Smith
Smith John
Frank White
Frank Green
Frank Black
And if I applied a top 10 filter, it would show me that Frank was the most common name, followed by John Smith, even though what I really want to see is John Smith, since it was in there more than any other name.
Hopefully this makes sense. If anyone could provide some insight into how I could accomplish this I would be most appreciative. Please let me know if you have any questions, thanks!
EDIT: Regarding how to determine what a first name is and what a last name is, The order of the names don't actually matter. The only problem I foresee with this is that there will be a duplicate of the most frequent names, so in the example I gave, "Smith John" and "John Smith" are the most popular names, with 2 each. (If that makes sense.) I'm ok with that, because it's very easy to look at a top 10 list and discount every other result.
How is "John" with 2 occurrences more frequent than "Frank" with 3?? – user1016274 – 2015-07-10T15:14:01.133
@user1016274 the "john smith" and "smith john" = 2, while each "Frank" only has one occurrence. – Raystafarian – 2015-07-10T15:15:03.190
Do you already have a list of all the possible full names? – Raystafarian – 2015-07-10T15:20:10.070
@Rastafarian - Unfortunately I have 1.4 million rows spread across 100 books so it's not really an option to fix the data. I do not have a list of all possible full names. – Matt – 2015-07-10T15:24:06.980
@Raystafarian You can still solve the problem, but the result will have the caveat that distinct names could be grouped together, like Frank Joseph and Joseph Frank. – Excellll – 2015-07-10T15:28:58.743
John can be a last name. I think the worst that could happen is that there would be a duplicate top result - John Smith and Smith John. – Matt – 2015-07-10T15:29:28.587
@Raystafarian I'm not proposing a solution. I'm just saying you don't need to get hung up on the last-name-that-could-be-a-first-name issue. You can still get the result desired, but just take it with a grain of salt. That's all I'm saying. – Excellll – 2015-07-10T15:32:28.093
@Excellll fair enough – Raystafarian – 2015-07-10T15:33:24.523
1Basically the order of the name does not need to be differentiated. Sorry for the confusion, I'm using the names example but my actual situation is a little more complicated to explain and a little bit different. Basically Matt John and John Matt are exactly the same to me. – Matt – 2015-07-10T15:33:48.233