List only unique occurances in small function

R

rhaney

Board Regular
Joined
Sep 27, 2009
Messages
64
Good afternoon.

I have a formula which works correctly upon confirming with control+shift+enter, except that I need it to only list the unique values.

As it stands now, it will list all data in the range such as:

a
a
a
b
b
c
c
c
c

and I need it to list only unique occurrances of the data such as:

a
b
c

=IF(ISERROR(INDEX('Master store info'!$A$1:$AC$3000,SMALL(IF('Master store info'!$B$1:$B$3000=$F$5,ROW('Master store info'!$B$1:$B$3000)),ROW(1:1)),3)),"",INDEX('Master store info'!$A$1:$AC$3000,SMALL(IF('Master store info'!$B$1:$B$3000=$F$5,ROW('Master store info'!$B$1:$B$3000)),ROW(1:1)),3))

Thank you!

Robert
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Col01</td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">a</td><td style="text-align: center;;">a</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">a</td><td style="text-align: center;;">b</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">a</td><td style="text-align: center;;">c</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">b</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">c</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B2</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">0,COUNTIF(<font color="Purple">B$1:$B1,$A$2:$A$10</font>),0</font>)</font>),"",INDEX(<font color="Red">$A$2:$A$10,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">B$1:$B1,$A$2:$A$10</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 />
Markmzz
 
Upvote 0
rhaney said:
Good afternoon.

I have a formula which works correctly upon confirming with control+shift+enter, except that I need it to only list the unique values.

As it stands now, it will list all data in the range such as:

a
a
a
b
b
c
c
c
c

and I need it to list only unique occurrances of the data such as:

a
b
c

=IF(ISERROR(INDEX('Master store info'!$A$1:$AC$3000,SMALL(IF('Master store info'!$B$1:$B$3000=$F$5,ROW('Master store info'!$B$1:$B$3000)),ROW(1:1)),3)),"",INDEX('Master store info'!$A$1:$AC$3000,SMALL(IF('Master store info'!$B$1:$B$3000=$F$5,ROW('Master store info'!$B$1:$B$3000)),ROW(1:1)),3))

Thank you!

Robert
Click to expand...
A generic approach...

<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=240><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3015" width=85><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3242" width=91><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2867594 class=xl66 width=85> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=91 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Distinct Items</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>a</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>b</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>c</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2867594 class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR></TBODY></TABLE>

C1, Control+shift+enter, not just enter:
Code: 
=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",
   MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0)),
    ROW($A$2:$A$10)-ROW($A$2)+1),1))

C3, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($C$3:C3)<=$C$1,INDEX($A$2:$A$10,
   SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",
    MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0)),
     ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),
      ROWS($C$3:C3))),"")
 
Upvote 0
With A2:A10 containing this text
a
a
a
b
b
c
c
c
c

This regular formula begins the list of unique list items:
Code: 
B2: =IFERROR(LOOKUP(REPT("Z",255),INDEX($A$2:$A$20,MATCH
(TRUE,INDEX(ISNA(MATCH($A$2:$A$20,$B$1:$B1,0)),0),0))),"")
or...if you have Pre-Excel 2007...this regular formula:
Code: 
B2: =LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($A$2:$A$20,MATCH
(TRUE,INDEX(ISNA(MATCH($A$2:$A$20,$B$1:$B1,0)),0),0))))

Copy either formula down as far as you need.

Is that something you can work with?
 
Upvote 0
Thank you very much for each of your assistance! I very much appreciate the time and expertise that you provided! The solution that Ron provided worked perfectly for me. I was having difficulties with part 2 of Aladin's approach. I will have to figure out what I was doing wrong. I will also need to spend more time with markmzz's answer.
 
Upvote 0
rhaney said:
Thank you very much for each of your assistance! I very much appreciate the time and expertise that you provided! The solution that Ron provided worked perfectly for me. I was having difficulties with part 2 of Aladin's approach. I will have to figure out what I was doing wrong. I will also need to spend more time with markmzz's answer.
Click to expand...

The formula in C3 is:
Rich (BB code): 
=IF(ROWS($C$3:C3)<=$C$1,INDEX($A$2:$A$10,
   SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",
    MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0)),
     ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),
      ROWS($C$3:C3))),"")

which must be confirmed with control+shift+enter, not just enter, then copied down.
 
Upvote 0
Try this ..
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Data</td><td style=";">Unique</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">a</td><td style=";">a</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">a</td><td style=";">b</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">a</td><td style=";">c</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">b</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">b</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">c</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">c</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">c</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">c</td><td style="text-align: right;;"></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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">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: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B2</th><td style="text-align:left">{=T(<font color="Blue">INDEX(<font color="Red">A:A,MIN(<font color="Green">IF(<font color="Purple">COUNTIF(<font color="Teal">$B$1:B1,$A$2:$A$10</font>),2^15,ROW(<font color="Teal">$A$2:$A$10</font>)</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 />
 
Upvote 0
Ron Coderre said:
With A2:A10 containing this text
a
a
a
b
b
c
c
c
c

This regular formula begins the list of unique list items:
Code: 
B2: =IFERROR(LOOKUP(REPT("Z",255),INDEX($A$2:$A$20,MATCH
(TRUE,INDEX(ISNA(MATCH($A$2:$A$20,$B$1:$B1,0)),0),0))),"")
or...if you have Pre-Excel 2007...this regular formula:
Code: 
B2: =LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($A$2:$A$20,MATCH
(TRUE,INDEX(ISNA(MATCH($A$2:$A$20,$B$1:$B1,0)),0),0))))

Copy either formula down as far as you need.

Is that something you can work with?
Click to expand...

Yahya said:
Try this ..
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Data</TD><TD>Unique</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>a</TD><TD>a</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>a</TD><TD>b</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>a</TD><TD>c</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>b</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>b</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>c</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>c</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>c</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>c</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1​


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">{=T(INDEX(A:A,MIN(IF(COUNTIF($B$1:B1,$A$2:$A$10),2^15,ROW($A$2:$A$10)))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>
Click to expand...

Non-benign data can be an issue here: Delete the contents of A4 and/or replace a text entry with a numeric entry, for example.
 
Upvote 0
Valid points, Aladin
If the list may contain blanks (which are not to be listed) and numbers (which ARE to be listed...although, as numeric text), then
these regular formulas should work:

Excel 2007 and later:
Code: 
B2: =IFERROR(LOOKUP(REPT("Z",255),INDEX($A$2:$A$20&"",MATCH(1,INDEX(ISNA
(MATCH($A$2:$A$20&"",$B$1:$B1,0))-($A$2:$A$20=""),0),0))),"")
Pre-Excel 2007:
Code: 
B2: =LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($A$2:$A$20&"",MATCH(1,INDEX(
ISNA(MATCH($A$2:$A$20&"",$B$1:$B1,0))-($A$2:$A$20=""),0),0))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
163
helpcharity
H
W
  • Question Question
VBA Code to Save Worksheets as separate files
Replies
7
Views
316
warleque
W
E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
211
Excel Jason
E
C
  • Solved
index match excel formula to vba code
Replies
9
Views
357
DanteAmor
DanteAmor
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
109
kivikatz
K

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

Share this page

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