Retrieve One Instance

[mlo356]

Hi All,
I have a formula that gives me a unique list of rows by returning its reference #. The formula I am using (Thanks to this amazing forum) is:

In cell E4 insert

{=INDEX(C:C,SMALL(IF($A$4:$B$20=400,ROW($A$4:$B$20)),ROW(A1)))}

then drag down for the list in the table below.

Notice that I am looking at 2 columns (A and B) and the reference # is returned if either is “400”. I ran into an unexpected problem. For one row, both columns A and B have 400 so instead of a unique list, I now have a delicate reference # in my formula results (Ref # 88888).

Is there a way to modify the formula so that if the same row shows up more than once in the formula, it only keeps one instance therefore making my list unique?

Thanks in advance!!!

	A​	B​	C​	D​	E​
1​
2​
3​	Data 1​	Data 2​	Reference​		Formula Results​
4​	100​	200​	11111​		33333​
5​	200​	100​	22222​		44444​
6​	300​	400​	33333​		88888​
7​	400​	300​	44444​		88888​
8​	100​	500​	55555​		12345​
9​	200​	600​	66666​
10​	300​	800​	77777​
11​	400​	400​	88888​
12​	200​	300​	99999​
13​	400​	700​	12345​

<tbody>
</tbody>

 

[amgis]

Probably not the best answer, but add some lines to add conditional formatting for duplicates. Then add a line to remove cells with the highlighting. You'd have to google the methods.

 

[Weazel]

maybe something like...

Excel 2013

	A	B	C	D	E
1
2
3	Data 1	Data 2	Reference		Formula Results
4	100	200	11111		33333
5	200	100	22222		44444
6	300	400	33333		88888
7	400	300	44444		12345
8	100	500	55555
9	200	600	66666
10	300	800	77777
11	400	400	88888
12	200	300	99999
13	400	700	12345

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula E4 {=IFERROR(INDEX($C$4:$C$13,SMALL(IF(FREQUENCY(IF(($A$4:$A$13=400)+($B$4:$B$13=400),MATCH($C$4:$C$13,$C$4:$C$13,0)),ROW($A$4:$A$13)-ROW($A$4)+1),ROW($A$4:$A$13)-ROW($A$4)+1),ROWS($E$4:E4))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[mlo356]

maybe something like...

Excel 2013

	A	B	C	D	E
1
2
3	Data 1	Data 2	Reference		Formula Results
4	100	200	11111		33333
5	200	100	22222		44444
6	300	400	33333		88888
7	400	300	44444		12345
8	100	500	55555
9	200	600	66666
10	300	800	77777
11	400	400	88888
12	200	300	99999
13	400	700	12345

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula E4 {=IFERROR(INDEX($C$4:$C$13,SMALL(IF(FREQUENCY(IF(($A$4:$A$13=400)+($B$4:$B$13=400),MATCH($C$4:$C$13,$C$4:$C$13,0)),ROW($A$4:$A$13)-ROW($A$4)+1),ROW($A$4:$A$13)-ROW($A$4)+1),ROWS($E$4:E4))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This worked great. Thank You. Not entirely sure how it works but it works. Much Appreciated.