List Unique Values across 3 cells

[surkdidat]

Hi
In Column A I have a city (for example London, Manchester, Liverpool
In Column B I have a mode of transport for example : Car, Bike, Motorcycle
In Column C I have a name, for example : Mary, Joseph, "Wee Donkey"

What I need it to do is list each combination, starting with Column A, then B, then C

So

Column A	Column B	Column C
London	Car	Mary
London	Bike	Joseph
Liverpool	Car	Wee Donkey
Manchester	Car	Joseph
Liverpool	Car	Wee Donkey
London	Car	Joseph
Manchester	Bike	Wee Donkey
Manchester	Bike	Mary
London	Bike	Wee Donkey
London	Car	Mary
London	Car	Mary

LONDON - CAR - MARY (So where this set of data comes up with on multiple rows its only listed once)
LONDON - CAR - JOSEPH
London - Car - Wee Donkey (This should NOT appear as this set of data does not appear in any single row)

LONDON - BIKE - JOSEPH
LONDON - BIKE - WEE DONKEY

MANCHESTER - CAR - JOSEPH

MANCHESTER - BIKE - WEE DONKEY
MANCHESTER - BIKE - MARY

LIVERPOOL - CAR - WEE DONKEY


Hope that makes sense!

 

[kweaver]

I used a helper column D. Does this look correct?

Book2
	A	B	C	D	E	F
2	London	Car	Mary	London - Car - Mary		London - Car - Mary
3	London	Bike	Joseph	London - Bike - Joseph		London - Bike - Joseph
4	Liverpool	Car	Wee Donkey	Liverpool - Car - Wee Donkey		Liverpool - Car - Wee Donkey
5	Manchester	Car	Joseph	Manchester - Car - Joseph		Manchester - Car - Joseph
6	Liverpool	Car	Wee Donkey	Liverpool - Car - Wee Donkey		London - Car - Joseph
7	London	Car	Joseph	London - Car - Joseph		Manchester - Bike - Wee Donkey
8	Manchester	Bike	Wee Donkey	Manchester - Bike - Wee Donkey		Manchester - Bike - Mary
9	Manchester	Bike	Mary	Manchester - Bike - Mary		London - Bike - Wee Donkey
10	London	Bike	Wee Donkey	London - Bike - Wee Donkey
11	London	Car	Mary	London - Car - Mary
12	London	Car	Mary	London - Car - Mary
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=A2&" - "&B2&" - "&C2
F2:F12	F2	=IFERROR(INDEX(D2:D100,AGGREGATE(15,6,(ROW(A2:A100)-ROW(A2)+1)/(ISNA(MATCH(D2:D100,F$1:F1,0)))/(D2:D100<>""),1)),"")

 

[surkdidat]

Apologies I have not been able to get online for a few days... My spreadsheet goes down to Row 20117, so have taken the 100 out, and replaced it to 20117

What does the 15,6 do - do i need to amend this as a result?

Also, I have put the formula in F2, but it is not bringing up any results - have I missed anything obvious? (I ie does F1 need a column Header? (have tried ths, but to no avail as its referencing F1 in the formula)

Thanks!
a

I used a helper column D. Does this look correct?

Book2
	A	B	C	D	E	F
2	London	Car	Mary	London - Car - Mary		London - Car - Mary
3	London	Bike	Joseph	London - Bike - Joseph		London - Bike - Joseph
4	Liverpool	Car	Wee Donkey	Liverpool - Car - Wee Donkey		Liverpool - Car - Wee Donkey
5	Manchester	Car	Joseph	Manchester - Car - Joseph		Manchester - Car - Joseph
6	Liverpool	Car	Wee Donkey	Liverpool - Car - Wee Donkey		London - Car - Joseph
7	London	Car	Joseph	London - Car - Joseph		Manchester - Bike - Wee Donkey
8	Manchester	Bike	Wee Donkey	Manchester - Bike - Wee Donkey		Manchester - Bike - Mary
9	Manchester	Bike	Mary	Manchester - Bike - Mary		London - Bike - Wee Donkey
10	London	Bike	Wee Donkey	London - Bike - Wee Donkey
11	London	Car	Mary	London - Car - Mary
12	London	Car	Mary	London - Car - Mary
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=A2&" - "&B2&" - "&C2
F2:F12	F2	=IFERROR(INDEX(D2:D100,AGGREGATE(15,6,(ROW(A2:A100)-ROW(A2)+1)/(ISNA(MATCH(D2:D100,F$1:F1,0)))/(D2:D100<>""),1)),"")

 

[kweaver]

In Aggregate, 15 means SMALL, and 6 means ignore error values. The first row does not need any headers but must exist. Headers optional.

Are you getting an error and, if so, what is it? Seems to work here:

Book3
	A	B	C	D	E	F
1	Optional	Optional	Optional	Optional		Optional
2	London	Car	Mary	London - Car - Mary		London - Car - Mary
3	London	Bike	Joseph	London - Bike - Joseph		London - Bike - Joseph
4	Liverpool	Car	Wee Donkey	Liverpool - Car - Wee Donkey		Liverpool - Car - Wee Donkey
5	Manchester	Car	Joseph	Manchester - Car - Joseph		Manchester - Car - Joseph
6	Liverpool	Car	Wee Donkey	Liverpool - Car - Wee Donkey		London - Car - Joseph
7	London	Car	Joseph	London - Car - Joseph		Manchester - Bike - Wee Donkey
8	Manchester	Bike	Wee Donkey	Manchester - Bike - Wee Donkey		Manchester - Bike - Mary
9	Manchester	Bike	Mary	Manchester - Bike - Mary		London - Bike - Wee Donkey
10	London	Bike	Wee Donkey	London - Bike - Wee Donkey
11	London	Car	Mary	London - Car - Mary
12	London	Car	Mary	London - Car - Mary
Sheet7

Cell Formulas
Range	Formula
D2:D12	D2	=A2&" - "&B2&" - "&C2
F2:F12	F2	=IFERROR(INDEX(D2:D20117,AGGREGATE(15,6,(ROW(A2:A20117)-ROW(A2)+1)/(ISNA(MATCH(D2:D20117,F$1:F1,0)))/(D2:D20117<>""),1)),"")

 

[surkdidat]

Apologies, I think where it was mass data it was taking an age to populate, so hence why I thought it was not bringing up anything!

Thanks for the explanations above - I will need to Google further for my own knowledge on this area as something I have not come across - cheers!