I need some help getting a list of common values between multiple columns and then how many times they show up. Basically I'd like to have Excel scan two or more columns at the same time and generate a list of common entries and their frequencies. I've included an example of the original excel file and the output that I would like. Any help is greatly appreciated. Thanks!
I'd just combine all your columns into one column and then either remove duplicates or add subtotals based on count. Then you can do a simple =COUNTIF to get the totals for each value.
In the attached file I've shown how you can do this with formulae - for convenience, I've done it in a second sheet of the same workbook. Columns A to D could be hidden if required.
Hope this helps.
Pete
This is incredible! Thanks! One more question..can a column be added to list the columns that the frequencies are derived from? Thanks again.
Put the word "Columns" in G1 (without the quotes), and put this formula in G2:
=TRIM(IF(COUNTIF(Sheet1!A:A,E2)>0,"A ","")&IF(COUNTIF(Sheet1!B:B,E2)>0,"B ","")&IF(COUNTIF(Sheet1!C:C,E2)>0,"C ","")&IF(COUNTIF(Sheet1!D:D,E2)>0,"D",""))
Then copy down to G8.
Hope this helps.
Pete
EDIT: if you want the names of the columns instead of the column letters, use "Certificate " instead of "A " in the first IF function, and similarly for the other IF functions - note the space at the end.
Pete
Last edited by Pete_UK; 11-05-2014 at 08:07 PM.
This is great. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks