Retrieving data from a list without skipping rows

  • Thread starter Thread starter John Richards
  • Start date Start date
J

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
 
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
 
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
 
Back
Top