to correct a conversion merging formula taking cl but not litres

[RAM72]

I have a worksheet which splits column K,l G,and merge them in column J

However I have trouble with description of wines in which there are 37.5 cl ,and 75 cl but there is an additional conversion of 1L for wines , tried to add in the red line but did not work

Can anyone assist should read 12*2*1L as per row 11which causing me trouble .


At row 56 to 60 are description from 1L,1.5l,2L,5L(wines), I have tried to modify but no success as incorrect arguments.

Could please assist
https://www.excelforum.com/excel-fo...merging-formula-taking-cl-but-not-litres.html

Thank you in advance

 

[RAM72]

I have a worksheet which splits column K,l G,and merge them in column J

However I have trouble with description of wines in which there are 37.5 cl ,and 75 cl but there is an additional conversion of 1L for wines , tried to add in the red line but did not work

Can anyone assist should read 12*2*1L as per row 11which causing me trouble .


At row 56 to 60 are description from 1L,1.5l,2L,5L(wines), I have tried to modify but no success as incorrect arguments.

Could please assist
https://www.excelforum.com/excel-fo...merging-formula-taking-cl-but-not-litres.html

Thank you in advance

Bumped

 

[bosco_yip]

Try,

In J2, copied down :

=IF(LEFT(H2)="2",IF(I2="WINES",K2&"*"&L2&"*"&IFERROR(LOOKUP(1,-FIND({37.5;75;"5L";"2L";"1.5L";"1L"},G2),{"37.5CL";"75CL";"5L";"2L";"1.5L";"1L"}),"75CL"),IF(U2="*",M2&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G2,LOOKUP(9^9,FIND("X",G2,ROW($1:$99)))-1),{1,2,3}))&"*",K2&"*"&L2&"*")&LOOKUP(1,-FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5"},G2),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L"})),"")

Regards
Bosco

 

[RAM72]

Try,

In J2, copied down :

=IF(LEFT(H2)="2",IF(I2="WINES",K2&"*"&L2&"*"&IFERROR(LOOKUP(1,-FIND({37.5;75;"5L";"2L";"1.5L";"1L"},G2),{"37.5CL";"75CL";"5L";"2L";"1.5L";"1L"}),"75CL"),IF(U2="*",M2&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G2,LOOKUP(9^9,FIND("X",G2,ROW($1:$99)))-1),{1,2,3}))&"*",K2&"*"&L2&"*")&LOOKUP(1,-FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5"},G2),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L"})),"")

Regards
Bosco

Tried work well thank you ,but on real data I having some issues,

e.g wines 3L,2.5L,3.5L

Fruit juice 2 L and

ice tea with 2L

Can formula be adjusted accordingly.

When items are with G e.g peanuts, converting to CL .

If it is an issue ,I will del manually

 

[RAM72]

=IF(LEFT(H276)="2",IF(I276="WINES",K276&"*"&L276&"*"&IFERROR(LOOKUP(1,-FIND({37.5;75;"5L";"2L";"1.5L";"1L";"1L5";"3L"},G276),{"37.5CL";"75CL";"5L";"2L";"1.5L";"1L";"1L5";"3L"}),"75CL"),IF(U276="*",M276&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G276,LOOKUP(9^9,FIND("X",G276,ROW($1:$99)))-1),{1,2,3}))&"*",K276&"*"&L276&"*")&LOOKUP(1,-FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G276),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1L5";"2L"})),"")

Sort it out but just remain when there are grams