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]

yo mean it goes from table1 sheet to MAIN sheet .

No, let me put in order.
First it goes MAIN A7. It sees that BSJ100 is the last number. It lookup the number in table1 column A. Finds at table1 A3. Copies the information from table1 C3 to MAIN B7.
It goes the same for other numbers until BSJ104 at MAIN A20. It looks at table1 column A. Can not find it. It throws the error.

Oh, wait a minute, would you like to skip it if it cannot finds?

 

[abdo meghari]

First it goes MAIN A7. It sees that BSJ100 is the last number. It lookup the number in table1 column A. Finds at table1 A3. Copies the information from table1 C3 to MAIN B7.

that's correct .

It goes the same for other numbers until BSJ104 at MAIN A20. It looks at table1 column A. Can not find it. It trows the error.

should not interest what is existed in MAIN sheet and is not existed in TABLE1 sheet .
the most important what's is existed for two sheets .

 

[Legacy 143009]

Then this should work:

Sub test()
  Dim MAIN As Worksheet, table1 As Worksheet
  Dim table1Range As Range, MAINRange1 As Range, MAINRange2 As Range, r 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
      Set r = table1Range.Columns(1).Find(what:=.Cells(i, 1).Value, LookIn:=xlFormulas, lookat:=xlWhole)
      If Not r Is Nothing 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
    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
      Set r = table1Range.Columns(1).Find(what:=.Cells(i, 1).Value, LookIn:=xlFormulas, lookat:=xlWhole)
      If Not r Is Nothing Then
        .Cells(i, 1).Offset(1).Resize(, 2).Insert Shift:=xlDown
        .Cells(i, 1).Offset(1).Resize(, 2).Value = .Cells(i, 1).Resize(, 2).Value
      End If
    End If
  Next
  On Error GoTo 0
  End With
End Sub

 

[abdo meghari]

awesome !
thanks very much for your time & help

 

[Legacy 143009]

Glad it did work! You are welcome