Lookup with multiple results, without duplicates

My data looks like this:

Num1 Num2
56 5
100 7
46 7
46 7
88 7
100 7
75 8

What I need as a result, looking up the value 7 in Num2:

100 46 88 (separate cells in a row)

I found the quoted material (see below) in another post, but that returns:

100 46 46 88 100

Is there anyway to eliminate the duplicates?

> Assumptions:
>
> A1:B5 contains your source data
>
> First row contains your headers/labels
>
>
> Formula:
>
> E2, copied across:
>
> =IF(COLUMNS($E1:E1)<=COUNTIF($A$2:$A$5,$D2),INDEX($B$2:$B$5,SMALL(IF($A$2
> :$A$5=$D2,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($E1:E1))),"")
>
> ....where D2 contains the program name of interest. The formula needs to
> be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, if you
> need to, you can enter your list of program names in Column D, starting
> at D2, enter the formula in E2, copy across and down.

Originally Posted by Rothman

My data looks like this:

Num1 Num2
56 5
100 7
46 7
46 7
88 7
100 7
75 8

What I need as a result, looking up the value 7 in Num2:

100 46 88 (separate cells in a row)

I found the quoted material (see below) in another post, but that returns:

100 46 46 88 100

Is there anyway to eliminate the duplicates?

> Assumptions:
>
> A1:B5 contains your source data
>
> First row contains your headers/labels
>
>
> Formula:
>
> E2, copied across:
>
> =IF(COLUMNS($E1:E1)<=COUNTIF($A$2:$A$5,$D2),INDEX($B$2:$B$5,SMALL(IF($A$2
> :$A$5=$D2,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($E1:E1))),"")
>
> ....where D2 contains the program name of interest. The formula needs to
> be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, if you
> need to, you can enter your list of program names in Column D, starting
> at D2, enter the formula in E2, copy across and down.

You also can try the formula below:
Suppose in Column A you have your NUM1, column B - NUM2
In column C you enter formula:
=IF(COUNTIF(B$1:B$7,B1)>1,IF(COUNTIF(A1:A7,A1)=1,A1,""),"")
(not just ENTER but CTRL+SHIFT+ENTER)
then Select so many cell as you need and press CTRL+D)

Or using Advanced Filter

Num1 Num2
56 5
100 7
46 7
46 7
88 7
100 7
75 8

Criteria
Test
FALSE

Num1
100
46
88

I assume you already applied the names Num1, Num2
Select Num1 and its data cells (8 cells) and give it the name Database.
Select Test and False (2 cells) and give it the name Criteria.
In the cell that says FALSE, enter this formula:
=OFFSET(Num1,,1,1)=7
Data > Filter > Advanced Filter
Click on Copy to another location
Click on Unique records only
Fill in Copy to
List range and Criteria range have been filled automatically.

Assuming that A2:B8 contains your data, try the following...

In D2, enter: 7

In E2, enter:

=COUNT(1/FREQUENCY(IF(B2:B8=D2,A2:A8),IF(B2:B8=D2,A2:A8)))

....confirmed with CONTROL+SHIFT+ENTER.

In F2, leave empty

In G2, enter and copy across:

=IF(COLUMNS($G2:G2)<=$E2,INDEX($A$2:$A$8,MATCH(0,IF($B$2:$B$8=$D2,COUNTIF
($F2:F2,$A$2:$A$8)),0)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
Rothman <[email protected]> wrote:

> My data looks like this:
>
> Num1 Num2
> 56 5
> 100 7
> 46 7
> 46 7
> 88 7
> 100 7
> 75 8
>
> What I need as a result, looking up the value 7 in Num2:
>
> 100 46 88 (separate cells in a row)