Add elements for adjacent cells by insert column based on multiple characters

[abdo meghari]

Hello
I'm not sure if could be by macro , but what I want if the item in column B contains R & number like this 700R16 or 195R15C or 29.5R25** VSDL as bold after R letter then should add element (TIRES) in adjacent cell for column C and if the item in column B contains / then the element in column C should be TIRES and if the item in column B contains × & - then the element in column C should be TIRES like this 28×9-15 LO as bold, if the item in column B contains number & letter A then the element in column C should be BATTERY like this 70A R OR 74A L or 100A as bold, if if the item in column B contains number & × & number & L then the element in column C should be OIL like this PP MEG 24KG 4×5L or CYU MEG 4×4L as bold .

[

CC1.xlsm
	A	B	C	D	E
1	ITEM	BATCH	QTY	PRICE	TOTAL
2	1	235/60R18	4.00	LYD 425.00	LYD 1,700.00
3	2	750R16 L50	2.00	LYD 400.00	LYD 800.00
4	3	700R16	7.00	LYD 800.00	LYD 5,600.00
5	4	215/65R16	4.00	LYD 355.00	LYD 1,420.00
6	5	265/70R16	5.00	LYD 550.00	LYD 2,750.00
7	6	29.5R25** VSDL	24.00	LYD 385.00	LYD 9,240.00
8	7	265/60R18	4.00	LYD 540.00	LYD 2,160.00
9	8	225/75R15	1.00	LYD 340.00	LYD 340.00
10	9	275/50R21	4.00	LYD 780.00	LYD 3,120.00
11	10	265/65R17	5.00	LYD 700.00	LYD 3,500.00
12	11	195R15C	5.00	LYD 600.00	LYD 3,000.00
13	12	18.4/15-30-20	4.00	LYD 310.00	LYD 1,240.00
14	13	28×9-15 LO	2.00	LYD 580.00	LYD 1,160.00
15	14	70A R	10.00	LYD 405.00	LYD 4,050.00
16	15	60A	10.00	LYD 335.00	LYD 3,350.00
17	16	90A	6.00	LYD 470.00	LYD 2,820.00
18	17	74A L	4.00	LYD 310.00	LYD 1,240.00
19	18	100A	3.00	LYD 490.00	LYD 1,470.00
20	19	150A	4.00	LYD 650.00	LYD 2,600.00
21	20	120A	2.00	LYD 565.00	LYD 1,130.00
22	21	23.5R25 18.4-30	1.00	LYD 385.00	LYD 385.00
23	22	PP MEG 24KG 4×5L	21.00	LYD 40.00	LYD 840.00
24	23	CYU MEG 4×4L	24.00	LYD 50.00	LYD 1,200.00
25	TOTAL				LYD 55,115.00
combined

Cell Formulas
Range	Formula
E2:E24	E2	=C2*D2
E25	E25	=SUM(E2:E24)

result

CC1.xlsm
	A	B	C	D	E	F
1	ITEM	BATCH	GROUP	QTY	PRICE	TOTAL
2	1	235/60R18	TIRES	4.00	LYD 425.00	LYD 1,700.00
3	2	750R16 L50	TIRES	2.00	LYD 400.00	LYD 800.00
4	3	700R16	TIRES	7.00	LYD 800.00	LYD 5,600.00
5	4	215/65R16	TIRES	4.00	LYD 355.00	LYD 1,420.00
6	5	265/70R16	TIRES	5.00	LYD 550.00	LYD 2,750.00
7	6	29.5R25** VSDL	TIRES	24.00	LYD 385.00	LYD 9,240.00
8	7	265/60R18	TIRES	4.00	LYD 540.00	LYD 2,160.00
9	8	225/75R15	TIRES	1.00	LYD 340.00	LYD 340.00
10	9	275/50R21	TIRES	4.00	LYD 780.00	LYD 3,120.00
11	10	265/65R17	TIRES	5.00	LYD 700.00	LYD 3,500.00
12	11	195R15C	TIRES	5.00	LYD 600.00	LYD 3,000.00
13	12	18.4/15-30-20	TIRES	4.00	LYD 310.00	LYD 1,240.00
14	13	28×9-15 LO	TIRES	2.00	LYD 580.00	LYD 1,160.00
15	14	70A R	BATTERY	10.00	LYD 405.00	LYD 4,050.00
16	15	60A	BATTERY	10.00	LYD 335.00	LYD 3,350.00
17	16	90A	BATTERY	6.00	LYD 470.00	LYD 2,820.00
18	17	74A L	BATTERY	4.00	LYD 310.00	LYD 1,240.00
19	18	100A	BATTERY	3.00	LYD 490.00	LYD 1,470.00
20	19	150A	BATTERY	4.00	LYD 650.00	LYD 2,600.00
21	20	120A	BATTERY	2.00	LYD 565.00	LYD 1,130.00
22	21	23.5R25 18.4-30	TIRES	1.00	LYD 385.00	LYD 385.00
23	22	PP MEG 24KG 4×5L	OIL	21.00	LYD 40.00	LYD 840.00
24	23	CYU MEG 4×4L	OIL	24.00	LYD 50.00	LYD 1,200.00
25	TOTAL					LYD 55,115.00
EXPECTED

Cell Formulas
Range	Formula
F2:F24	F2	=D2*E2
F25	F25	=SUM(F2:F24)

should be clear data in second sheet from row 2 before bring data . if it's impossible by macro or any suggestion to make easy things I accept that or formula
thanks

 

[SunnyAlv]

22​	PP MEG 24KG 4×5L​	OIL​	21.00​	LYD 40.00​	LYD 840.00​
24	23​	CYU MEG 4×4L​	OIL​	24.00​	LYD 50.00​	LYD 1,200.00​

For the 2 items above, are there special requirements for OIL?

 

[abdo meghari]

For the 2 items above, are there special requirements for OIL?

I don't understand it !

 

[SunnyAlv]

I don't understand it !

Ah sorry, I've just finished the formula well and I just need to make the VBA, so I'll send it to you tomorrow

 

[abdo meghari]

thank you I will wait for you until tomorrow.
take your time .

 

[SunnyAlv]

terima kasih aku akan menunggumu sampai besok.
tidak usah buru-buru .

Sorry for waiting so long @abdo meghari

i have 2 option u can use with formula or VBA

a. Formula
u can manually adding one column and Copy this Formula on Column C (See The Picture Below)

This the Formula

=LET(

OIL,
IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("×",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("×",B2)-1,1))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("×",B2)+1,1))),IFERROR(IF(MID(B2,SEARCH("×",B2)+2,1)="L",TRUE,FALSE),FALSE))=TRUE,TRUE,FALSE),


