lookup

S

sandip.dhamapurkar

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
 
B

Biff

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
 
B

babycody

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

sandip.dhamapurkar

Biff said:
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
Click to expand...

********** Yes
How about making it easy and putting the product codes in separate cells?
Click to expand...

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

Biff

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

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 MOREFUNC.XLLhttp://xcell05.free.fr/english/Biff<[email protected]> wrote in messageBiff 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>
 
B

Biff

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?
Click to expand...

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
 
S

sandip.dhamapurkar

Thank you. Will try that

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


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
Click to expand...
 
Top