Unique Formula for 2 columns

[albertan]

I was thinking that I can use Unique formula and get unique list from 2 separate columns, but I struggled with this one. Can I get some help here?

Thank you

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	County			County
2	Surrey			Northamptonshire				Surrey
3	Greater London			Staffordshire				Greater London
4	Somerset			Wiltshire				Somerset
5	Lancashire			Merseyside				Lancashire
6	Hampshire			Norfolk				Hampshire
7	Greater London			Hertfordshire				West Yorkshire
8	West Yorkshire			Nottinghamshire				Hertfordshire
9	Hampshire			East Riding of Yorkshire				Tyne and Wear
10	Hertfordshire			Hertfordshire				Derbyshire
11	Tyne and Wear			Berkshire				Leicestershire
12	Derbyshire			Greater London				County Durham
13	Leicestershire			Hampshire				West Sussex
14	County Durham			Essex				Lincolnshire
15	Greater London			Essex				Berkshire
16	West Sussex			Warwickshire				South Yorkshire
17	Greater London			Berkshire				West Midlands
18	Lincolnshire			Surrey				Nottinghamshire
19	Berkshire			Tyne and Wear				Northamptonshire
20	Leicestershire			West Yorkshire				Staffordshire
21	South Yorkshire			West Midlands				Wiltshire
22	Surrey			Cumbria				Merseyside
23	Leicestershire			Greater London				Norfolk
24	West Midlands			Greater London				East Riding of Yorkshire
25	Nottinghamshire			Greater London				Essex
26								Warwickshire
27								Cumbria
28
Main

Cell Formulas
Range	Formula
H2:H27	H2	=LET(Rngs,(A2:A25,D2:D25),Rws,ROWS(A2:A25),Qty, SEQUENCE(Rws*AREAS(Rngs),,0),Ary,INDEX(Rngs,MOD(Qty,Rws)+1,1,INT(Qty/Rws)+1),UNIQUE(FILTER(Ary,Ary<>"")))
Dynamic array formulas.

 

[albertan]

Thank you. Will do. Unfortunately I don't have LET function yet.

 

[Eric W]

Try this:

Book4 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	County			County				Uniques
2	Surrey			Northamptonshire				Berkshire
3	Greater London			Staffordshire				County Durham
4	Somerset			Wiltshire				Cumbria
5	Lancashire			Merseyside				Derbyshire
6	Hampshire			Norfolk				East Riding of Yorkshire
7	Greater London			Hertfordshire				Essex
8	West Yorkshire			Nottinghamshire				Greater London
9	Hampshire			East Riding of Yorkshire				Hampshire
10	Hertfordshire			Hertfordshire				Hertfordshire
11	Tyne and Wear			Berkshire				Lancashire
12	Derbyshire			Greater London				Leicestershire
13	Leicestershire			Hampshire				Lincolnshire
14	County Durham			Essex				Merseyside
15	Greater London			Essex				Norfolk
16	West Sussex			Warwickshire				Northamptonshire
17	Greater London			Berkshire				Nottinghamshire
18	Lincolnshire			Surrey				Somerset
19	Berkshire			Tyne and Wear				South Yorkshire
20	Leicestershire			West Yorkshire				Staffordshire
21	South Yorkshire			West Midlands				Surrey
22	Surrey			Cumbria				Tyne and Wear
23	Leicestershire			Greater London				Warwickshire
24	West Midlands			Greater London				West Midlands
25	Nottinghamshire			Greater London				West Sussex
26	Surrey							West Yorkshire
27								Wiltshire
Sheet5

Cell Formulas
Range	Formula
H2:H27	H2	=SORT(UNIQUE(IF(SEQUENCE(COUNTA(A2:A100,D2:D100))<=COUNTA(A2:A100),INDEX(A2:A100,SEQUENCE(COUNTA(A2:A100,D2:D100))),INDEX(D2:D100,SEQUENCE(COUNTA(A2:A100,D2:D100))-COUNTA(A2:A100)))))
Dynamic array formulas.

 

[Peter_SSs]

If your lists are not too large ..

21 09 01.xlsm
	A	B	C	D	E	F	G	H
1	County			County				Unigue
2	Surrey			Northamptonshire				Surrey
3	Greater London			Staffordshire				Greater London
4	Somerset			Wiltshire				Somerset
5	Lancashire			Merseyside				Lancashire
6	Hampshire			Norfolk				Hampshire
7	Greater London			Hertfordshire				West Yorkshire
8	West Yorkshire			Nottinghamshire				Hertfordshire
9	Hampshire			East Riding of Yorkshire				Tyne and Wear
10	Hertfordshire			Hertfordshire				Derbyshire
11	Tyne and Wear			Berkshire				Leicestershire
12	Derbyshire			Greater London				County Durham
13	Leicestershire			Hampshire				West Sussex
14	County Durham			Essex				Lincolnshire
15	Greater London			Essex				Berkshire
16	West Sussex			Warwickshire				South Yorkshire
17	Greater London			Berkshire				West Midlands
18	Lincolnshire			Surrey				Nottinghamshire
19	Berkshire			Tyne and Wear				Northamptonshire
20	Leicestershire			West Yorkshire				Staffordshire
21	South Yorkshire			West Midlands				Wiltshire
22	Surrey			Cumbria				Merseyside
23	Leicestershire			Greater London				Norfolk
24	West Midlands			Greater London				East Riding of Yorkshire
25	Nottinghamshire							Essex
26								Warwickshire
27								Cumbria
28
Unique List

Cell Formulas
Range	Formula
H2:H27	H2	=UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,A2:A25,D2:D24)&"</c></p>","//c"))
Dynamic array formulas.