Formula challenge - How to write a formula that returns non-duplicate items from a list?

[mfexcel]

Hi all experts,
I am wondering if I could create a dynamic name range that returns only non-duplicate items from a list?

e.g. in cell A1:A6, I have
Apple
Apple
Apple
Banana
Grape
Grape

I want to create a dynamic name range that returns only Apply,Banana,Grape.

Is it possible?

Any hints are appreciate!

 

[Matt Rogers]

Hi,

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60,75pt'><col width='60,75pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' style='font-family:Arial; font-size:10px; ' >Apple</td><td align='left' style='font-family:Arial; font-size:10px; ' >Apple</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' style='font-family:Arial; font-size:10px; ' >Apple</td><td align='left' style='font-family:Arial; font-size:10px; ' >Banana</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='left' style='font-family:Arial; font-size:10px; ' >Apple</td><td align='left' style='font-family:Arial; font-size:10px; ' >Grape</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='left' style='font-family:Arial; font-size:10px; ' >Banana</td><td align='right' style='font-family:Arial; font-size:10px; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='left' style='font-family:Arial; font-size:10px; ' >Grape</td><td align='right' style='font-family:Arial; font-size:10px; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='left' style='font-family:Arial; font-size:10px; ' >Grape</td><td align='right' style='font-family:Arial; font-size:10px; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='right' style='font-family:Arial; font-size:10px; ' > </td><td align='right' style='font-family:Arial; font-size:10px; ' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>{=INDEX</Span><Span style='color:#0000DD'>(A:A,MIN</Span><Span style='color:#222222'>(IF</Span><Span style='color:#0000DD'>(A1:A99<Span style='color:#0000DD'><</Span>>"",ROW</Span><Span style='color:#222222'>(1:99)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>}</Span></td></tr><tr><td>B2</td><td><Span style='color:#222222'>{=INDEX</Span><Span style='color:#0000DD'>(A:A,MATCH</Span><Span style='color:#222222'>(1,</Span><Span style='color:#0000DD'>(COUNTIF</Span><Span style='color:#222222'>(B$1:B1,A$1:A$99)</Span><Span style='color:#0000DD'>=0)</Span><Span style='color:#222222'>*</Span><Span style='color:#0000DD'>(A$1:A$99<Span style='color:#0000DD'><</Span>>"")</Span><Span style='color:#222222'>,0)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>}</Span></td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Array-Formula!</td></tr><tr><td><span>Ctrl+Shift + Enter </span><span style='text-decoration:underline'></span><span></span></td></tr><tr><td><span></span><span style='font-weight:bold; color:#FF0000'></span><span></span></td></tr></table>

 

[mfexcel]

Hi Matt,
Very impressive. Simple and nice!
I am a bit confused about the array from Countif(B$1:B1,A$1:A$99)... not quite sure how it really works when the size of the Range is smaller than the size of the Criteria. Noted that an array of 1,0 returned that is of the size of the criteria and this is puzzling me...
Appreciate if you could help me better understand that.
Many thanks!!!

 

[Matt Rogers]

Hi mfexel,

Thanks for your friendly feedback.

The range a1:a99 and rows 1:99 is the maximum in this formula.
You can enlarge to A1:a10000 and 1:10000 and more

If your range differently then change the formula to
a1:a(your range) and the rows 1your range)

 

[Aladin Akyurek]

A single formula approach...

B1, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$A$7,SMALL(IF(FREQUENCY(IF($A$1:$A$7<>"",
  MATCH("~"&$A$1:$A$7,$A$1:$A$7&"",0)),ROW($A$1:$A$7)-ROW($A$1)+1),
  ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$1:B1))),"")

 

[taurean]

Here's a discussion on this in Chandoo's Excel challenge section:
Formula Challenge 008 - return distinct/duplicate /unique strings as an array | Chandoo.org Excel Forums - Become Awesome in Excel

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl63, width: 49, bgcolor: transparent"]Products
[/TD]
[TD="class: xl63, width: 43, bgcolor: transparent"]Result
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Apple
[/TD]
[TD="class: xl64, bgcolor: yellow"]Apple
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Apple
[/TD]
[TD="class: xl64, bgcolor: yellow"]Banana
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Apple
[/TD]
[TD="class: xl64, bgcolor: yellow"]Grape
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Banana
[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Grape
[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Grape
[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]********
[/TD]
[TD="class: xl63, bgcolor: transparent"]*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]****
[/TD]
[/TR]
</tbody>[/TABLE]


Formula

B2-> =IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$9)+($A$2:$A$9=""),),0)),"")

Markmzz

 

[Aladin Akyurek]

Here's a discussion on this in Chandoo's Excel challenge section:
Formula Challenge 008 - return distinct/duplicate /unique strings as an array | Chandoo.org Excel Forums - Become Awesome in Excel

Shrivallabha: With some set ups/rewrites, there is a robustness issue (although I did not test them). Another issue, in fact often a non-issue) the number of chars a formula counts. Better criteria would be correctness, efficiency, and robustness. I'd like to add another: flexibility (i.e., extendibility).

 

[taurean]

Shrivallabha: With some set ups/rewrites, there is a robustness issue (although I did not test them). Another issue, in fact often a non-issue) the number of chars a formula counts. Better criteria would be correctness, efficiency, and robustness. I'd like to add another: flexibility (i.e., extendibility).

Aladin, I agree. In fact, I had said so in one of the other challenges here:
Formula Challenge 001 - Return everything in string after first block of numbers | Page 3 | Chandoo.org Excel Forums - Become Awesome in Excel

But on the other side, what I did like was, people keep generating alternatives / perspectives for the situation given.

In fact, I'd like to see some of the guys from here on Chandoo's which I'm sure will add to the challenges.

 

[mfexcel]

Hi Matt,
Thanks again for your feedback. My question was not quite clear. Actually what I am confused is probably sth basic...:P
I don't understand the logic of Countif(A1:A2,B1:B10), where the range is A1:A2 (say "Apple, Banana") while the Criteria is "B1:B10" (say "Apple, Banana, Grape......"). What is the logic behind in return that array with 10 True/False? I have never used a Range as a criteria in Countif before... and I have totally no idea on how it works?
Hope some guidelines!