Categorisation multiple entries

tigerzen

Board Regular
Joined
Mar 8, 2023
Messages
209
Office Version
  1. 365
Platform
  1. Windows
After a formula/function if possible to categorise as follows:
Book3
ABCDEFG
1
2Descriptor ItemCountry 1 Country 2Country 3KeywordCountry
3Sale Rome ItalyRomeItaly
4Travel Vienna LondonAustriaGreat BritainViennaAustria
5Purchases LondonGreat BritainLondonGreat Britain
6Sale Paris Rome LondonFranceItalyGreat BritainParisFrance
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Try:

Book1
ABCDEFG
1
2Descriptor ItemCountry 1 Country 2Country 3KeywordCountry
3Sale Rome Italy  RomeItaly
4Travel Vienna LondonAustriaGreat Britain ViennaAustria
5Purchases LondonGreat Britain  LondonGreat Britain
6Sale Paris Rome LondonItalyGreat BritainFranceParisFrance
Sheet6
Cell Formulas
RangeFormula
B3:D6B3=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))),"")
 
Upvote 0
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
ABCDEFG
1
2Descriptor ItemCountry 1 Country 2Country 3KeywordCountry
3Sale Rome Italy  RomeItaly
4Travel Vienna LondonAustriaGreat Britain ViennaAustria
5Purchases LondonderryNorthern IrelandGreat Britain LondonderryNorthern Ireland
6Purchases LondonGreat Britain  LondonGreat Britain
7Travel FromellesItalyFrance FromellesFrance
8Sale Paris Rome LondonItalyGreat BritainFranceParisFrance
9
10
11Descriptor ItemCountry 1 Country 2Country 3KeywordCountry
12Sale Rome Italy  RomeItaly
13Travel Vienna LondonAustriaGreat Britain ViennaAustria
14Purchases LondonderryNorthern Ireland  LondonderryNorthern Ireland
15Purchases LondonGreat Britain  LondonGreat Britain
16Travel FromellesFrance  FromellesFrance
17Sale Paris Rome LondonItalyGreat BritainFranceParisFrance
Country
Cell Formulas
RangeFormula
B3:D8B3=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:D17B12=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))),"")
 
Upvote 0
Thanks Eric for your solution. Also thanks Peter for the quality control.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,657
Latest member
giadungthienduyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top