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
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!!!
<tbody>
</tbody>
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
Code:
{=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>