List multiple values in seperate cells

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

In column E and F, I have multiple values and amounts. For example:
Column E Column F
321 $775.00
557 $50.00
395 $45.00
321 $54.00
574 $65.00
321 $85.00

I want to be able to have Excel search for all the dollar amounts that have
a corresponding 321 in Column E, but place these amounts in different cells.
In the example above, I'd like $775.00 to appear in cell A1....$54.00 appear
in cell A2...$85.00 in cell A3 and so on until all dollar amounts with a
corresponding 321 are listed.
Any suggestions on how to do this with using a Filter?
I know Sumproduct will add the amounts together...but I want them listed
seperately....and Vlookup will only return the first dollar amount that
matches that requirement. So I'm not sure what formula to use.
 
One quick play ..
Assume input value for col E will be made in G2, eg: 321
In H2: =IF(E2="","",IF(E2=$G$2,ROW(),""))
Leave H1 blank
In I2:
=IF(ROWS($1:1)>COUNT(H:H),"",INDEX(F:F,SMALL(H:H,ROWS($1:1))))
Select H2:I2, copy down to cover the max expected extent of data in col E.
Col I will return the required bunched results from col F for the input in
G2.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
 
Hi Max....I gave it a try....used the following formula in cell H2
=IF(Y2="","",IF(Y2=321,ROW(),""))
Then put the following in G2:
=IF(ROWS($1:1)>COUNT(P:P),"",INDEX(Y:Y,SMALL(Y:Y,ROWS($1:1))))

I then copied this formula down to several rows.

For some reason it's generating $40.00 in the first cell...then $400.00 in
the next four. Not sure where these amounts are coming from as they are not
the results I'm looking for.
Any suggestions?
 
Thanks Max....
I didn't spell out exactly the format I had the data in....so I figured as
long as I got the correct idea from you, I'd be able to adapt the formula to
the way I needed it to be.
Unfortunately I'm still not getting it to work right....but I can tell I'm
getting close.
I just uploaded a file called "Excel" back to the website you directed me to
download the example to.
In that example, I was hoping to have the amounts $5,024.00 and $1,000.00
show up in cells B5 and B6....but can't figure out what I'm doing wrong.
Can you let me know what you think? (or let me know where I should post this
file so that you can see it)
Thanks again Max!
 
Sorry....I'm not sure how to do that....
Do I need to save the file to some website first then post the link?
 
Back
Top