SOLVED

Identifying matches between multiple columns

Copper Contributor

Hi, all-- I'm assisting one of our teachers who has collected survey data from students. We're trying to identify students who have given corresponding "strengths" and "weaknesses" so that they can be paired up. Column A lists their reported "greatest" strength, Column B lists an area that they want to work on, and C lists all their identified strengths.

 

Our goal would be to be able to match that one student has listed (for example) "Posing Interesting Questions" as a strength in either A or C, and "Building on the ideas of others" as a goal in B, while another student has listed "Building..." as a strength in A or C and "Posing" as a goal in B. 

 

Is there a way that we can match those corresponding values? (For ID purposes, we also have a column of e-mail addresses as D, but I removed that to post the file attachment)

 

Thanks!

2 Replies
best response confirmed by Jeff Tillinghast (Copper Contributor)
Solution

Hi Jeff,

 

I assume what text in column A "Biggest Strengths" is included in related cell in column C "All Strengths They Chose" as substring. Thus we only need to compare columns B and C.

 

Two identical possible solutions.

If you have Excel 2016 where TEXTJOIN function is available when we may add to column E array formula (Ctrl+Shift+Enter)

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH($B2,$C$2:$C$48)),$D$2:$D$48,""))

It checks if text in cell of column B appears somethere in column C and combine related ID:s (column D) into one string. That is in Sheet1 of the attached file.

 

For previous version of Excel that's bit more complicated (see Sheet2). We will find matched ID:s one by one.

First match is in column E

=INDEX($D$2:$D$48,MATCH(1,--ISNUMBER(SEARCH($B2,$C$2:$C$48)),0))

, drag E2 down till end of the range.

 

In next columns we shall exclude already found ID:s. To do that we start search from the position next to previously founded ID in previous column shifting start of the search by OFFSET: in column F also array formula

=IFERROR(INDEX(OFFSET($D$2:$D$48,MATCH(E2,$D$2:$D$48,0),0),MATCH(1,--ISNUMBER(SEARCH($B2,OFFSET($C$2:$C$48,MATCH(E2,$D$2:$D$48,0),0))),0)),"")

drag F2 down till end of the range and after that entire column F (more exactly F2:F48 in your case) to the right till all cells in the column becomes empty.

 

 

 

Sergei-- This is great--thank you. We have Excel 2016, so your first solution works for us!

1 best response

Accepted Solutions
best response confirmed by Jeff Tillinghast (Copper Contributor)
Solution

Hi Jeff,

 

I assume what text in column A "Biggest Strengths" is included in related cell in column C "All Strengths They Chose" as substring. Thus we only need to compare columns B and C.

 

Two identical possible solutions.

If you have Excel 2016 where TEXTJOIN function is available when we may add to column E array formula (Ctrl+Shift+Enter)

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH($B2,$C$2:$C$48)),$D$2:$D$48,""))

It checks if text in cell of column B appears somethere in column C and combine related ID:s (column D) into one string. That is in Sheet1 of the attached file.

 

For previous version of Excel that's bit more complicated (see Sheet2). We will find matched ID:s one by one.

First match is in column E

=INDEX($D$2:$D$48,MATCH(1,--ISNUMBER(SEARCH($B2,$C$2:$C$48)),0))

, drag E2 down till end of the range.

 

In next columns we shall exclude already found ID:s. To do that we start search from the position next to previously founded ID in previous column shifting start of the search by OFFSET: in column F also array formula

=IFERROR(INDEX(OFFSET($D$2:$D$48,MATCH(E2,$D$2:$D$48,0),0),MATCH(1,--ISNUMBER(SEARCH($B2,OFFSET($C$2:$C$48,MATCH(E2,$D$2:$D$48,0),0))),0)),"")

drag F2 down till end of the range and after that entire column F (more exactly F2:F48 in your case) to the right till all cells in the column becomes empty.

 

 

 

View solution in original post