Unique values with Formula NOT Macro

MDX2013

New Member
Joined
Jan 2, 2018
Messages
8
I have a column of values. Some are duplicate, some are not. I need a formula to return only unique values without blank cells in the column. See example below.

Raw datacurrent outputdesired output
aaa
ab
bbc
cc
c

<tbody>
</tbody>


Edit:
I should also mention that I am creating a template. Therefore, I do not want to use the "remove duplicates" button or any other solution that involves the user.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
Hi, welcome to the forum.

Couple of options, one is an array which requires ctrl+shift+enter to confirm, the other is normal function which can just be entered

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">List</td><td style="text-align: center;;">Unique</td><td style="text-align: center;;">Unique CSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">a</td><td style="text-align: center;;">c</td><td style="text-align: center;;">a</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">a</td><td style="text-align: center;;">b</td><td style="text-align: center;;">b</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">a</td><td style="text-align: center;;">a</td><td style="text-align: center;;">c</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">2,1/(<font color="Green">COUNTIF(<font color="Purple">$B$1:B1,$A$2:$A$9</font>)=0</font>),$A$2:$A$9</font>), ""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$9, MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$C$1:C1, $A$2:$A$9</font>), 0</font>)</font>), ""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
1
aa
2
b
3
ac
4
5
b
6
7
b
8
9
c
10
11
c

<tbody>
</tbody>

b1=IFERROR(INDEX($A$1:$A$11,SMALL(IF(FREQUENCY(IF($A$1:$A$11<>"",MATCH($A$1:$A$11,$A$1:$A$11,0)),ROW($A$1:$A$11)-ROW($A$1)+1),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:A1))),"")

control+shift+enter copy down
 
Last edited:

MDX2013

New Member
Joined
Jan 2, 2018
Messages
8
Worked perfectly!!

Now I just have to find a formula to get the data in a new column with it sorted ascending.

Also, what formula would I use to create a new column with two rows between values? Example:
AA
B
C
B
C

<tbody>
</tbody>

<tbody>
</tbody>
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316

ADVERTISEMENT

Perhaps this will get you started

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">List</td><td style=";">Unique Sorted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">John</td><td style=";">Andrew</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Ian</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Andrew</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Bill</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Bill</td><td style=";">Colin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Bill</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Colin</td><td style=";">Frank</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Colin</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Frank</td><td style=";">Ian</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">John</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=IFERROR(<font color="Blue">IF(<font color="Red">MOD(<font color="Green">ROW(<font color="Purple"></font>), 2</font>)=0, INDEX(<font color="Green">$A$2:$A$10, MATCH(<font color="Purple">SMALL(<font color="Teal">IF(<font color="#FF00FF">COUNTIF(<font color="Navy">$B$1:B1, $A$2:$A$10</font>)=0, COUNTIF(<font color="Navy">$A$2:$A$10, "<"&$A$2:$A$10</font>), ""</font>), 1</font>), COUNTIF(<font color="Teal">$A$2:$A$10, "<"&$A$2:$A$10</font>), 0</font>)</font>), ""</font>), ""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

MDX2013

New Member
Joined
Jan 2, 2018
Messages
8
This works fantastic! But one small issue. I don't always know how long my data is going to be. It could go to A10 or A99. But if i change the range to A99 with empty cells, it messes the formula up.
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
You would definitely be better with a helper cell/column. But...

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">List</td><td style=";">Unique Sorted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">John</td><td style=";">Andrew</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Ian</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Andrew</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Bill</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Bill</td><td style=";">Ian</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Bill</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">John</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=IFERROR(<font color="Blue">IF(<font color="Red">MOD(<font color="Green">ROW(<font color="Purple"></font>), 2</font>)=0, INDEX(<font color="Green">$A$2:INDEX(<font color="Purple">A:A, MATCH(<font color="Teal">"zzz", A:A</font>)</font>), MATCH(<font color="Purple">SMALL(<font color="Teal">IF(<font color="#FF00FF">COUNTIF(<font color="Navy">$B$1:B1, $A$2:INDEX(<font color="Blue">A:A, MATCH(<font color="Red">"zzz", A:A</font>)</font>)</font>)=0, COUNTIF(<font color="Navy">$A$2:INDEX(<font color="Blue">A:A, MATCH(<font color="Red">"zzz", A:A</font>)</font>), "<"&$A$2:INDEX(<font color="Blue">A:A, MATCH(<font color="Red">"zzz", A:A</font>)</font>)</font>), ""</font>), 1</font>), COUNTIF(<font color="Teal">$A$2:INDEX(<font color="#FF00FF">A:A, MATCH(<font color="Navy">"zzz", A:A</font>)</font>), "<"&$A$2:INDEX(<font color="#FF00FF">A:A, MATCH(<font color="Navy">"zzz", A:A</font>)</font>)</font>), 0</font>)</font>), ""</font>), ""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

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
Top