delete and put the matched item into adjacent cell

abdo meghari · Jul 4, 2022

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

BS 165 R13C R624 INDO

46

BS 215R14C R624 INDO

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 .

Peter_SSs · Jul 4, 2022

In your sample it seems that everything in column B appears in column H which means that the column H result is simply a copy of column B.
So, is it possible that an item in column B does not appear in column H?

abdo meghari · Jul 4, 2022

So, is it possible that an item in column B does not appear in column H?

if you see the picture 2 you will see some items are deleted( should delete item from column H if the items in column B does not appear in column H just keep the matched items in column H based on column B .

Peter_SSs · Jul 4, 2022

That did not answer my question:

Peter_SSs said:
is it possible that an item in column B does not appear in column H?

abdo meghari said:
if you see the picture 2 ..

In picture 2 I can see the value in cell H8 and cannot see why it is there.

abdo meghari · Jul 4, 2022

sorry about mistake !! also shouldn't be duplicated items in the same column
I hope this fixing the things

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

BS 165R13C R624 THI

46

BS 215R14C R624 INDO

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 THI

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 THI

purchase

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

BS 165 R13C R624 THI

46

8

BS 165 R13C R624 THI

9

BS 175/70R13 B250 INDO

1

9

BS 175/70R13 B250 INDO

purchase

Peter_SSs · Jul 4, 2022

Despite asking twice, you still have not answered my question (

) so I expect this is not what is actually required. However, it does produce your expected result from your sample data.

VBA Code:

Sub IExpectThisIsNotWhatIsRequired()
  Range("A2:B" & Range("H" & Rows.Count).End(xlUp).Row).Copy Destination:=Range("G2")
End Sub

abdo meghari · Jul 4, 2022

Despite asking twice, you still have not answered my question

sorry ! I try to answer you but seem I misunderstand you . now I knew it why you ask this after test the code
based on your question yes. and based on your code add new item to column H shouldn't add it .

Peter_SSs · Jul 4, 2022

abdo meghari said:
based on your question yes.

Thanks you.
So, could we have some small sample data and expected results where 1 or 2 items from column B do not appear in column H?

abdo meghari · Jul 4, 2022

see the items in rows 2,11 in column B

DL (version 1).xlsb

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 1200R20 G580 JAP

200

BS 195R14C 613V JAP

4

3

BS 185R14C R624 INDO

16

BS 195R14C R660 TR

5

4

BS 175/65R14 B250 THI

50

BS 195/70R14 150 EZ THI

6

5

BS 175/65R14 B250 INDO

30

BS 205R14C 613V JAP

7

6

BS 175/70R14 EP150 THI

25

BS 205R14C R624 INDO

8

7

BS 165 R13C R624 INDO

50

BS 215R14C R624 INDO

9

8

BS 165R13C R624 THI

46

BS 185/65R15 B250 JAP

10

9

BS 175/70R13 B250 INDO

1

BS 185/65R15 T005 INDO

11

10

BS 1400R20 VSJ TCF JAP

20

BS 185/65R15 T01 JAP

12

BS 175/65R14 B250 INDO

13

BS 175/70R14 EP150 THI

14

BS 165 R13C R624 THI

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 THI

purchase

DL (version 1).xlsb

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 1200R20 G580 JAP

200

4

2

BS 185R14C R624 INDO

16

2

BS 185R14C R624 INDO

5

3

BS 175/65R14 B250 THI

50

3

BS 175/65R14 B250 THI

6

4

BS 175/65R14 B250 INDO

30

4

BS 175/65R14 B250 INDO

7

5

BS 175/70R14 EP150 THI

25

5

BS 175/70R14 EP150 THI

8

6

BS 165 R13C R624 INDO

50

6

BS 165 R13C R624 INDO

9

8

BS 165 R13C R624 THI

46

8

BS 165 R13C R624 THI

10

9

BS 175/70R13 B250 INDO

1

9

BS 175/70R13 B250 INDO

11

10

BS 1400R20 VSJ TCF JAP

20

purchase

Peter_SSs · Jul 4, 2022

In the results mini-sheet ..

Why is cell A4 = 2 when in the original data cell A4 = 3?
Why is cell G4 = 3 when the original data has 2 for BS 185R14C R624 INDO?
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.

delete and put the matched item into adjacent cell

abdo meghari

Active Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

abdo meghari

Active Member

Peter_SSs

MrExcel MVP, Moderator

abdo meghari

Active Member

Peter_SSs

MrExcel MVP, Moderator

abdo meghari

Active Member

Peter_SSs

MrExcel MVP, Moderator

abdo meghari

Active Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

delete and put the matched item into adjacent cell

Active Member

Excel Facts

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock