lookup
 

This is what I have in range A1:B7
Product_code Date
ABC 06/01/06
DEF 06/01/06
GHI 06/02/06
JKL 06/03/06
MNO 06/03/06
PQR 06/03/06

This is what I have in range B11:B14
Date Product_Code
06/01/06 ABC, DEF
06/02/06 GHI
06/03/06 JKL,MNO,PQR

I need formulas in range B11:B14 that can search product code from the
given date in range A11:A14

lookup
 

Do you really mean:

This is what I have in range A11:A14:

Date Product_Code
06/01/06
06/02/06
06/03/06

And this is what I want in range A11:B14

Date Product_Code
06/01/06 ABC, DEF
06/02/06 GHI
06/03/06 JKL,MNO,PQR

How about making it easy and putting the product codes in separate cells?

Biff

wrote in message
ups.com...
This is what I have in range A1:B7
Product_code Date
ABC 06/01/06
DEF 06/01/06
GHI 06/02/06
JKL 06/03/06
MNO 06/03/06
PQR 06/03/06

This is what I have in range B11:B14
Date Product_Code
06/01/06 ABC, DEF
06/02/06 GHI
06/03/06 JKL,MNO,PQR

I need formulas in range B11:B14 that can search product code from the
given date in range A11:A14

lookup
 

To do what Biff suggested select the cells that you where you want to
parse them into the adjacent cells. Go to Data choose Text to Columns.
Select delimited, and click next. Now choose space and comma as your
delimiters. Press next. Format the column with your dates as MDY, and
click finish. Now your information will be seperated into useable data.


--
babycody
------------------------------------------------------------------------
babycody's Profile: http://www.excelforum.com/member.php...o&userid=13120
View this thread: http://www.excelforum.com/showthread...hreadid=555448

lookup
 

Biff wrote:
Do you really mean:

This is what I have in range A11:A14:

Date Product_Code
06/01/06
06/02/06
06/03/06

And this is what I want in range A11:B14

Date Product_Code
06/01/06 ABC, DEF
06/02/06 GHI
06/03/06 JKL,MNO,PQR


********** Yes

How about making it easy and putting the product codes in separate cells?

********** No, I can't because I have 25 columns more to the right of
column B and I cannot move them.

Is it possible to have them in different cell from column AB and then
contatenate them and show them in column B?

Thanks

lookup
 

Is it possible to have them in different cell from column AB and then
contatenate them and show them in column B?

Yes, but that's a lot of extra work! Especially if there might be instances
where you need to concatenate 10 or 20 cells!

Here's how to get the codes into separate cells:

Based on your sample data enter this formula as an array using the key
combination of CTRL,SHIFT,ENTER into cell B12:

=IF(COLUMNS($A:A)<=COUNTIF($B$2:$B$7,$A12),INDEX($ A$2:$A$7,SMALL(IF($B$2:$B$7=$A12,ROW(A$2:A$7)-ROW(A$2)+1),COLUMNS($A:A))),"")Copy across to the number of cells that equals the maximum number of codesfor any one date. In your sample data that would be 3 cells: 6/3/2006 hasthe max number of codes, 3. So, copy across then down as needed.Then you can concatenate those cells.There's a free add-in available that has a "concatenate range" function thatwould make the concatenation a lot easier:Look for wrote in oglegroups.com... Biff wrote: Do you really mean: This is what I have in range A11:A14: Date Product_Code 06/01/06 06/02/06 06/03/06 And this is what I want in range A11:B14 Date Product_Code 06/01/06 ABC, DEF 06/02/06 GHI 06/03/06 JKL,MNO,PQR ********** Yes How about making it easy and putting the product codes in separate cells? ********** No, I can't because I have 25 columns more to the right of column B and I cannot move them. Is it possible to have them in different cell from column AB and then contatenate them and show them in column B? Thanks

lookup
 

Hmmm....don't know why that post got all messed up! Here it is "straightened
out":

Is it possible to have them in different cell from column AB and then
contatenate them and show them in column B?

Yes, but that's a lot of extra work! Especially if there might be instances
where you need to concatenate 10 or 20 cells!

Here's how to get the codes into separate cells:

Based on your sample data enter this formula as an array using the key
combination of CTRL,SHIFT,ENTER into cell B12:

=IF(COLUMNS($A:A)<=COUNTIF($B$2:$B$7,$A12),INDEX($ A$2:$A$7,SMALL(IF($B$2:$B$7=$A12,ROW(A$2:A$7)-ROW(A$2)+1),COLUMNS($A:A))),"")Copy across to the number of cells that equals the maximum number of codesfor any one date. In your sample data that would be 3 cells: 6/3/2006 hasthe max number of codes, 3. So, copy across then down as needed.Then you can concatenate those cells.There's a free add-in available thathas a "concatenate range" function that would make the concatenation a loteasier:Look for MOREFUNC.XLLhttp://xcell05.free.fr/english/Biff

lookup
 

Thank you. Will try that


Biff wrote:
Hmmm....don't know why that post got all messed up! Here it is "straightened
out":

Is it possible to have them in different cell from column AB and then
contatenate them and show them in column B?

Yes, but that's a lot of extra work! Especially if there might be instances
where you need to concatenate 10 or 20 cells!

Here's how to get the codes into separate cells:

Based on your sample data enter this formula as an array using the key
combination of CTRL,SHIFT,ENTER into cell B12:

=IF(COLUMNS($A:A)<=COUNTIF($B$2:$B$7,$A12),INDEX($ A$2:$A$7,SMALL(IF($B$2:$B$7=$A12,ROW(A$2:A$7)-ROW(A$2)+1),COLUMNS($A:A))),"")Copy across to the number of cells that equals the maximum number of codesfor any one date. In your sample data that would be 3 cells: 6/3/2006 hasthe max number of codes, 3. So, copy across then down as needed.Then you can concatenate those cells.There's a free add-in available thathas a "concatenate range" function that would make the concatenation a loteasier:Look for MOREFUNC.XLLhttp://xcell05.free.fr/english/Biff