complex split formula to merge as per description

L

learn99

New Member
Joined
Jul 11, 2017
Messages
10
I have a formula which looks in column K ,L and G to merge pack size description in J ,
However In G only the conversion units are extracted that is L , cl with its corresponding numbers.

However I having trouble when adding L litres corresponding for wines only , I have tried to add 1 litres but unsucessful.

Can anyone assist to correct the formula .





FGHIJKLMXY
1CODE ARTICLEDESIGNATIONTariff NoDescriptionPack Size DescriptionCNDTQte CndtQTE UVC
217017154BOUTON OR CROUT.AIL&F.HERBE60G19054090TOASTED BREAD206120
317082321CHABRIOR *******S NATURE 250G19059090BISCUITS1225300
417118363PAQUITO SIROP CITRON.VRT 75CL21069060SYRUP6*5*75CL6530
517311278AIX LES BAINS EAU MIN 6X1L522011011MINERAL WATER105*6*1.5L1105105
617311280AIX LES BAINS E.MINERALE 6X1L22011011MINERAL WATER39*6*1L13939
717314311BROCELIANDE EAU MIN6X1L5 FARD22011011MINERAL WATER104*6*1.5L1104104
817314605ONDINE E.DE SOURCE 6X1L5 1/2P22011011MINERAL WATER56*6*1.5L56156
917330954BABV BLC MANGUE E.CLUB22041090WINES6*1*75CL616
1041027155CHABRIOR GENOISE CITRON 150G19053100BISCUITS2410240
1152335060VIN ESPAGNE BLC MOELCALISSOU1L22041090WINES12*2*75CL12224should read 12*2*1L in column J
1252337011VDP CX PIN ROUGE SYRAH22041090WINES6*14*75CL61484
1352337025VDPOC CAB.SAUV RG XPIN 37.5CL22041090WINES12*5*37.5CL12560
1452337073VDP OC MERLOT RGE X PIN BIB5L22041090WINES4*12*75CL41248
1552337077VDP CX PIN ROUGE MERLOT 75CL22041090WINES6*39*75CL639234
1652337300GAMAY RGE CHARENTAIS 75CL22041090WINES12*3*75CL12336
1752337602VDP CX PIN RGE CAB.SAUVIGN75CL22041090WINES6*43*75CL643258
1852338041VDP CX DU PIN BLC CHARDONN75CL22041090WINES6*5*75CL6530

<tbody>
</tbody>
SPLIT SPIRITS (2)​

