Unique list without office 365

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,538
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I want to create a unique list of names from two different columns

A and B are source and expected result is in column D

Book6
ABCDE
1List1List2Unique
2DaveDaveDave
3DaveDaveDwayne
4DwayneDaveJoy
5DwayneDwayneAmy
6DwayneJoyRonny
7DwayneJoyCary
8JoyJoy
9JoyJoy
10JoyJoy
11AmyRonny
12AmyCary
13Ronny
14
15
Sheet1
 
If you have more columns you should the indirect/text formula, more dynamic
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Here is one

Map1
ABCDE
1col1col2col3Unique
2atttblablaa
3basdfasdfblablattt
4ctttabcblabla
5dhiabcdb
6ethisabcasdfasdf
7fthisaaaaac
8gthisqwertyabc
9aisqwertyd
10aislolhi
11aforlolabcd
12aforExcele
13baExcelthis
14btestformulaf
15barrayaaaaa
16carrayg
17cqwerty
18cis
19lol
20for
21Excel
22test
23formula
24array
Blad1
Cell Formulas
RangeFormula
E2:E24E2=INDIRECT(TEXT(MIN(IF(($A$2:$C$18<>"")*(COUNTIF($E$1:E1,$A$2:$C$18)=0),ROW($2:$18)*100+COLUMN($A:$C),EXP(99))),"R00K00"),)
 
Upvote 0
You only need to change "R00K00" to "R00C00"

Xl2BB did not convert that one.
 
Upvote 0
Hello @AhoyNC
Do you know why it's not working

Book1
ABCDEFGHI
1List
21926919269
35/5/202219269
4S.NO19269
5A11919269
6A12019269
7#DIV/0!
81926919269#DIV/0!
95/5/20225/5/202219269
10S.NOS.NO19269
11B29B21719269
12B210B22019269
13B211B21819269
14B212B21919269
15B213B22119269
16B214B22219269
17#DIV/0!
181926919269#DIV/0!
195/5/20225/5/202219269
20S.NOS.NO19269
21S133S14119269
22S136S14419269
23#DIV/0!
24#DIV/0!
251926919269#DIV/0!
265/5/20225/5/202219269
27S.NOS.NO19269
28S41S4919269
29S44S41019269
30S42S41119269
31S43S41219269
32S45S41319269
33S46S41419269
34#DIV/0!
351926919269#DIV/0!
365/5/20225/5/202219269
37S.NOS.NO19269
38S418S42519269
39S417S42819269
40S41919269
41S4201926919269
42S4215/5/202219269
43S422S.NO19269
44S4260
4519269S4270
465/5/2022S42919269
47S.NOS43019269
48S425S43219269
49S428S42419269
50#DIV/0!
511926919269#DIV/0!
525/5/20225/5/202219269
53S.NOS.NO19269
54S441S43419269
55S444S43519269
56S4150
5719269S4160
585/5/2022S4719269
59S.NOS4819269
60S5219269
61S531926919269
62S555/5/202219269
63S51S.NO19269
64S56S43319269
65S54S43619269
Sheet1
Cell Formulas
RangeFormula
I2:I65I2=IFNA(IFERROR(LOOKUP(2,1/(IF($A$2:$A$500<>"",COUNTIF($I$1:I1,$A$2:$A$500)=0)),$A$2:$A$500),LOOKUP(2,1/(IF($D$2:$D$500<>"",COUNTIF($I$1:I1,$D$2:$D$500)=0)),$D$2:$D$500)),"")
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
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