return multiple entries from vlookup
 

I have the following data in columns B and C, respectively:

B: Occurances, 3, 3, 2, 1, 4, 4, 2, 4, 3, 4
C: Name, adam, brad, chuck, dale, ed, frank, george, hal, john, leo

I want to sort it by occurances, so I added in column A, values 1 through
10, and in the second column to the right of names (column E - I have other
data in column D), I want the occurances to be in numeric order, so I use the
following formula and drag it down to the last row of data:

Large($B$2:$B$10,A2), giving me what I want:

4, 4, 4, 4, 3, 3, 3, 2, 2, 1

Now I want to match up the corresponding occurance as listed in column E
with a corresponding name. Using =VLOOKUP(E2,$B$2:$C$11,2,FALSE) in column F
and dragging to the last row of data, I get:

ed, ed, ed, ed, adam, adam, adam, chuck, chuck, dale

when what I want in column F is:

ed, frank, hal, leo, adam, brad, john, chuck, george, dale.

The list of names will remain the same, but the occurance associated with
any name could change, so my formula needs to be flexible for that
possibility. I'm thinking that for each row, I should be able to do a
vlookup, using the original range minus the row(s) of data that contain any
previously returned name. For example, in the row I expect to see the name
"brad", the vlookup function should only be looking at rows 2,3,4,7,9 of the
original dataset of $B$2:$C$11 since the names in the other rows have already
been returned. Maybe there is an easier way...if so, I'd love to hear it.
Here is what the final matrix should look like:

1 3 adam empty 4 ed
2 3 brad empty 4 frank
3 2 chuck empty 4 hal
4 1 dale empty 4 leo
5 4 ed empty 3 adam
6 4 frank empty 3 brad
7 2 george empty 3 john
8 4 hal empty 2 chuck
9 3 john empty 2 george
10 4 leo empty 1 dale

return multiple entries from vlookup
 

With the assumption that the combination of cells B and C will be unique
(that is, you won't have "3,Adam" listed twice, for instance), here's my
approach...
In column A, create a formula that calculates the correct sequence number so
that "4,Ed" gets a 0, "4,Frank" a 1, etc.
That formula, entered in A2 and copied down to fill A2:A11 is
=COUNTIF($B$2:$B$11,"" &
B2)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11<C2)). The COUNTIF piece just
counts how many entries have fewer occurances than the current row. The
SUMPRODUCT piece breaks the tie among those with the same number of
occurances by counting how many with the same number of occurances have names
that come earlier in the alphabet.
Then in columns E and F, just do a normal vlookup. In E2 and F2, the
formulas are
=VLOOKUP(ROW()-2,$A$2:$C$11,2,0) and
=VLOOKUP(ROW()-2,$A$2:$C$11,3,0). Copy E2:F2 down through E11:F11.
--Bruce

"1brad19" wrote:

I have the following data in columns B and C, respectively:

B: Occurances, 3, 3, 2, 1, 4, 4, 2, 4, 3, 4
C: Name, adam, brad, chuck, dale, ed, frank, george, hal, john, leo

I want to sort it by occurances, so I added in column A, values 1 through
10, and in the second column to the right of names (column E - I have other
data in column D), I want the occurances to be in numeric order, so I use the
following formula and drag it down to the last row of data:

Large($B$2:$B$10,A2), giving me what I want:

4, 4, 4, 4, 3, 3, 3, 2, 2, 1

Now I want to match up the corresponding occurance as listed in column E
with a corresponding name. Using =VLOOKUP(E2,$B$2:$C$11,2,FALSE) in column F
and dragging to the last row of data, I get:

ed, ed, ed, ed, adam, adam, adam, chuck, chuck, dale

when what I want in column F is:

ed, frank, hal, leo, adam, brad, john, chuck, george, dale.

The list of names will remain the same, but the occurance associated with
any name could change, so my formula needs to be flexible for that
possibility. I'm thinking that for each row, I should be able to do a
vlookup, using the original range minus the row(s) of data that contain any
previously returned name. For example, in the row I expect to see the name
"brad", the vlookup function should only be looking at rows 2,3,4,7,9 of the
original dataset of $B$2:$C$11 since the names in the other rows have already
been returned. Maybe there is an easier way...if so, I'd love to hear it.
Here is what the final matrix should look like:

1 3 adam empty 4 ed
2 3 brad empty 4 frank
3 2 chuck empty 4 hal
4 1 dale empty 4 leo
5 4 ed empty 3 adam
6 4 frank empty 3 brad
7 2 george empty 3 john
8 4 hal empty 2 chuck
9 3 john empty 2 george
10 4 leo empty 1 dale

return multiple entries from vlookup
 

That's fantastic...a little modification to my numbers and viola! Thanks so
much.

"bpeltzer" wrote:

With the assumption that the combination of cells B and C will be unique
(that is, you won't have "3,Adam" listed twice, for instance), here's my
approach...
In column A, create a formula that calculates the correct sequence number so
that "4,Ed" gets a 0, "4,Frank" a 1, etc.
That formula, entered in A2 and copied down to fill A2:A11 is
=COUNTIF($B$2:$B$11,"" &
B2)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11<C2)). The COUNTIF piece just
counts how many entries have fewer occurances than the current row. The
SUMPRODUCT piece breaks the tie among those with the same number of
occurances by counting how many with the same number of occurances have names
that come earlier in the alphabet.
Then in columns E and F, just do a normal vlookup. In E2 and F2, the
formulas are
=VLOOKUP(ROW()-2,$A$2:$C$11,2,0) and
=VLOOKUP(ROW()-2,$A$2:$C$11,3,0). Copy E2:F2 down through E11:F11.
--Bruce

"1brad19" wrote:

I have the following data in columns B and C, respectively:

B: Occurances, 3, 3, 2, 1, 4, 4, 2, 4, 3, 4
C: Name, adam, brad, chuck, dale, ed, frank, george, hal, john, leo

I want to sort it by occurances, so I added in column A, values 1 through
10, and in the second column to the right of names (column E - I have other
data in column D), I want the occurances to be in numeric order, so I use the
following formula and drag it down to the last row of data:

Large($B$2:$B$10,A2), giving me what I want:

4, 4, 4, 4, 3, 3, 3, 2, 2, 1

Now I want to match up the corresponding occurance as listed in column E
with a corresponding name. Using =VLOOKUP(E2,$B$2:$C$11,2,FALSE) in column F
and dragging to the last row of data, I get:

ed, ed, ed, ed, adam, adam, adam, chuck, chuck, dale

when what I want in column F is:

ed, frank, hal, leo, adam, brad, john, chuck, george, dale.

The list of names will remain the same, but the occurance associated with
any name could change, so my formula needs to be flexible for that
possibility. I'm thinking that for each row, I should be able to do a
vlookup, using the original range minus the row(s) of data that contain any
previously returned name. For example, in the row I expect to see the name
"brad", the vlookup function should only be looking at rows 2,3,4,7,9 of the
original dataset of $B$2:$C$11 since the names in the other rows have already
been returned. Maybe there is an easier way...if so, I'd love to hear it.
Here is what the final matrix should look like:

1 3 adam empty 4 ed
2 3 brad empty 4 frank
3 2 chuck empty 4 hal
4 1 dale empty 4 leo
5 4 ed empty 3 adam
6 4 frank empty 3 brad
7 2 george empty 3 john
8 4 hal empty 2 chuck
9 3 john empty 2 george
10 4 leo empty 1 dale