Worksheet Formulas
CellFormula
J2=IF(LEFT(H2)="2",IF(I2="WINES",K2&"*"&L2&"*"&IF(ISNUMBER(FIND(37.5,G2)),"37.5CL","75CL"),IF(U2="*",M2&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G2,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G2))-2),{1,2,3}))&"*",K2&"*"&L2&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G2),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J3=IF(LEFT(H3)="2",IF(I3="WINES",K3&"*"&L3&"*"&IF(ISNUMBER(FIND(37.5,G3)),"37.5CL","75CL"),IF(U3="*",M3&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G3,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G3))-2),{1,2,3}))&"*",K3&"*"&L3&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G3),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J4=IF(LEFT(H4)="2",IF(I4="WINES",K4&"*"&L4&"*"&IF(ISNUMBER(FIND(37.5,G4)),"37.5CL","75CL"),IF(U4="*",M4&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G4,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G4))-2),{1,2,3}))&"*",K4&"*"&L4&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G4),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J5=IF(LEFT(H5)="2",IF(I5="WINES",K5&"*"&L5&"*"&IF(ISNUMBER(FIND(37.5,G5)),"37.5CL","75CL"),IF(U5="*",M5&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G5,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G5))-2),{1,2,3}))&"*",K5&"*"&L5&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G5),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J6=IF(LEFT(H6)="2",IF(I6="WINES",K6&"*"&L6&"*"&IF(ISNUMBER(FIND(37.5,G6)),"37.5CL","75CL"),IF(U6="*",M6&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G6,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G6))-2),{1,2,3}))&"*",K6&"*"&L6&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G6),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J7=IF(LEFT(H7)="2",IF(I7="WINES",K7&"*"&L7&"*"&IF(ISNUMBER(FIND(37.5,G7)),"37.5CL","75CL"),IF(U7="*",M7&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G7,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G7))-2),{1,2,3}))&"*",K7&"*"&L7&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G7),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J8=IF(LEFT(H8)="2",IF(I8="WINES",K8&"*"&L8&"*"&IF(ISNUMBER(FIND(37.5,G8)),"37.5CL","75CL"),IF(U8="*",M8&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G8,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G8))-2),{1,2,3}))&"*",K8&"*"&L8&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G8),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J9=IF(LEFT(H9)="2",IF(I9="WINES",K9&"*"&L9&"*"&IF(ISNUMBER(FIND(37.5,G9)),"37.5CL","75CL"),IF(U9="*",M9&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G9,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G9))-2),{1,2,3}))&"*",K9&"*"&L9&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G9),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J10=IF(LEFT(H10)="2",IF(I10="WINES",K10&"*"&L10&"*"&IF(ISNUMBER(FIND(37.5,G10)),"37.5CL","75CL"),IF(U10="*",M10&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G10,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G10))-2),{1,2,3}))&"*",K10&"*"&L10&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G10),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J11=IF(LEFT(H11)="2",IF(I11="WINES",K11&"*"&L11&"*"&IF(ISNUMBER(FIND(37.5,G11)),"37.5CL","75CL"),IF(U11="*",M11&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G11,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G11))-2),{1,2,3}))&"*",K11&"*"&L11&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G11),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J12=IF(LEFT(H12)="2",IF(I12="WINES",K12&"*"&L12&"*"&IF(ISNUMBER(FIND(37.5,G12)),"37.5CL","75CL"),IF(U12="*",M12&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G12,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G12))-2),{1,2,3}))&"*",K12&"*"&L12&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G12),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J13=IF(LEFT(H13)="2",IF(I13="WINES",K13&"*"&L13&"*"&IF(ISNUMBER(FIND(37.5,G13)),"37.5CL","75CL"),IF(U13="*",M13&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G13,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G13))-2),{1,2,3}))&"*",K13&"*"&L13&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G13),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J14=IF(LEFT(H14)="2",IF(I14="WINES",K14&"*"&L14&"*"&IF(ISNUMBER(FIND(37.5,G14)),"37.5CL","75CL"),IF(U14="*",M14&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G14,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G14))-2),{1,2,3}))&"*",K14&"*"&L14&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G14),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J15=IF(LEFT(H15)="2",IF(I15="WINES",K15&"*"&L15&"*"&IF(ISNUMBER(FIND(37.5,G15)),"37.5CL","75CL"),IF(U15="*",M15&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G15,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G15))-2),{1,2,3}))&"*",K15&"*"&L15&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G15),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J16=IF(LEFT(H16)="2",IF(I16="WINES",K16&"*"&L16&"*"&IF(ISNUMBER(FIND(37.5,G16)),"37.5CL","75CL"),IF(U16="*",M16&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G16,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G16))-2),{1,2,3}))&"*",K16&"*"&L16&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G16),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J17=IF(LEFT(H17)="2",IF(I17="WINES",K17&"*"&L17&"*"&IF(ISNUMBER(FIND(37.5,G17)),"37.5CL","75CL"),IF(U17="*",M17&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G17,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G17))-2),{1,2,3}))&"*",K17&"*"&L17&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G17),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
J18=IF(LEFT(H18)="2",IF(I18="WINES",K18&"*"&L18&"*"&IF(ISNUMBER(FIND(37.5,G18)),"37.5CL","75CL"),IF(U18="*",M18&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G18,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G18))-2),{1,2,3}))&"*",K18&"*"&L18&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G18),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Hi learn99,
I used the same formula that you had used, and I am getting the J4 and J5 values as 6*5*75CL and 1*105*1.5L respectively. Which excel version are you using???

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>



​
 
Upvote 0
how about the designation in G column:
BABV BLC MANGUE E.CLUB
VDP CX PIN ROUGE SYRAH
VDP OC MERLOT RGE X PIN BIB5L

What about the Description here - since in first 2, nothing is mentioned, while for third one, it says 5L

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Use the following formula in place of the existing formula:

=IF(LEFT(H14)="2",IF(I14="WINES",K14&"*"&L14&"*"&IF(ISNUMBER(FIND(37.5,G14)),"37.5CL",IF(ISNUMBER(FIND("75CL",G14)),"75CL",IF(ISNUMBER(FIND("1L5",G14)),"1.5L",IF(ISNUMBER(FIND("1L",G14)),"1L","75CL")))),IF(U14="*",M14&"*"&LOOKUP(9^9,0+RIGHT(LEFT(G14,LOOKUP(9^9,FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G14))-2),{1,2,3}))&"*",K14&"*"&L14&"*")&LOOKUP(2,1/FIND({25;30;33;50;70;75;100;"1L";"1.5L";"1L5";"2L"},G14),{"25CL";"30CL";"33CL";"50CL";"70CL";"75CL";"100CL";"1L";"1.5L";"1.5L";"2L"})),"")
 
Upvote 0
I have used your formula , we are on the right track the wines with blank should be taken automatic 75CL by default.

I have tested on 1.5L, 2L,3L, 5 L ,need to adjust accordingly

however I notice that beer where they are 3*25cl , it should be taken M only , this was done previously by adding * in column U , it automatically adjust accordingly , this also applies for mineral water .

I have attached a link with full for ease of understanding

https://drive.google.com/file/d/1Mo0XPKo5g7jian4iNLbgHBe7uBtznAa0/view?usp=sharing

but need some adjustments on mineral water , beer and wines when there are no litrages , then take default 75 vl

