auto fill for two lists under last duplicates items based on matching column

[abdo meghari]

Hi

I search for auto-fill the same duplicates items under last row contains duplicated item into columns A,B,E,F for sheet Main based on matching column A for table1 sheet .

in column A for MAIN sheet will insert row under last duplicated item & fill based on matching on column A for table1 sheet and auto fill in column B for MAIN sheet from column C for table1 sheet

as to column E,F should auto fill based on what precede duplicated item for the last row is existed in columns E,F and so on for the others items are existed in table1 sheet.

NUMBER.xlsm
	A	B	C	D	E	F
1	CODE	BRAND			CODE	BRAND
2	BSJ100	BS 1200 R20 18PR G580			BSJ100	BS 1200R20 G580 JAP
3	BSJ100	BS 1200R20 G580 TCF			BSJ100	BS 1200R20 G580 JAP
4	BSJ100	BS 1200 R20 18PR G580 JAP			BSJ100	BS 1200R20 G580 JAP
5	BSJ100	BS 1200R20 G580 TCF JAP			BSJ100	BS 1200R20 G580 JAP
6	BSJ100	BS 1200R20 G580 TCF			BSJ100	BS 1200R20 G580 JAP
7	BSJ101	BS 1200R20 G580 THA			BSJ101	BS 1200R20 G580 THI
8	BSJ101	BS 1200 R20 18PR G580 THI			BSJ101	BS 1200R20 G580 THI
9	BSJ101	BS 1200R20 G580			BSJ101	BS 1200R20 G580 THI
10	BSJ102	BS 1200 R24 G582			BSJ102	BS 1200R24 G582 JAP
11	BSJ102	BS 1200R24 G582			BSJ102	BS 1200R24 G582 JAP
12	BSJ102	BS 1200R24 G582 JAP			BSJ102	BS 1200R24 G582 JAP
13	BSJ102	BS 1200 R24 G582 JAP			BSJ102	BS 1200R24 G582 JAP
14	BSJ103	BS 1200R20-18PR R187 JAP			BSJ103	BS 1200R20 R187 JAP
15	BSJ103	BS 1200R20 R187 TCF			BSJ103	BS 1200R20 R187 JAP
16	BSJ104	BS 1200R20-18PR R187 THI			BSJ104	BS 1200R20 R187 THI
17	BSJ105	BS 1200R24 G580			BSJ105	BS 1200R24 G580 JAP
18	BSJ105	BS 1200R24 G580 JAP			BSJ105	BS 1200R24 G580 JAP
19	BSJ106	BS 13 R22.5 R187 JAP			BSJ106	BS 13R22.5 R187 JAP
20	BSJ107	BS 1400R20 TCF R180 JAP			BSJ107	BS 1400R20 R180 JAP
21	BSJ107	BS 1400R20 TCF R180			BSJ107	BS 1400R20 R180 JAP
22	BSJ108	BS 1400R20 TCF R180BZ JAP			BSJ108	BS 1400R20 R180BZ JAP
23	BSJ109	BS 1400R20VSJ TCF JAP			BSJ109	BS 1400R20 VSJ JAP
24	BSJ109	BS 1400R20 VSJ TCF JAP			BSJ109	BS 1400R20 VSJ JAP
25	BSJ109	BS 1400R20 VSJ TCF			BSJ109	BS 1400R20 VSJ JAP
26	BSJ110	BS 155 R12C R624 IND			BSJ110	BS 155R12C R624 INDO
27	BSJ111	BS 155R12C R623 IND			BSJ111	BS 155R12C R623 INDO
Main

NUMBER.xlsm
	A	B	C	D	E
1	S.N	ITEM	item	pur	sel
2	BSJ101	1	BS 1200 R20 18PR G580 THI	6.00
3	BSJ100	2	BS 1200 R20 18PR G580 JAP	1.00	818.00
4	BSJ103	3	BS 1200R20-18PR R187 JAP	8.00	6.00
5	BSJ105	4	BS 1200R24 G580 JAP		4.00
6	BSJ102	5	BS 1200R24 G582 JAP		2.00
7	BSJ106	6	BS 13 R22.5 R187 JAP		3.00
8	BSJ107	7	BS 1400R20 TCF R180 JAP		12.00
9	BSJ109	8	BS 1400R20VSJ TCF JAP		22.00
10	BSJ132	9	BS 195/60 R15 EP150 THI		14.00
table1

Cell Formulas
Range	Formula
E3	E3	=490+278+50

I put some result how should be as highlighted how add them

NUMBER.xlsm
	A	B	C	D	E	F
