List multiple values in seperate cells
 

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.

List multiple values in seperate cells
 

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,R OWS($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
---
"Brian" wrote:
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.

List multiple values in seperate cells
 

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,R OWS($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?


"Max" wrote:

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,R OWS($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
---
"Brian" wrote:
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.

List multiple values in seperate cells
 

I'm puzzled by how & why you adapted it like that.
That's not what I said.
Have you changed your original question altogether?

Here's a working sample for your easy reference:
http://www.freefilehosting.net/download/3jdbe
Brian_1.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Brian" wrote:
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,R OWS($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?

List multiple values in seperate cells
 

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!



"Max" wrote:

I'm puzzled by how & why you adapted it like that.
That's not what I said.
Have you changed your original question altogether?

Here's a working sample for your easy reference:
http://www.freefilehosting.net/download/3jdbe
Brian_1.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Brian" wrote:
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,R OWS($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?

List multiple values in seperate cells
 

You can paste the direct link to your sample file in reply here
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"Brian" wrote in message
...
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!

List multiple values in seperate cells
 

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

"Max" wrote:

You can paste the direct link to your sample file in reply here
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"Brian" wrote in message
...
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!

List multiple values in seperate cells
 

You mentioned earlier that you had uploaded your file,
I just uploaded a file called "Excel" back to the website you directed
me
to download the example to.

http://www.freefilehosting.net/

After you upload, just copy the "Direct Link" which is generated
Then paste that link into your reply here
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"Brian" wrote in message
...
Sorry....I'm not sure how to do that....
Do I need to save the file to some website first then post the link?