Look for specific text within other text, separated by commas

[geigMR]

Hello

I need to know a formula to look for specific text within other text, and if found display that text. The text items in the cell are always separated by commas, but there other irrelevant text items in the same cell.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Example:<o></o>
column B to look to see whether column A contains “red”, or “Bill and Ben”, or “big” and display all of these words found.<o></o>
A1: red,green,blue,black,Bill and Ben,Flower Pot Men,big,small<o></o>
B1: red,Bill and Ben,big<o></o>
<o> </o>
A2: red,white,green,blue,orange,black,Flower Pot Men,big,small<o></o>
B2: red,small<o></o>
<o> </o>
A3: white,green,blue,purple,black,Flower Pot Men,big,medium,short<o></o>
B3: big<o></o>
<o> </o>
It is important that column B maintains the separation of words by commas.<o></o>
<o> </o>
If it’s not possible to pull out the words I need, perhaps it’s possible to exclude/delete the other unnecessary words?<o></o>
<o> </o>
Many thanks for any help! <o></o>

 

[number kruncher]

Welcome to the forum

You could use a COUNTIF with wildcards eg.

=IF(COUNTIF(A1,"*"&"red"&"*"),"red"&",","")&IF(COUNTIF(A1,"*"&"Bill and Ben"&"*"),"Bill and Ben"&",","")&IF(COUNTIF(A1,"*"&"big"&"*"),"big",""

HTH

 

[geigMR]

That seems to work splendidly - thanks a million!

 

[geigMR]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Update</o>
<o></o>
<o>The above formula is great for small use, but I've discovered I think I need to modify it somehow or use something totally different. Spent all day searching and trying things but to no avail...</o>
<o></o>
<o>Just to elaborate my situation:</o>
<o> </o>
I have a column of cells with multiple strings of text in, separated by commas:<o></o>
<o> </o>
e.g. in worksheet 1<o></o>
A1: [import title]<o></o>
A2: banana,apricot,apple,pear,blue and red,red,green<o></o>
A3: pineapple,kiwi,apricot,pear,red,brown,green<o></o>
A4: mango,kiwi,banana,apple,pink and red,brown,triangular and round, oval<o></o>
etc. etc.<o></o>
<o> </o>
There are many cells often with long lists of text. This text combines different categories (e.g. fruit, colour, shape, size, country, etc)<o></o>
<o> </o>
In worksheet 2 I have a table with every single text option listed under category-titled columns<o></o>
e.g.<o></o>
column A: <o></o>
A1 fruit [title]<o></o>
A2 pineapple<o></o>
A3 mango<o></o>
column B:<o></o>
A1 Colour [title]<o></o>
A2 pink<o></o>
A3 pink and red<o></o>
etc. etc.<o></o>
<o> </o>
This is the important bit:<o></o>
In worksheet 1, in row 1 columns B and following, I have got all the same titles from worksheet 2. In the cells underneath these I need a formula to pull in the relevant text from the corresponding cell in column A, also separated by commas<o></o>
e.g.<o></o>
B2: banana,apricot,apple,pear<o></o>
B3: pineapple,kiwi,apricot,pear<o></o>
B4: mango,kiwi,banana,apple<o></o>
etc.<o></o>
C2: blue and red,red,green<o></o>
C3: red,brown,green<o></o>
C4: pink and red,brown<o></o>
etc. etc.<o></o>
<o> </o>
So each column in worksheet 1 will have its own formula relating to its corresponding column in worksheet 2.<o></o>
<o> </o>
NB. Whilst some individual words are obviously shared, the text within commas in each cell is unique – there is no duplication e.g. of “orange” for colour and “orange” for fruit (instead it would be e.g. “orange coloured”). There are no spaces next to commas, although there can be space within text e.g. “blue and red”.<o></o>
<o> </o>
It is important that the formula maintains the separation of words by commas.<o></o>
<o> </o>
So far, by using the formula kindly supplied by the previous poster, I have a got a semi-solution:<o></o>
=IF(COUNTIF($A2,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")<o></o>
<o> </o>
and dragging this formula over the over columns. However this is quite cumbersome and doesn’t work perfectly. It can lead to a series of commas in blank cells. I could type in each value, e.g. “mango” instead of 'cat table'!A$2, but this is not practical as I have many, many different categories and underlying values. It would also make the formulas very long (not sure if there is a limit).<o></o>
<o> </o>
I really need a formula to extract and present this text, that involves ‘selecting’ the appropriate column data in worksheet 2 e.g. uses A2:A30.<o></o>
<o> </o>
Thank you so much for any guidance on this!!!

 

[number kruncher]

I'm not sure if I fully understand your issue, however, I think it may be you need dynamic COUNTS on the text strings in Column A, is this right?

If this is the case you sould use Left, Mid and Right to pick out each text string within each comma, and have them populate the cells to the right of each main string. I hope this makes sense.

Then use these cells to populate the COUNTIF formula.

For a starter, use =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 to count the 'Strings' in each main string.

HTH

 

[geigMR]

Hi again NK<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thanks for looking at this! <o></o>
<o> </o>
I basically need to avoid manually typing in each 'inner string' in the formulas as I have so many inner strings. Whilst column B will be looking for certain inner strings, column C will be looking for others, column D for others and so on. The orginial COUNTIF formula basically works but I'm hoping to automate the process further, i.e. by referring to a pre-typed lists of inner string elements rather than typing each one in to the formulas. Does that make sense?<o></o>
<o> </o>
I've used your =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 to count the 'Strings' in each main string<o></o>
<o> </o>
e.g.<o></o>
<o></o>
A2: banana,apricot,apple,pear,blue and red,red,green B2: 7 <o></o>
A3: pineapple,kiwi,apricot,pear,red,brown,green B3:7 <o></o>
A4: mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval B4: 8 <o></o>
A5: mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval,square,italy B5: 10 <o></o>
A6: mango,kiwi,apple,pink and red,brown,triangular and round,oval,france,germany B6: 9 <o></o>
A7: kiwi,banana,apple,pink and red,brown,triangular and round,square and round,spain,portugal,france B7: 10 <o></o>
<o> </o>
Not quite sure what next step is?<o></o>

 

[number kruncher]

Are the 'Inner Strings' constant?

Can you have the original Countif formulae pick up other cells which vary the 'inner string' variables?

 

[geigMR]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I may be missing the point, but the ‘inner strings’ are constant in that I have a fixed list of them (on sheet 2), each under their relevant categories e.g. C2:C7 are all fruit. This list has one ‘inner string’ per cell. The ‘inner strings’ in sheet 1 A1 will never vary from this list.<o></o>
<o> </o>
The original COUNTIF formulae is picking up these cells which give the ‘inner string’ options.<o></o>
<o> </o>
Thus, as per example above, I have:<o></o>
=IF(COUNTIF($A2,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")<o></o>
<o> </o>
where ‘cat table’ is the name of my sheet 2.<o></o>
<o> </o>
However on sheet 1 I now notice that this formula is erroneously showing an ‘inner string’ if it is contained within another ‘inner string’, e.g. “pink and red” in A4 shows “pink and red”,”pink”,”red” in C4 even though the latter two ‘inner strings’ are not in A4.
<o> </o>
I have done screenshots to show what I’m talking about and trying to achieve, but unfortunately I don’t seem to be enabled to attach images…

Thanks for helping

 

[geigMR]

Ok, I think I've worked out how to display my examples

Sheet 1:

Excel Workbook
	A	B	C	D	E
1		FRUIT	COLOUR	SHAPE	COUNTRY
2	banana,apricot,apple,pear,blue and red,red,green	banana,apricot,apple,	blue and red,red,	,
3	pineapple,kiwi,apricot,pear,red,brown,green	kiwi,apricot,apple,	red,brown,	,
4	mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval	mango,kiwi,banana,apple,	pink and red,pink,red,brown,	triangular and round,oval,,
5	mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval,square,italy	mango,kiwi,banana,apple,	pink and red,pink,red,brown,	triangular and round,oval,square,,	italy,
6	mango,kiwi,apple,pink and red,brown,triangular and round,oval,france,germany	mango,kiwi,apple,	pink and red,pink,red,brown,	triangular and round,oval,,	france,germany,
7	kiwi,banana,apple,pink and red,brown,triangular and round,square and round,spain,portugal,france	kiwi,banana,apple,	pink and red,pink,red,brown,	triangular and round,square and round,square,,	spain,portugal,france,
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A2,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")
B3	=IF(COUNTIF($A3,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")
B4	=IF(COUNTIF($A4,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A4,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A4,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A4,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A4,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")
B5	=IF(COUNTIF($A5,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A5,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A5,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A5,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A5,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")
B6	=IF(COUNTIF($A6,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A6,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A6,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A6,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A6,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")
B7	=IF(COUNTIF($A7,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A7,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A7,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A7,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A7,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")
C2	=IF(COUNTIF($A2,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")
C3	=IF(COUNTIF($A3,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")
C4	=IF(COUNTIF($A4,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A4,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A4,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A4,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A4,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")
C5	=IF(COUNTIF($A5,"*"&"pink and red"&"*"),"pink and red"&",","")&IF(COUNTIF($A5,"*"&"pink"&"*"),"pink"&",","")&IF(COUNTIF($A5,"*"&"blue and red"&"*"),"blue and red"&",","")&IF(COUNTIF($A5,"*"&"red"&"*"),"red"&",","")&IF(COUNTIF($A5,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")
C6	=IF(COUNTIF($A6,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A6,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A6,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A6,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A6,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")
C7	=IF(COUNTIF($A7,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A7,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A7,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A7,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A7,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")
D2	=IF(COUNTIF($A2,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")
D3	=IF(COUNTIF($A3,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")
D4	=IF(COUNTIF($A4,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A4,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A4,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A4,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A4,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")
D5	=IF(COUNTIF($A5,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A5,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A5,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A5,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A5,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")
D6	=IF(COUNTIF($A6,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A6,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A6,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A6,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A6,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")
D7	=IF(COUNTIF($A7,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A7,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A7,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A7,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A7,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")
E2	=IF(COUNTIF($A2,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")
E3	=IF(COUNTIF($A3,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")
E4	=IF(COUNTIF($A4,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A4,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A4,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A4,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A4,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")
E5	=IF(COUNTIF($A5,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A5,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A5,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A5,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A5,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")
E6	=IF(COUNTIF($A6,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A6,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A6,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A6,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A6,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")
E7	=IF(COUNTIF($A7,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A7,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A7,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A7,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A7,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")

and Sheet 2 ('cat table'):

Excel Workbook
	A	B	C	D
1	FRUIT	COLOUR	SHAPE	COUNTRY
2	mango	pink and red	triangular and round	spain
3	kiwi	pink	oval	portugal
4	banana	blue and red	square and round	italy
5	apricot	red	square	france
6	apple	brown		germany
7	pear	green
8
cat table

Excel 2003



Hope this works...

 

[geigMR]

Hi

I have made some progress...

I've removed the aforementioned duplication of words by changing the formula to remove the
&","
combined with adding commas to the beginning and end of my reference table in sheet 2.

The drawback is that this leads to double commas between inner strings, as well as commas at the beginning and end of the entire string, which I don't want. However I'm sure this is overcomable(?)

Now, if I can just get it so that I can simply select a column in sheet2, rather than typing a hundred cell references, that would be perfect...

Please see below:

Excel Workbook
	A	B	C	D	E
1		FRUIT	COLOUR	SHAPE	COUNTRY
2	banana,apricot,apple,pear,blue and red,red,green	,apricot,,apple,	,blue and red,,red,
3	pineapple,kiwi,apricot,pear,red,brown,green	,kiwi,,apricot,	,red,,brown,
4	mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval	,kiwi,,banana,,apple,	,pink and red,,brown,	,triangular and round,
5	mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval,square,italy	,kiwi,,banana,,apple,	,pink and red,,brown,	,triangular and round,,oval,,square,
6	mango,kiwi,apple,pink and red,brown,triangular and round,oval,france,germany	,kiwi,,apple,	,pink and red,,brown,	,triangular and round,,oval,	,france,
7	kiwi,banana,apple,pink and red,brown,triangular and round,square,square and round,spain,portugal,france	,banana,,apple,	,pink and red,,brown,	,triangular and round,,square and round,,square,	,spain,,portugal,
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A2,"*"&'cat table'!A$2&"*"),'cat table'!A$2,"")&IF(COUNTIF($A2,"*"&'cat table'!A$3&"*"),'cat table'!A$3,"")&IF(COUNTIF($A2,"*"&'cat table'!A$4&"*"),'cat table'!A$4,"")&IF(COUNTIF($A2,"*"&'cat table'!A$5&"*"),'cat table'!A$5,"")&IF(COUNTIF($A2,"*"&'cat table'!A$6&"*"),'cat table'!A$6,"")
B3	=IF(COUNTIF($A3,"*"&'cat table'!A$2&"*"),'cat table'!A$2,"")&IF(COUNTIF($A3,"*"&'cat table'!A$3&"*"),'cat table'!A$3,"")&IF(COUNTIF($A3,"*"&'cat table'!A$4&"*"),'cat table'!A$4,"")&IF(COUNTIF($A3,"*"&'cat table'!A$5&"*"),'cat table'!A$5,"")&IF(COUNTIF($A3,"*"&'cat table'!A$6&"*"),'cat table'!A$6,"")
B4	=IF(COUNTIF($A4,"*"&'cat table'!A$2&"*"),'cat table'!A$2,"")&IF(COUNTIF($A4,"*"&'cat table'!A$3&"*"),'cat table'!A$3,"")&IF(COUNTIF($A4,"*"&'cat table'!A$4&"*"),'cat table'!A$4,"")&IF(COUNTIF($A4,"*"&'cat table'!A$5&"*"),'cat table'!A$5,"")&IF(COUNTIF($A4,"*"&'cat table'!A$6&"*"),'cat table'!A$6,"")
B5	=IF(COUNTIF($A5,"*"&'cat table'!A$2&"*"),'cat table'!A$2,"")&IF(COUNTIF($A5,"*"&'cat table'!A$3&"*"),'cat table'!A$3,"")&IF(COUNTIF($A5,"*"&'cat table'!A$4&"*"),'cat table'!A$4,"")&IF(COUNTIF($A5,"*"&'cat table'!A$5&"*"),'cat table'!A$5,"")&IF(COUNTIF($A5,"*"&'cat table'!A$6&"*"),'cat table'!A$6,"")
B6	=IF(COUNTIF($A6,"*"&'cat table'!A$2&"*"),'cat table'!A$2,"")&IF(COUNTIF($A6,"*"&'cat table'!A$3&"*"),'cat table'!A$3,"")&IF(COUNTIF($A6,"*"&'cat table'!A$4&"*"),'cat table'!A$4,"")&IF(COUNTIF($A6,"*"&'cat table'!A$5&"*"),'cat table'!A$5,"")&IF(COUNTIF($A6,"*"&'cat table'!A$6&"*"),'cat table'!A$6,"")
B7	=IF(COUNTIF($A7,"*"&'cat table'!A$2&"*"),'cat table'!A$2,"")&IF(COUNTIF($A7,"*"&'cat table'!A$3&"*"),'cat table'!A$3,"")&IF(COUNTIF($A7,"*"&'cat table'!A$4&"*"),'cat table'!A$4,"")&IF(COUNTIF($A7,"*"&'cat table'!A$5&"*"),'cat table'!A$5,"")&IF(COUNTIF($A7,"*"&'cat table'!A$6&"*"),'cat table'!A$6,"")
C2	=IF(COUNTIF($A2,"*"&'cat table'!B$2&"*"),'cat table'!B$2,"")&IF(COUNTIF($A2,"*"&'cat table'!B$3&"*"),'cat table'!B$3,"")&IF(COUNTIF($A2,"*"&'cat table'!B$4&"*"),'cat table'!B$4,"")&IF(COUNTIF($A2,"*"&'cat table'!B$5&"*"),'cat table'!B$5,"")&IF(COUNTIF($A2,"*"&'cat table'!B$6&"*"),'cat table'!B$6,"")
C3	=IF(COUNTIF($A3,"*"&'cat table'!B$2&"*"),'cat table'!B$2,"")&IF(COUNTIF($A3,"*"&'cat table'!B$3&"*"),'cat table'!B$3,"")&IF(COUNTIF($A3,"*"&'cat table'!B$4&"*"),'cat table'!B$4,"")&IF(COUNTIF($A3,"*"&'cat table'!B$5&"*"),'cat table'!B$5,"")&IF(COUNTIF($A3,"*"&'cat table'!B$6&"*"),'cat table'!B$6,"")
C4	=IF(COUNTIF($A4,"*"&'cat table'!B$2&"*"),'cat table'!B$2,"")&IF(COUNTIF($A4,"*"&'cat table'!B$3&"*"),'cat table'!B$3,"")&IF(COUNTIF($A4,"*"&'cat table'!B$4&"*"),'cat table'!B$4,"")&IF(COUNTIF($A4,"*"&'cat table'!B$5&"*"),'cat table'!B$5,"")&IF(COUNTIF($A4,"*"&'cat table'!B$6&"*"),'cat table'!B$6,"")
C5	=IF(COUNTIF($A5,"*"&'cat table'!B$2&"*"),'cat table'!B$2,"")&IF(COUNTIF($A5,"*"&'cat table'!B$3&"*"),'cat table'!B$3,"")&IF(COUNTIF($A5,"*"&'cat table'!B$4&"*"),'cat table'!B$4,"")&IF(COUNTIF($A5,"*"&'cat table'!B$5&"*"),'cat table'!B$5,"")&IF(COUNTIF($A5,"*"&'cat table'!B$6&"*"),'cat table'!B$6,"")
C6	=IF(COUNTIF($A6,"*"&'cat table'!B$2&"*"),'cat table'!B$2,"")&IF(COUNTIF($A6,"*"&'cat table'!B$3&"*"),'cat table'!B$3,"")&IF(COUNTIF($A6,"*"&'cat table'!B$4&"*"),'cat table'!B$4,"")&IF(COUNTIF($A6,"*"&'cat table'!B$5&"*"),'cat table'!B$5,"")&IF(COUNTIF($A6,"*"&'cat table'!B$6&"*"),'cat table'!B$6,"")
C7	=IF(COUNTIF($A7,"*"&'cat table'!B$2&"*"),'cat table'!B$2,"")&IF(COUNTIF($A7,"*"&'cat table'!B$3&"*"),'cat table'!B$3,"")&IF(COUNTIF($A7,"*"&'cat table'!B$4&"*"),'cat table'!B$4,"")&IF(COUNTIF($A7,"*"&'cat table'!B$5&"*"),'cat table'!B$5,"")&IF(COUNTIF($A7,"*"&'cat table'!B$6&"*"),'cat table'!B$6,"")
D2	=IF(COUNTIF($A2,"*"&'cat table'!C$2&"*"),'cat table'!C$2,"")&IF(COUNTIF($A2,"*"&'cat table'!C$3&"*"),'cat table'!C$3,"")&IF(COUNTIF($A2,"*"&'cat table'!C$4&"*"),'cat table'!C$4,"")&IF(COUNTIF($A2,"*"&'cat table'!C$5&"*"),'cat table'!C$5,"")&IF(COUNTIF($A2,"*"&'cat table'!C$6&"*"),'cat table'!C$6,"")
D3	=IF(COUNTIF($A3,"*"&'cat table'!C$2&"*"),'cat table'!C$2,"")&IF(COUNTIF($A3,"*"&'cat table'!C$3&"*"),'cat table'!C$3,"")&IF(COUNTIF($A3,"*"&'cat table'!C$4&"*"),'cat table'!C$4,"")&IF(COUNTIF($A3,"*"&'cat table'!C$5&"*"),'cat table'!C$5,"")&IF(COUNTIF($A3,"*"&'cat table'!C$6&"*"),'cat table'!C$6,"")
D4	=IF(COUNTIF($A4,"*"&'cat table'!C$2&"*"),'cat table'!C$2,"")&IF(COUNTIF($A4,"*"&'cat table'!C$3&"*"),'cat table'!C$3,"")&IF(COUNTIF($A4,"*"&'cat table'!C$4&"*"),'cat table'!C$4,"")&IF(COUNTIF($A4,"*"&'cat table'!C$5&"*"),'cat table'!C$5,"")&IF(COUNTIF($A4,"*"&'cat table'!C$6&"*"),'cat table'!C$6,"")
D5	=IF(COUNTIF($A5,"*"&'cat table'!C$2&"*"),'cat table'!C$2,"")&IF(COUNTIF($A5,"*"&'cat table'!C$3&"*"),'cat table'!C$3,"")&IF(COUNTIF($A5,"*"&'cat table'!C$4&"*"),'cat table'!C$4,"")&IF(COUNTIF($A5,"*"&'cat table'!C$5&"*"),'cat table'!C$5,"")&IF(COUNTIF($A5,"*"&'cat table'!C$6&"*"),'cat table'!C$6,"")
D6	=IF(COUNTIF($A6,"*"&'cat table'!C$2&"*"),'cat table'!C$2,"")&IF(COUNTIF($A6,"*"&'cat table'!C$3&"*"),'cat table'!C$3,"")&IF(COUNTIF($A6,"*"&'cat table'!C$4&"*"),'cat table'!C$4,"")&IF(COUNTIF($A6,"*"&'cat table'!C$5&"*"),'cat table'!C$5,"")&IF(COUNTIF($A6,"*"&'cat table'!C$6&"*"),'cat table'!C$6,"")
D7	=IF(COUNTIF($A7,"*"&'cat table'!C$2&"*"),'cat table'!C$2,"")&IF(COUNTIF($A7,"*"&'cat table'!C$3&"*"),'cat table'!C$3,"")&IF(COUNTIF($A7,"*"&'cat table'!C$4&"*"),'cat table'!C$4,"")&IF(COUNTIF($A7,"*"&'cat table'!C$5&"*"),'cat table'!C$5,"")&IF(COUNTIF($A7,"*"&'cat table'!C$6&"*"),'cat table'!C$6,"")
E2	=IF(COUNTIF($A2,"*"&'cat table'!D$2&"*"),'cat table'!D$2,"")&IF(COUNTIF($A2,"*"&'cat table'!D$3&"*"),'cat table'!D$3,"")&IF(COUNTIF($A2,"*"&'cat table'!D$4&"*"),'cat table'!D$4,"")&IF(COUNTIF($A2,"*"&'cat table'!D$5&"*"),'cat table'!D$5,"")&IF(COUNTIF($A2,"*"&'cat table'!D$6&"*"),'cat table'!D$6,"")
E3	=IF(COUNTIF($A3,"*"&'cat table'!D$2&"*"),'cat table'!D$2,"")&IF(COUNTIF($A3,"*"&'cat table'!D$3&"*"),'cat table'!D$3,"")&IF(COUNTIF($A3,"*"&'cat table'!D$4&"*"),'cat table'!D$4,"")&IF(COUNTIF($A3,"*"&'cat table'!D$5&"*"),'cat table'!D$5,"")&IF(COUNTIF($A3,"*"&'cat table'!D$6&"*"),'cat table'!D$6,"")
E4	=IF(COUNTIF($A4,"*"&'cat table'!D$2&"*"),'cat table'!D$2,"")&IF(COUNTIF($A4,"*"&'cat table'!D$3&"*"),'cat table'!D$3,"")&IF(COUNTIF($A4,"*"&'cat table'!D$4&"*"),'cat table'!D$4,"")&IF(COUNTIF($A4,"*"&'cat table'!D$5&"*"),'cat table'!D$5,"")&IF(COUNTIF($A4,"*"&'cat table'!D$6&"*"),'cat table'!D$6,"")
E5	=IF(COUNTIF($A5,"*"&'cat table'!D$2&"*"),'cat table'!D$2,"")&IF(COUNTIF($A5,"*"&'cat table'!D$3&"*"),'cat table'!D$3,"")&IF(COUNTIF($A5,"*"&'cat table'!D$4&"*"),'cat table'!D$4,"")&IF(COUNTIF($A5,"*"&'cat table'!D$5&"*"),'cat table'!D$5,"")&IF(COUNTIF($A5,"*"&'cat table'!D$6&"*"),'cat table'!D$6,"")
E6	=IF(COUNTIF($A6,"*"&'cat table'!D$2&"*"),'cat table'!D$2,"")&IF(COUNTIF($A6,"*"&'cat table'!D$3&"*"),'cat table'!D$3,"")&IF(COUNTIF($A6,"*"&'cat table'!D$4&"*"),'cat table'!D$4,"")&IF(COUNTIF($A6,"*"&'cat table'!D$5&"*"),'cat table'!D$5,"")&IF(COUNTIF($A6,"*"&'cat table'!D$6&"*"),'cat table'!D$6,"")
E7	=IF(COUNTIF($A7,"*"&'cat table'!D$2&"*"),'cat table'!D$2,"")&IF(COUNTIF($A7,"*"&'cat table'!D$3&"*"),'cat table'!D$3,"")&IF(COUNTIF($A7,"*"&'cat table'!D$4&"*"),'cat table'!D$4,"")&IF(COUNTIF($A7,"*"&'cat table'!D$5&"*"),'cat table'!D$5,"")&IF(COUNTIF($A7,"*"&'cat table'!D$6&"*"),'cat table'!D$6,"")

Excel Workbook
	A	B	C	D
1	FRUIT	COLOUR	SHAPE	COUNTRY
2	,mango,	,pink and red,	,triangular and round,	,spain,
3	,kiwi,	,pink,	,oval,	,portugal,
4	,banana,	,blue and red,	,square and round,	,italy,
5	,apricot,	,red,	,square,	,france,
6	,apple,	,brown,		,germany,
7	,pear,	,green,
cat table

Excel 2003



Thanks