Categorisation multiple entries

[tigerzen]

After a formula/function if possible to categorise as follows:

Book3
	A	B	C	D	E	F	G
1
2	Descriptor Item	Country 1	Country 2	Country 3		Keyword	Country
3	Sale Rome	Italy				Rome	Italy
4	Travel Vienna London	Austria	Great Britain			Vienna	Austria
5	Purchases London	Great Britain				London	Great Britain
6	Sale Paris Rome London	France	Italy	Great Britain		Paris	France
Sheet1

Formula needs to pick up the cities from the descriptor item and then compare against column F and G, some descriptor cells will have multiple cities and that is the part I'm struggling with. I'm assisting someone with this, they have Office 2019 and the basic suite of functions, as far as I know they don't have the latest Office 365 upgraded functions.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1
2	Descriptor Item	Country 1	Country 2	Country 3		Keyword	Country
3	Sale Rome	Italy				Rome	Italy
4	Travel Vienna London	Austria	Great Britain			Vienna	Austria
5	Purchases London	Great Britain				London	Great Britain
6	Sale Paris Rome London	Italy	Great Britain	France		Paris	France
Sheet6

Cell Formulas
Range	Formula
B3:D6	B3	=IFERROR(INDEX($G$3:$G$6,AGGREGATE(15,6,(ROW($F$3:$F$6)-ROW($F$3)+1)/ISNUMBER(SEARCH($F$3:$F$6,$A3)),COLUMNS($B:B))),"")

 

[Peter_SSs]

I have changed the sample data but would suggest a slight change to avoid possible errors like C5 & B7 below. Adjusted formula shown in the bottom section.

23 08 03.xlsm
	A	B	C	D	E	F	G
1
2	Descriptor Item	Country 1	Country 2	Country 3		Keyword	Country
3	Sale Rome	Italy				Rome	Italy
4	Travel Vienna London	Austria	Great Britain			Vienna	Austria
5	Purchases Londonderry	Northern Ireland	Great Britain			Londonderry	Northern Ireland
6	Purchases London	Great Britain				London	Great Britain
7	Travel Fromelles	Italy	France			Fromelles	France
8	Sale Paris Rome London	Italy	Great Britain	France		Paris	France
9
10
11	Descriptor Item	Country 1	Country 2	Country 3		Keyword	Country
12	Sale Rome	Italy				Rome	Italy
13	Travel Vienna London	Austria	Great Britain			Vienna	Austria
14	Purchases Londonderry	Northern Ireland				Londonderry	Northern Ireland
15	Purchases London	Great Britain				London	Great Britain
16	Travel Fromelles	France				Fromelles	France
17	Sale Paris Rome London	Italy	Great Britain	France		Paris	France
Country

Cell Formulas
Range	Formula
B3:D8	B3	=IFERROR(INDEX($G$3:$G$8,AGGREGATE(15,6,(ROW($F$3:$F$8)-ROW($F$3)+1)/ISNUMBER(SEARCH($F$3:$F$8,$A3)),COLUMNS($B:B))),"")
B12:D17	B12	=IFERROR(INDEX($G$12:$G$17,AGGREGATE(15,6,(ROW($G$12:$G$17)-ROW($G$12)+1)/ISNUMBER(SEARCH(" "&$F$12:$F$17&" "," "&$A12&" ")),COLUMNS($B:B))),"")

 

[tigerzen]

Thanks Eric for your solution. Also thanks Peter for the quality control.

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[tigerzen]

As a follow-up question to this post, the ROW($F$3:$F$6)-ROW($F$3)+1)/ISNUMBER(SEARCH($F$3:$F$6,$A3)),COLUMNS($B:B) is such a clever array argument that delivers the outcome needed. Does anyone know if there is a video explaining this further or failing a video, a book that goes into this further and perhaps its wider applicability?

 

[Peter_SSs]

Not that I am aware of.