Hi guys, wondering if you can help me out.
I have a long list of names split up into the first and the last name. I'd like to figure out what are the 5 most popular full names. Some last names and first names are also 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 what the most popular name is, 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
John Smith
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!
Bookmarks