BATT,
IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("A",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("A",B2)-1,1))))=TRUE,TRUE,FALSE),


WHILE,
IF(OR(IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("R",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("R",B2)+1,1))))=TRUE,TRUE,FALSE),IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("×",B2))),ISNUMBER(NUMBERVALUE(SEARCH("-",B2))))=TRUE,TRUE,FALSE),ISNUMBER(NUMBERVALUE(SEARCH("/",B2))))=TRUE,TRUE,FALSE),

IF(OIL=TRUE,"OIL",IF(BATT=TRUE,"Battry",IF(WHILE=TRUE,"While",""))))

b. VBA

Sub IFANDOR()
Application.ScreenUpdating = False


    ActiveSheet.Select
    Columns("C:C").Insert Shift:=xlToRight  ' U can Change Column
    
    Range("C1").Formula = "GROUP"  'Name of Header Column C
    
    'Formula
    Range("C2").FormulaR1C1 = _
        "=LET(" & Chr(10) & "" & Chr(10) & "OIL," & Chr(10) & "IF(AND(ISNUMBER(NUMBERVALUE(SEARCH(""×"",RC[-1]))),ISNUMBER(NUMBERVALUE(MID(RC[-1],SEARCH(""×"",RC[-1])-1,1))),ISNUMBER(NUMBERVALUE(MID(RC[-1],SEARCH(""×"",RC[-1])+1,1))),IFERROR(IF(MID(RC[-1],SEARCH(""×"",RC[-1])+2,1)=""L"",TRUE,FALSE),FALSE))=TRUE,TRUE,FALSE)," & Chr(10) & "" & Chr(10) & "" & Chr(10) & "BATT," & Chr(10) & "IF(AND(ISNUMBER(NUMBERVALUE(SEARCH(""A"",RC[-1]))),ISNUMBER(NUMBERVALUE(MID(RC[-1],SE" & _
        "ARCH(""A"",RC[-1])-1,1))))=TRUE,TRUE,FALSE)," & Chr(10) & "" & Chr(10) & "" & Chr(10) & "WHILE," & Chr(10) & "IF(OR(IF(AND(ISNUMBER(NUMBERVALUE(SEARCH(""R"",RC[-1]))),ISNUMBER(NUMBERVALUE(MID(RC[-1],SEARCH(""R"",RC[-1])+1,1))))=TRUE,TRUE,FALSE),IF(AND(ISNUMBER(NUMBERVALUE(SEARCH(""×"",RC[-1]))),ISNUMBER(NUMBERVALUE(SEARCH(""-"",RC[-1]))))=TRUE,TRUE,FALSE),ISNUMBER(NUMBERVALUE(SEARCH(""/"",RC[-1]))))=TRUE,TRUE,FALSE)," & Chr(10) & "" & Chr(10) & "IF" & _
        "(OIL=TRUE,""OIL"",IF(BATT=TRUE,""Battry"",IF(WHILE=TRUE,""While"",""""))))" & _
        ""
    
    'Auto Copy to Last Row
    Range("C2").AutoFill Destination:=Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
    
    
    Application.ScreenUpdating = True
    MsgBox "Done"
    
    
End Sub

*I hope this helps your problem, If there’s any unclear information please let me know

 

[SunnyAlv]

×

BTW, the use of "x" here is different from the "x" in the alphabet, so make sure you use the letter "x" correctly.

 

[abdo meghari]

thanks
just question , your solution deals with Office Version 2010 because I work for another laptop contains 2010 version ?
because shows error

CC1.xlsm
	A	B	C	D	E	F
1	ITEM	BATCH	GROUP	QTY	PRICE	TOTAL
2	1	235/60R18	#NAME?	4.00	LYD 425.00	LYD 1,700.00
3	2	750R16 L50	#NAME?	2.00	LYD 400.00	LYD 800.00
4	3	700R16	#NAME?	7.00	LYD 800.00	LYD 5,600.00
5	4	215/65R16	#NAME?	4.00	LYD 355.00	LYD 1,420.00
6	5	265/70R16	#NAME?	5.00	LYD 550.00	LYD 2,750.00
7	6	29.5R25** VSDL	#NAME?	24.00	LYD 385.00	LYD 9,240.00
8	7	265/60R18	#NAME?	4.00	LYD 540.00	LYD 2,160.00
9	8	225/75R15	#NAME?	1.00	LYD 340.00	LYD 340.00
10	9	275/50R21	#NAME?	4.00	LYD 780.00	LYD 3,120.00
11	10	265/65R17	#NAME?	5.00	LYD 700.00	LYD 3,500.00
12	11	195R15C	#NAME?	5.00	LYD 600.00	LYD 3,000.00
13	12	18.4/15-30-20	#NAME?	4.00	LYD 310.00	LYD 1,240.00
14	13	28×9-15 LO	#NAME?	2.00	LYD 580.00	LYD 1,160.00
15	14	70A R	#NAME?	10.00	LYD 405.00	LYD 4,050.00
16	15	60A	#NAME?	10.00	LYD 335.00	LYD 3,350.00
17	16	90A	#NAME?	6.00	LYD 470.00	LYD 2,820.00
18	17	74A L	#NAME?	4.00	LYD 310.00	LYD 1,240.00
19	18	100A	#NAME?	3.00	LYD 490.00	LYD 1,470.00
20	19	150A	#NAME?	4.00	LYD 650.00	LYD 2,600.00
21	20	120A	#NAME?	2.00	LYD 565.00	LYD 1,130.00
22	21	23.5R25 18.4-30	#NAME?	1.00	LYD 385.00	LYD 385.00
23	22	PP MEG 24KG 4×5L	#NAME?	21.00	LYD 40.00	LYD 840.00
24	23	CYU MEG 4×4L	#NAME?	24.00	LYD 50.00	LYD 1,200.00
25	TOTAL					LYD 55,115.00
combined

Cell Formulas
Range	Formula
C2:C24	C2	=LET( OIL, IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("×",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("×",B2)-1,1))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("×",B2)+1,1))),IFERROR(IF(MID(B2,SEARCH("×",B2)+2,1)="L",TRUE,FALSE),FALSE))=TRUE,TRUE,FALSE), BATT, IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("A",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("A",B2)-1,1))))=TRUE,TRUE,FALSE), WHILE, IF(OR(IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("R",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("R",B2)+1,1))))=TRUE,TRUE,FALSE),IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("×",B2))),ISNUMBER(NUMBERVALUE(SEARCH("-",B2))))=TRUE,TRUE,FALSE),ISNUMBER(NUMBERVALUE(SEARCH("/",B2))))=TRUE,TRUE,FALSE), IF(OIL=TRUE,"OIL",IF(BATT=TRUE,"Battry",IF(WHILE=TRUE,"While",""))))
F2:F24	F2	=D2*E2
F25	F25	=SUM(F2:F24)

if it's not then I should test it for version 2019 at work but not today .

 

[SunnyAlv]

thanks
just question , your solution deals with Office Version 2010 because I work for another laptop contains 2010 version ?
because shows error

CC1.xlsm
	A	B	C	D	E	F
1	ITEM	BATCH	GROUP	QTY	PRICE	TOTAL
2	1	235/60R18	#NAME?	4.00	LYD 425.00	LYD 1,700.00
3	2	750R16 L50	#NAME?	2.00	LYD 400.00	LYD 800.00
4	3	700R16	#NAME?	7.00	LYD 800.00	LYD 5,600.00
5	4	215/65R16	#NAME?	4.00	LYD 355.00	LYD 1,420.00
6	5	265/70R16	#NAME?	5.00	LYD 550.00	LYD 2,750.00
7	6	29.5R25** VSDL	#NAME?	24.00	LYD 385.00	LYD 9,240.00
8	7	265/60R18	#NAME?	4.00	LYD 540.00	LYD 2,160.00
9	8	225/75R15	#NAME?	1.00	LYD 340.00	LYD 340.00
10	9	275/50R21	#NAME?	4.00	LYD 780.00	LYD 3,120.00
11	10	265/65R17	#NAME?	5.00	LYD 700.00	LYD 3,500.00
12	11	195R15C	#NAME?	5.00	LYD 600.00	LYD 3,000.00
13	12	18.4/15-30-20	#NAME?	4.00	LYD 310.00	LYD 1,240.00
14	13	28×9-15 LO	#NAME?	2.00	LYD 580.00	LYD 1,160.00
15	14	70A R	#NAME?	10.00	LYD 405.00	LYD 4,050.00
16	15	60A	#NAME?	10.00	LYD 335.00	LYD 3,350.00
17	16	90A	#NAME?	6.00	LYD 470.00	LYD 2,820.00
18	17	74A L	#NAME?	4.00	LYD 310.00	LYD 1,240.00
19	18	100A	#NAME?	3.00	LYD 490.00	LYD 1,470.00
20	19	150A	#NAME?	4.00	LYD 650.00	LYD 2,600.00
21	20	120A	#NAME?	2.00	LYD 565.00	LYD 1,130.00
22	21	23.5R25 18.4-30	#NAME?	1.00	LYD 385.00	LYD 385.00
23	22	PP MEG 24KG 4×5L	#NAME?	21.00	LYD 40.00	LYD 840.00
24	23	CYU MEG 4×4L	#NAME?	24.00	LYD 50.00	LYD 1,200.00
25	TOTAL					LYD 55,115.00
combined

Cell Formulas
Range	Formula
C2:C24	C2	=LET( OIL, IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("×",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("×",B2)-1,1))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("×",B2)+1,1))),IFERROR(IF(MID(B2,SEARCH("×",B2)+2,1)="L",TRUE,FALSE),FALSE))=TRUE,TRUE,FALSE), BATT, IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("A",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("A",B2)-1,1))))=TRUE,TRUE,FALSE), WHILE, IF(OR(IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("R",B2))),ISNUMBER(NUMBERVALUE(MID(B2,SEARCH("R",B2)+1,1))))=TRUE,TRUE,FALSE),IF(AND(ISNUMBER(NUMBERVALUE(SEARCH("×",B2))),ISNUMBER(NUMBERVALUE(SEARCH("-",B2))))=TRUE,TRUE,FALSE),ISNUMBER(NUMBERVALUE(SEARCH("/",B2))))=TRUE,TRUE,FALSE), IF(OIL=TRUE,"OIL",IF(BATT=TRUE,"Battry",IF(WHILE=TRUE,"While",""))))
F2:F24	F2	=D2*E2
F25	F25	=SUM(F2:F24)

if it's not then I should test it for version 2019 at work but not today .

my bad, ok try with Excel Ver.2019, and if still eror i will convert the formula to manually

 

[Fluff]

That formula will still error in 2019 as only 2012 & 365 have the LET function