Unique Formula for 2 columns

albertan

Board Regular
Joined
Nov 16, 2014
Messages
66
Office Version
  1. 365
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
ABCDEFGH
1CountyCounty
2SurreyNorthamptonshireSurrey
3Greater LondonStaffordshireGreater London
4SomersetWiltshireSomerset
5LancashireMerseysideLancashire
6HampshireNorfolkHampshire
7Greater LondonHertfordshireWest Yorkshire
8West YorkshireNottinghamshireHertfordshire
9HampshireEast Riding of YorkshireTyne and Wear
10HertfordshireHertfordshireDerbyshire
11Tyne and WearBerkshireLeicestershire
12DerbyshireGreater LondonCounty Durham
13LeicestershireHampshireWest Sussex
14County DurhamEssexLincolnshire
15Greater LondonEssexBerkshire
16West SussexWarwickshireSouth Yorkshire
17Greater LondonBerkshireWest Midlands
18LincolnshireSurreyNottinghamshire
19BerkshireTyne and WearNorthamptonshire
20LeicestershireWest YorkshireStaffordshire
21South YorkshireWest MidlandsWiltshire
22SurreyCumbriaMerseyside
23LeicestershireGreater LondonNorfolk
24West MidlandsGreater LondonEast Riding of Yorkshire
25NottinghamshireGreater LondonEssex
26Warwickshire
27Cumbria
28
Main
Cell Formulas
RangeFormula
H2:H27H2=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.
 
Upvote 0
Try this:

Book4 (version 1).xlsb
ABCDEFGH
1CountyCountyUniques
2SurreyNorthamptonshireBerkshire
3Greater LondonStaffordshireCounty Durham
4SomersetWiltshireCumbria
5LancashireMerseysideDerbyshire
6HampshireNorfolkEast Riding of Yorkshire
7Greater LondonHertfordshireEssex
8West YorkshireNottinghamshireGreater London
9HampshireEast Riding of YorkshireHampshire
10HertfordshireHertfordshireHertfordshire
11Tyne and WearBerkshireLancashire
12DerbyshireGreater LondonLeicestershire
13LeicestershireHampshireLincolnshire
14County DurhamEssexMerseyside
15Greater LondonEssexNorfolk
16West SussexWarwickshireNorthamptonshire
17Greater LondonBerkshireNottinghamshire
18LincolnshireSurreySomerset
19BerkshireTyne and WearSouth Yorkshire
20LeicestershireWest YorkshireStaffordshire
21South YorkshireWest MidlandsSurrey
22SurreyCumbriaTyne and Wear
23LeicestershireGreater LondonWarwickshire
24West MidlandsGreater LondonWest Midlands
25NottinghamshireGreater LondonWest Sussex
26SurreyWest Yorkshire
27Wiltshire
Sheet5
Cell Formulas
RangeFormula
H2:H27H2=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.
 
Upvote 0
If your lists are not too large ..

21 09 01.xlsm
ABCDEFGH
1CountyCountyUnigue
2SurreyNorthamptonshireSurrey
3Greater LondonStaffordshireGreater London
4SomersetWiltshireSomerset
5LancashireMerseysideLancashire
6HampshireNorfolkHampshire
7Greater LondonHertfordshireWest Yorkshire
8West YorkshireNottinghamshireHertfordshire
9HampshireEast Riding of YorkshireTyne and Wear
10HertfordshireHertfordshireDerbyshire
11Tyne and WearBerkshireLeicestershire
12DerbyshireGreater LondonCounty Durham
13LeicestershireHampshireWest Sussex
14County DurhamEssexLincolnshire
15Greater LondonEssexBerkshire
16West SussexWarwickshireSouth Yorkshire
17Greater LondonBerkshireWest Midlands
18LincolnshireSurreyNottinghamshire
19BerkshireTyne and WearNorthamptonshire
20LeicestershireWest YorkshireStaffordshire
21South YorkshireWest MidlandsWiltshire
22SurreyCumbriaMerseyside
23LeicestershireGreater LondonNorfolk
24West MidlandsGreater LondonEast Riding of Yorkshire
25NottinghamshireEssex
26Warwickshire
27Cumbria
28
Unique List
Cell Formulas
RangeFormula
H2:H27H2=UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,A2:A25,D2:D24)&"</c></p>","//c"))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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