Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 683
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 683
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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?


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 683
Default 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?




  #6   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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!



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 683
Default 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!




  #8   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
seperate two values fromone cell into 2 cells Darius Excel Worksheet Functions 3 February 13th 07 06:02 PM
Help needed replacing multiple cells from a list of values. Emoshag Excel Discussion (Misc queries) 6 July 6th 06 09:15 PM
How to seperate 2 values to 2 different cells nander Excel Discussion (Misc queries) 2 April 27th 06 08:06 PM
Search multiple values & return single value - seperate worksheets JANA Excel Worksheet Functions 4 October 27th 05 08:43 PM
How can I look up two seperate values/cells and return the value . Barbara Excel Worksheet Functions 2 December 6th 04 10:59 PM


All times are GMT +1. The time now is 11:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"