How to categorise a column in excel based on anoth

问题:

I have the following spreadsheet on the image (please click on link below for image):

Raw data and categorised data

Suppose I have the raw data Name B4:B11 and Interests C4:C11.

My question is I want to categorise the raw data so the output is as pictured in B16:B34 and C16:C34. I am trying to categorise people by their interests when their interests are in a column containing strings separated by semicolon. The Name can come up multiple times according to their interests where in this case Movie, Music and Radio.

Please kindly assist. Thanks kindly.

回答1:

This formula will work as in the picture below.

This will need to be entered as an array formula (when in the formula bar hit Ctrl+Shift+Enter)

=IFERROR(INDEX($B$1:$B$11,SMALL(IF(ISNUMBER(SEARCH(B$15,$C$1:$C$11)),ROW($C$1:$C$11)-ROW(INDEX($C$1:$C$11,1,1))+1),ROW(1:1))),"")

Array formula's break down ranges and calculate them them one cell (or row) at a time.

The SMALL formula (=SMALL(array,k)) will use the array results of the IF formula. The IF formula is returning either FALSE (default) or the row number when the text can be found in the cell using search, I have used =ISNUMBER to force true false rather than it throwing any errors as if it finds the text it will give a numeric value as the starting position of that string in that cell.

The SMALL formula puts the results in the array in ascending order and will only call on actual values (not FALSE) so I have used ROW(1:1) at the end of the small formula for the k part: When the row automatically increases to the next number, the next highest result is displayed.

I hope this helps explain the logic used.