1	CODE	BRAND			CODE	BRAND
2	BSJ100	BS 1200 R20 18PR G580			BSJ100	BS 1200R20 G580 JAP
3	BSJ100	BS 1200R20 G580 TCF			BSJ100	BS 1200R20 G580 JAP
4	BSJ100	BS 1200 R20 18PR G580 JAP			BSJ100	BS 1200R20 G580 JAP
5	BSJ100	BS 1200R20 G580 TCF JAP			BSJ100	BS 1200R20 G580 JAP
6	BSJ100	BS 1200R20 G580 TCF			BSJ100	BS 1200R20 G580 JAP
7	BSJ100	BS 1200 R20 18PR G580 JAP			BSJ100	BS 1200R20 G580 JAP
8	BSJ101	BS 1200R20 G580 THA			BSJ101	BS 1200R20 G580 THI
9	BSJ101	BS 1200 R20 18PR G580 THI			BSJ101	BS 1200R20 G580 THI
10	BSJ101	BS 1200R20 G580			BSJ101	BS 1200R20 G580 THI
11	BSJ101	BS 1200 R20 18PR G580 THI			BSJ101	BS 1200R20 G580 THI
12	BSJ102	BS 1200 R24 G582			BSJ102	BS 1200R24 G582 JAP
13	BSJ102	BS 1200R24 G582			BSJ102	BS 1200R24 G582 JAP
14	BSJ102	BS 1200R24 G582 JAP			BSJ102	BS 1200R24 G582 JAP
15	BSJ102	BS 1200 R24 G582 JAP			BSJ102	BS 1200R24 G582 JAP
16	BSJ102	BS 1200R24 G582 JAP			BSJ102	BS 1200R24 G582 JAP
17	BSJ103	BS 1200R20-18PR R187 JAP			BSJ103	BS 1200R20 R187 JAP
18	BSJ103	BS 1200R20 R187 TCF			BSJ103	BS 1200R20 R187 JAP
19	BSJ103	BS 1200R20-18PR R187 JAP			BSJ103	BS 1200R20 R187 JAP
20	BSJ104	BS 1200R20-18PR R187 THI			BSJ104	BS 1200R20 R187 THI
21	BSJ105	BS 1200R24 G580			BSJ105	BS 1200R24 G580 JAP
22	BSJ105	BS 1200R24 G580 JAP			BSJ105	BS 1200R24 G580 JAP
23	BSJ105	BS 1200R24 G580 JAP			BSJ105	BS 1200R24 G580 JAP
24	BSJ106	BS 13 R22.5 R187 JAP			BSJ106	BS 13R22.5 R187 JAP
25	BSJ107	BS 1400R20 TCF R180 JAP			BSJ107	BS 1400R20 R180 JAP
26	BSJ107	BS 1400R20 TCF R180			BSJ107	BS 1400R20 R180 JAP
27	BSJ108	BS 1400R20 TCF R180BZ JAP			BSJ108	BS 1400R20 R180BZ JAP
Main

 

[Legacy 143009]

How about this?

Sub test()
  Dim MAIN As Worksheet, table1 As Worksheet
  Dim table1Range As Range, MAINRange1 As Range, MAINRange2 As Range
 
  Set MAIN = Worksheets("MAIN")
  Set MAINRange1 = Intersect(MAIN.UsedRange, MAIN.Range("A:B"))
  Set MAINRange2 = Intersect(MAIN.UsedRange, MAIN.Range("E:F"))
 
  Set table1 = Worksheets("table1")
  Set table1Range = Intersect(table1.UsedRange, table1.Range("A:C"))

  With MAINRange1
  For i = .Rows.Count To 2 Step -1
    If .Cells(i, 1).Offset(1).Value <> .Cells(i, 1).Value Then
      .Cells(i, 1).Offset(1).Resize(, 2).Insert Shift:=xlDown
      .Cells(i, 1).Offset(1).Value = .Cells(i, 1).Value
      .Cells(i, 2).Offset(1).Value = table1Range.Cells(Application.Match(.Cells(i, 1).Value, table1Range.Columns(1), 0), 3)
    End If
  Next
  End With
  With MAINRange2
  For i = .Rows.Count To 2 Step -1
    If .Cells(i, 1).Offset(1).Value <> .Cells(i, 1).Value Then
      .Cells(i, 1).Offset(1).Resize(, 2).Insert Shift:=xlDown
      .Cells(i, 1).Offset(1).Value = .Cells(i, 1).Value
      .Cells(i, 2).Offset(1).Value = table1Range.Cells(Application.Match(.Cells(i, 1).Value, table1Range.Columns(1), 0), 3)
    End If
  Next
  End With
End Sub

 

[abdo meghari]

thanks , it gives mismatch error in this line

.Cells(i, 2).Offset(1).Value = table1Range.Cells(Application.Match(.Cells(i, 1).Value, table1Range.Columns(1), 0), 3)

 

[Legacy 143009]

The code will throw mismatch error when MAIN column A value is not matched in table1 column A.
In other words, table1 must contain every code and brand that exists in MAIN sheet.

 

[abdo meghari]

just question , did you test it? in reality I copy the same data as in OP in a new file still the error shows .

 

[Legacy 143009]

Yes,

Please find the working test file. I've changed table1 A9 to BSJ04 because the code will give error on MAIN A20 as I told you before.

https://file.io/9Jc8hHXJeBVI

 

[abdo meghari]

there is no file as your link !
so you wanna say if the code in column A for table1 sheet is new and is not existed in column A for MAIN sheet , then will show error?

 

[abdo meghari]

can you see my file,please?autofill.xlsm

 

[Legacy 143009]

I think, I mean vice versa. You say, you want to double the every last code number in MAIN column A. It goes to table1 and looks for that code number. It has to be there, otherwise, how can it pull the brand name from table1 column C?

Kitap1.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[abdo meghari]

ok I expected when fill for column E: F will not clear.
so just compare row 7 for columns B with F is not the same brand . as I said

as to column E,F should auto fill based on what precede duplicated item for the last row is existed in columns E,F

but your code will fill in column F based on TABLE1 sheet . should not do that.
the right way should fill based on precede cell (F6)
and why your file work and mine is not ?!