Retrieving data from a list without skipping rows

[John Richards]

Given a list with multiple lables in column A and corresponding data in
column B, can I generate separate lists for each label in the original list
without skipping rows that correspond to other labels in the original list?
Example:

---Source list--- Red Blue
A B C D
Red 5 5 3
Blue 3 2
Red 2

It seems that I need some sort of function like VLOOKUP that could put the
result in the next available cell within a range rather than only the cell
that contains the function - hope that's clear.

Thanks
John

 

[Bernie Deitrick]

John,

If your list starts in cell A1, and you have the value "Red" in cell C1, and
"Blue": in cell D1, in cell C2 array-enter the formula (enter using
ctrl-shift-enter)

=INDEX($B:$B,SMALL(IF(($A$2:$A$100=C$1),ROW($A$2:$A$100),1000),ROW()-1))

Adjust the 100s to be at least as high as lowest row with data, and change
the 1000 in the IF function to a value where that row in column B will be
blank. Then go to that cell in column B (B1000 for this example) and enter
a single space. (that's done to prevent a 0 from showing).

Copy the formula across to D2, and down as far as you need, until blank
spaces start showing up.

HTH,
Bernie
MS Excel MVP

 

[John Richards]

Thanks Bernie. It does work but I'm not sure yet exactly how. It will give
me something to play with and hopefully learn a little more about the
functions you used.

John