AEFGHIJKLMNTUVWX
1INVOICE NOfffCODE ARTICLEDESIGNATIONTariff NoDescriptionPack Size DescriptionCNDTQte CndtQTE UVCheader18header24expected resultsChecking
2AAA017017154BOUTON OR CROUT.AIL&F.HERBE60G19054090TOASTED BREAD2061203.25039E+12TRUE
3AAA017017157BOUTON DOR CROUTONS SALADE 60G19054090TOASTED BREAD205100TRUE
4AAA2203000117020032KINGSBRAU TEKIROS BTE 50CL22030011BEER24*4*50CL2449624*4*50CLTRUE
5AAA1905313017023165CHAB PT BEURRE PEP CHOCO 200G19053100BISCUITS2410240105*6*1.5LFALSE
6AAA1905319117023168CHAB MINI GOUT.FOUR.CHOCO 168G19053100BISCUITS16914439*6*1LFALSE
7AAA2204214217023249ST.EMILION GC RG EXPERT CLUB7522041090WINES6*4*75CL64246*4*75CLTRUE
8AAA2204214217023312BLAYE CTE BORDEAUX RG EXP.CLUB22041090WINES#N/A69546*9*75CL#N/AWRONG
9AAA1904103017023388CHABRIOR CEREALES JUNGLE 400G19042000PREPARED CEREAL14101406*1*75CLFALSE
10AAA1905319917026438CHABRIOR SPECULOOS 250G19053100BISCUITS2012240TRUE
11AAA1905311917026442CHABRIOR TH CHOCO/BLANC 150G19053100BISCUITS24921612*2*1LFALSE
12AAA2204214217026941BDX SUP RGE HAUSSMANN B.EUGENE22041090WINES#N/A64246*4*75CL#N/AWRONG
13AAA017031153BOUTON D'OR NOIX DE PECAN 100G20081190PREPARED NUTS18*16*100CL1816288WRONG SHOULD BE BLANK12*5*37.5CLFALSE
14AAA1905904517031156BOUTON OR STICKS&BRETZELS 300G19059090SNACKS12253004*12*75CLFALSE
15AAA1905319917036404CHAB SABLES FOURRES CACAO 125G19053100BISCUITS20102006*39*75CLFALSE
16AAA1905319917036405CHAB SABLE FOURRE NOISETTE 12519053100BISCUITS20918012*3*75CLFALSE
17AAA1905311917047017CHAB BRIEFING CHOCO BLANC 15019053100BISCUITS12121446*43*75CLFALSE
18AAA2008119617069815BO CRUSTY NUT CCH SALEES 125G20081190PREPARED NUTS27*6*25CL276162WRONG SHOULD BE BLANK6*5*75CLFALSE
19AAA1905409017082321CHABRIOR *******S NATURE 250G19059090BISCUITS1225300TRUE
20AAA1905311917082659CHAB TURBULO CHOCO NOIR 200G19053100BISCUITS2022440TRUE
21AAA2204211717088420MUSCADET S&M LIE HT-BANCHE.75C22041090WINES6*3*75CL63186*3*75CLTRUE
22AAA1905319917088449CHAB TART.CARR.CHOCO/NOIS 127G19053100BISCUITS1813234TRUE
23AAA1905319917088457CHAB TART.CARREE FRAISE 127G19053100BISCUITS1811198TRUE
24AAA2208701017105867ON OFF ****T.COSMOPOLIT.15D 7022087019LIQUEURS6*9*75CL69546*9*75CLTRUE
25AAA017109134PAQUITO BIO SIR.GRENADINE 50CL21069060SYRUP6*3*50CL63186*3*50CLTRUE
26AAA017109139PAQUITO BIO SIROP MENTHE 50CL21069060SYRUP6*3*50CL63186*3*50CLTRUE
27AAA1905313017109867CHAB GALET.PEPI.CHOCOLAT 130G19053100BISCUITS2412288TRUE
28AAA2203000117118212ABBAY LEFFE NECTAR 6X25CL22030019BEER4*8*25CL483232*6*25CLFALSEWRONG
29AAA017118363PAQUITO SIROP CITRON.VRT 75CL21069060SYRUP6*5*75CL65306*5*75CLTRUE
30AAA1905313017142381CHAB PE**** BEURRE BIO 200G19053100BISCUITS24124TRUE
31AAA1905401017143747CHAB BISCOTTES COMPLETES 300G19053100BISCUITS189162TRUE
32AAA017144036CHABRIOR BOUDOIRS 175G19053100BISCUITS1423322TRUE
33AAA2204214217144067ST.EM RG LEO LA GAFFELIERE 7522041090WINES6*7*75CL67426*7*75CLTRUE
34AAA2204211217144130BORDEAUX BL MOUTON CADET22041090WINES#N/A67426*7*75CL#N/A
35AAA017144305CHAB.PT.PAIN GRIL.7CER.BIO225G19054090TOASTED BREAD16580TRUE
36AAA2106905917145347ON OFF ****T. S/ALC.PECHE 75CL22021091FRUIT JUICE6*13*75CL613786*13*75CLTRUE
37AAA2106905917145355ON OFF ****T.S/ALC.PLANTEUR 7522021091FRUIT JUICE6*10*75CL610606*10*75CLTRUE
38AAA017152226CHAB.PT.PAIN GRILLE.6CEREAL22519054090TOASTED BREAD1610160TRUE
39AAA2204217917169695BABV BLC PECHE EXPERT.CLUB22041090WINES#N/A63186*3*75CL#N/AWRONG
40AAA2204218017169919BABV RSE PAMPL EXP CLUB22041090WINES#N/A63186*3*75CL#N/AWRONG
41AAA1905905517171170CHABRIOR PETIT LAIT 200G19053100BISCUITS2416384TRUE
42AAA2106905917171222O.OFF CITRO.VERT/KIWI S/ALC.7522021091FRUIT JUICE6*13*75CL613786*13*75CLTRUE
43AAA2204213817171585CTE BERGERAC BL MOELL EXP CLUB22041090WINES#N/A65306*5*75CL#N/AWRONG
44AAA2208701017173935ON OFF MOJITO ****T.15D 70CL22087019LIQUEURS6*32*75CL6321926*32*75CLTRUE
45AAA2208701017181184ON OFF LIQUEUR LITCHI 15D 70CL22087019LIQUEURS6*23*70CL6231386*23*70CLTRUE
46AAA2208701017181185ON OFF LIQUE. SO COCO 18D 70CL22087019LIQUEURS6*29*70CL6291746*29*70CLTRUE
47AAA1905905517193760BO *******S GOUT EMMENTAL 105G19059090BISCUIT *******S1611176TRUE
48AAA1905904517193765BO *******S SESAME/PAVOT 100G19059090BISCUIT *******S168128TRUE
49AAA1905904517193781BO *******S GOUT PIZZA 85G19059090BISCUIT *******S169144TRUE
50AAA1905904517211057BO MINI FLUTE FEUIL.EMMENT.12519059090BAKERIES1011110TRUE
51AAA2208701017212593ON OFF SO MINT 18D 70CL22087019LIQUEURS6*13*70CL613786*13*70CLTRUE
52AAA2208701017216032ON OFF SO MANZANA 18D 70 CL22087019LIQUEURS6*27*70CL6271626*27*70CLTRUE
53AAA2008199117221760BO NOIX DE CAJOU SANS SEL 125G20081190PREPARED NUTS36*10*25CL3610360FALSE
54AAA2204214217242647AOC BDX SUPP RGE BOISE E.C22041090WINES6*7*75CL67426*7*75CLTRUE???
55AAA2202901017246609PAQUITO ****TAIL MOJITO BOC 1L22021091FRUIT JUICE6*35*1L6352106*35*1LTRUE
56AAA017270385CHAB GATEAU TOUT CHOCO 300G19059090BAKERIES1210120TRUE
57AAA2204218017277426CINS.GRENACHE RS E.CLUB 37.5CL22041090WINES12*3*37.5CL1233612*3*37.5CLTRUE
58AAA2204214817290883BABV SANGRIA ROSE SOLARIA22041090WINES#N/A63186*3*75CL#N/AWRONG
59AAA1905311917301659CHAB TWISTY CHOCOLAT LAIT 90G19059090BAKERIES18472TRUE
60AAA2201101117311278AIX LES BAINS EAU MIN 6X1L522011011MINERAL WATER1*105*1.5L1105105105*6*1L5FALSEWRONG
61AAA2201101117311280AIX LES BAINS E.MINERALE 6X1L22011011MINERAL WATER1*39*1L1393939*6*1LFALSEWRONG
62AAA017314311BROCELIANDE EAU MIN6X1L5 FARD22011011MINERAL WATER1*104*1.5L1104104104*6*1L5FALSEWRONG
63AAA2201101117314605ONDINE E.DE SOURCE 6X1L5 1/2P22011011MINERAL WATER56*1*1.5L5615656*6*1L5FALSEWRONG
64AAA2204217917330954BABV BLC MANGUE E.CLUB22041090WINES#N/A6166*1*75CL#N/AWRONG
65AAA1905319117331300CHAB ROLL'CREAMY CHOC 154G19053100BISCUITS245120TRUE
66AAA2204218017331710BABV BLC CITRON EXPERT CLUB22041090WINES#N/A64246*4*75CL#N/A
67AAA2204218017332676BABV RSE FRAMBOISE EX.CLUB22041090WINES#N/A63186*3*75CL#N/A
68AAA2208701017358032ON OFF CAIPIRINHA 15D 70 CL22087019LIQUEURS6*10*70CL610606*10*70CLTRUE
69AAA017368785CHAB.BISCUITS SESAME BIO 180G19054090TOASTED BREAD12336TRUE
70AAA1905311917368967LES CREAT. BISC MERING.CAFE10019053100BISCUITS12784TRUE
71AAA1905311917368971LES CREAT. BISC MERING.CHOC90G19053100BISCUITS12784TRUE
72AAA2008191117373192BO.MELANGE DU SOLEIL 120G20089900PREPARED FRUITS#N/A325160#N/AWRONG
73AAA1905313017373198CHAB.BISCUIT AMAN/CIT BIO 200G19053100BISCUITS12336TRUE
74AAA1905329117376829CHABRIOR SABLES CAFE 120G19053100BISCUITS14342TRUE
75AAA1905409017376867CHAB PT PAIN GRIL.NAT BIO 225G19054090TOASTED BREAD16580TRUE
76AAA2008199317376926BOUTON OR AMANDE CHILI 100G20081190MIXED NUTS PREPARATION18*11*100CL1811198FALSE
77AAA1905319917382427CHA.TARTELETTE ROND.ABRIC 150G19053100BISCUITS2413312TRUE
78AAA1901909917382442PATURAGES CREME DESS.VANIL.51021069090TOPPINGS#N/A12560#N/AWRONG
79AAA2203000117390415KINGSBRAU BIERE PREMIUM 50CL22030011BEER24*5*50CL24512024*5*50CLTRUE
80AAA1904103017391100CHABRIOR CRACK'N GOLD 375G19041000CEREALS14684TRUE
81AAA1905904517397475BO.*******S ALL.OLIV ORIGAN10019059090*******S15690TRUE
82AAA017403801PAQUITO ICED TEA PECHE 75CL22029970ICE TEA6*30*75CL6301806*30*75CLTRUE
83AAA2208701017463429ON OFF MANGUE PASS.LIQ.15D 70C22087019LIQUEURS6*12*70CL612726*12*70CLTRUE
84AAA1905319917465554CHAB TARTELET.RDES FRAISE 150G19053100BISCUITS2411264TRUE
85AAA1905319917465563CHABRIOR TH CREAMY CHOC 115G19053100BISCUITS1211132TRUE
86AAA1905319117468731CHAB GOUTER CROK VANILLE 300G19053100BISCUITS2411264TRUE
87AAA017477963ST ELOI CROUTONS NATURE 90G X219054090TOASTED BREAD813104TRUE
88AAA2106909217478101PAQUITO ZERO SUCRE CITRON 75CL21069060SYRUP6*7*75CL67426*7*75CLTRUE
89AAA1905401017478543CHAB.BISCOTTE BRIOCHEE 300G19054090BISCOT186108TRUE
90AAA1905319117478731CHAB.BISC.CREAMY CHOC 165G19053100BISCUITS16464TRUE
91AAA1904103017484745CHAB MUESLI CRISPY CHOCOLT 50019042000MUESLI CEREALS1615240TRUE
92AAA1905313017485180CHAB PT DEJ MULTI CEREAL400G19042000MUESLI CEREALS10770TRUE
93AAA2008191917501995BO.MELANGE FTS RGES/AMAND.120G20081190PREPARED NUTS#N/A226132#N/AWRONG
94AAA2204217821002885GAILLAC RSE LES 3 ORMEAUX22041090WINES#N/A610606*10*75CL#N/AWRONG
95AAA1905901040395050IDS TORTILA CHIP MEXIC400G19059090SNACKS1219228TRUE
96AAA1905901040395052IDS TORTILLA CHIP NAT 400G19059090SNACKS1225300TRUE
97AAA1905401041011030CHABRIOR BISCOTTES 36TR 300G19054090BISCOT189162TRUE
98AAA041011051CHABRIOR BISCOT 36T CEREAL300G19054090BISCOT187126TRUE
99AAA1905401041011130TOP BUDGET BISCOTTES 1KG19054090BISCOT10990TRUE
100AAA1905401041012010CHABRIOR BISCOTTES S/SEL 300G19054090BISCOT188144TRUE

