|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 9th, 2008, 03:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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
July 9th, 2008, 03:33 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
July 10th, 2008, 01:43 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
July 10th, 2008, 08:26 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
July 14th, 2008, 03:34 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
July 15th, 2008, 12:02 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
July 15th, 2008, 01:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
July 15th, 2008, 10:50 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
