delete and put the matched item into adjacent cell

[abdo meghari]

hello
I want matching the items in column B with the items in column H if the items are matched with column B then should put each matched item next to adjacent matched item in column A and delete the others not matched in the same column H .

DL .xlsm
	A	B	C	D	E	F	G	H
1	Sr	Description	QTY					LIST
2	1	BS 185/70R13 EP150 INDO	184					BS 195R14C R623 THI
3	2	BS 185R14C R624 INDO	16					BS 195R14C 613V JAP
4	3	BS 175/65R14 B250 THI	50					BS 195R14C R660 TR
5	4	BS 175/65R14 B250 INDO	30					BS 195/70R14 150 EZ THI
6	5	BS 175/70R14 EP150 THI	25					BS 205R14C 613V JAP
7	6	BS 165 R13C R624 INDO	50					BS 205R14C R624 INDO
8	8	BS 165 R13C R624 INDO	46					BS 215R14C R624 INDO
9	9	BS 175/70R13 B250 INDO	1					BS 185/65R15 B250 JAP
10								BS 185/65R15 T005 INDO
11								BS 185/65R15 T01 JAP
12								BS 175/65R14 B250 INDO
13								BS 175/70R14 EP150 THI
14								BS 165 R13C R624 INDO
15								BS 185R14C R624 INDO
16								BS 175/65R14 B250 THI
17								BS 165 R13C R624 INDO
18								BS 175/70R13 B250 INDO
19								BS 185/70R13 EP150 INDO
20								BS 185/70R13 EP150 INDO
purchase

result in column H

DL .xlsm
	A	B	C	D	E	F	G	H
1	Sr	Description	QTY				ITEM	LIST
2	1	BS 185/70R13 EP150 INDO	184				1	BS 185/70R13 EP150 INDO
3	2	BS 185R14C R624 INDO	16				2	BS 185R14C R624 INDO
4	3	BS 175/65R14 B250 THI	50				3	BS 175/65R14 B250 THI
5	4	BS 175/65R14 B250 INDO	30				4	BS 175/65R14 B250 INDO
6	5	BS 175/70R14 EP150 THI	25				5	BS 175/70R14 EP150 THI
7	6	BS 165 R13C R624 INDO	50				6	BS 165 R13C R624 INDO
8	7	BS 175/70R13 B250 INDO	8				7	BS 185/70R13 EP150 INDO
9	8	BS 165 R13C R624 INDO	46				8	BS 165 R13C R624 INDO
10	9	BS 175/70R13 B250 INDO	1				9	BS 175/70R13 B250 INDO
11
purchase

my list about 1000 items .

 

[abdo meghari]

again my apologies !!
I was hurry up

• Why is cell A4 = 2 when in the original data cell A4 = 3

yes you're right should be 3 not to

• Why is cell G4 = 3 when the original data has 2 for BS 185R14C R624 INDO?

because in post #9 add new items if you put in 2 then will not match for adjacent cell in B2 , in this case should leave the cell and keep empty as in picture

• In cell H9, where did the BS 165 R13C R624 THI come from? I can't see that value anywhere in H2:H20 of the original data.

what do you mean ? based on post#9 is existed in H19 as in pic 1

 

[Peter_SSs]

what do you mean ? based on post#9 is existed in H19 as in pic 1

Actually, I think the value that I asked about was in H14 in pic 1. However, I had actually asked the wrong question. I meant to say "I can't see that value anywhere in B2:B9 of the original data."
H9 in your results is the value that I asked about BS 165 R13C R624 THI
and that value does not appear in B2:B9 of the original data (pic 1). I do note though that B9 in pic1 of post 9 was BS 165R13C R624 THI but B9 in pic 2 has changed to BS 165 R13C R624 THI so I assume the pic 1 value was another error?

If so, try this

Sub MatchItems()
  Dim a As Variant, b As Variant
  Dim rFound As Range
  Dim i As Long
 
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 2)
  For i = 1 To UBound(a)
    Set rFound = Columns("H").Find(What:=a(i, 2), LookAt:=xlWhole)
    If Not rFound Is Nothing Then
      b(i, 1) = a(i, 1)
      b(i, 2) = a(i, 2)
    End If
  Next i
  With Range("G2", Range("H" & Rows.Count).End(xlUp))
    .ClearContents
    .Resize(UBound(b)).Value = b
  End With
End Sub

 

[abdo meghari]

so I assume the pic 1 value was another error?

It's too bad explanation
I was hurry up . it's not excuses . this is what happened . my apologies for bad explenation
although of all of things happend , you have patience. if another member I expect leaving this thread. I don't exaggerate , but you keep going until solve it .

it's great !!
many thanks for your time & help

 

[Peter_SSs]

You're welcome.

It is important that you give accurate sample data and expected results though as that is all helpers have to go on. They are not familiar with your data and expectations like you are.
As you have seen here, being in a hurry & making mistakes actually slows the process down.