List multiple values in seperate cells

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.
 
M

Max

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
 
B

Brian

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?
 
B

Brian

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!
 
B

Brian

Sorry....I'm not sure how to do that....
Do I need to save the file to some website first then post the link?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

summing two columns of different data 4
Lookup value in table with multiple results 3
Match Dates in Two Cells and Ignore subsequent Cells 1
Finding a sum by matching two cells with the same identifier 1
Sum Totaled based on Matching Identical Criteria in Worksheet 1
SUMMING UP ONLY ONES IN A LIST 1
Highlight Five Values With Conditional Formula 1
Balance Sheet Formula 5

Top