<tbody>
</tbody>
SPLIT​
 
Last edited:
Upvote 0
Hi learn99,
Sorry , I was occupied yesterday, hence could not check the message.
I could see that there are only 9 values where the description is given as BEER. And in those values, I could not find a 3*25CL.
Request you to kindly re-phrase the problem encountered.
 
Upvote 0
learn99 said:
I have attached a link with full for ease of understanding

https://drive.google.com/file/d/1Mo0XPKo5g7jian4iNLbgHBe7uBtznAa0/view?usp=sharing
Click to expand...
I am not sure that it will resolve all your problems, but that file's column J formula has a fundamental problem in that it contains an incorrect row reference. That is, the first part of the J2 formula includes a reference to row 12, not row 2

=IF(LEFT(H2)="2",IF(I12="WINES",K2&"*"&L2&"*"&IF(...

After correcting that, if you still have problems, please post (directly in your post is better than a link for now) a small set of sample data with expected results. We don't need all columns, just the relevant ones which, I think, would be G:M & U:V
 
Last edited:
Upvote 0
Peter sir,
the first part of the J2 formula includes a reference to row 12, not row 2 seems to have been by mistake , as it is applicable only to first 12 rows. After that, the formula is referring to the correct cells. :)
 
Upvote 0
asakhy said:
Peter sir,
the first part of the J2 formula includes a reference to row 12, not row 2 seems to have been by mistake , as it is applicable only to first 12 rows. After that, the formula is referring to the correct cells. :)
Click to expand...
Perhaps we are looking at different files? I just double-checked the link in post #6 and did not find what you described. Even the last formula (row 272) refers to cell I282 :)

As I indicated earlier, I doubt this is the entire problem, but it would be the first thing to fix.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,841
Messages
6,127,221
Members
449,371
Latest member
strawberrish

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top