Excel- How do I find common text values from multiple (>2) columns?
I have 9 columns of text data and need to know the common terms that are found in all nine columns (or possibly different combinations of columns). I can do 2 columns =IF(ISERROR(MATCH(A1,$C$1:$C$133,0)),"",A1) and I can find the text values that are duplicates across the whole spreadsheet but can't figure out how to look at the common text values across multiple different columns.
Is there a way to modify the =IF(ISERROR(MATCH(A1,$C$1:$C$133,0)),"",A1) formula so I can compare 3, 4,... 9 columns rather than just two? I'm using Microsoft 2013 if that helps.
3 Answers
I've got a solution that would work but it's kind of ugly. I'm going to assume you are looking at a cell A1 and seeing if it shows up in all 3 columns (D,E,F).
=MIN(MAX(($D$1:$D$3=$A1)*1),MAX(($E$1:$E$3=$A1)*1),MAX(($F$1:$F$3=$A1)*1))This is an array formula so it needs to be entered with ctrl+shift+enter
Now how it works starting from the inside out
($D$1:$D$3=$A1)*1 Compare D1 through D3 to A1 which would return true or false the *1 converts this to 1s and 0s
Max If the first formula finds a match in column D it returns a 1 which the Max will return. It no match is found the max (only) value is zero.
Min If all the columns contain a match all the max formulas will contain a 1 so the minimum will be 1. If there isn't a match in all the columns the minimum value will be zero.
If you want to count the number of columns that match use sum instead of min and rememeber ctrl+shift+enter.
note:It will be one max formula for each column you want to search.
This set-up will work for any number of columns.
First go to Name Manager (Formulas tab) and define the following:
Name: Range1
Refers to: =$A$1:$I$8(Or whatever happens to be the range in question.)
Name: Arry1
Refers to: =COLUMN(Range1)-MIN(COLUMN(Range1))
Name: Arry2
Refers to: =ROW(INDEX(Range1,,1))-MIN(ROW(INDEX(Range1,,1)))+1
Name: Arry3
Refers to: =MMULT(0+(COUNTIF(OFFSET(INDEX(Range1,,1),,Arry1,,),INDEX(Range1,,1))>0),ROW(INDIRECT("1:"&COLUMNS(Range1)))^0)Exit Name Manager.
The required array formula is then:
=IFERROR(INDEX(INDEX(Range1,,1),SMALL(IF(FREQUENCY(IF(INDEX(Range1,,1)<>"",IF(Arry3=COLUMNS(Range1),MATCH(INDEX(Range1,,1),INDEX(Range1,,1),0))),Arry2),Arry2),ROWS($1:1))),"")Copy down until you start to get blanks for the results.
Regards
Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just Enter, you first hold down Ctrl and Shift, and only then press Enter. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
I think you could get pretty far with countif:
