Add item for first location with space based on matching helper column

[abdo meghari]

Hello,
I would match column B with column H then should add item from column G to the first location with make space in column C for each cell
before

MR.xlsm
	B	C	D	E	F	G	H
1	MARK	BRAND	QTY			MARK SHORT	MARK
2	BRIDGESTONE	1200R20 G580 JAP	200			BS	BRIDGESTONE
3	FIRESTONE	1200R20 G580 JAP	120			FSR	FIRESTONE
4	DAYTON	1200R20 G580 JAP	300			DAT	DAYTON
5	BRIDGESTONE	1200R20 G580 THI	230			GRD	GOODRIDE
6	FIRESTONE	1200R20 FSR4000 THI	120			KOHM	KOUHMO
7	DAYTON	1200R20 D50A THI	110
8	GOODRIDE	1200R20 GR423	13
9	KOUHMO	1200R20 KT12	14
10	GOODRIDE	1400R20 VSJ	15
MARK

after

MR.xlsm
	A	B	C	D	E	F	G	H
1		MARK	BRAND	QTY			MARK SHORT	MARK
2		BRIDGESTONE	BS 1200R20 G580 JAP	200			BS	BRIDGESTONE
3		FIRESTONE	FSR 1200R20 G580 JAP	120			FSR	FIRESTONE
4		DAYTON	DAT 1200R20 G580 JAP	300			DAT	DAYTON
5		BRIDGESTONE	BS 1200R20 G580 THI	230			GRD	GOODRIDE
6		FIRESTONE	FSR 1200R20 FSR4000 THI	120			KOHM	KOUHMO
7		DAYTON	DAT 1200R20 D50A THI	110
8		GOODRIDE	GRD 1200R20 GR423	13
9		KOUHMO	KOHM 1200R20 KT12	14
10		GOODRIDE	GRD 1400R20 VSJ	15
11
MARK

every time I add new data for both ranges.
when run the macro every time should search for items are added in first location in column C with matched column H if there are existed then don't add , just search for missed for any item.
thanks

 

[bebo021999]

Try below code:

Option Explicit
Sub AddMarkShort()
Dim i&, rng, short, sp
Dim dic As Object, key
Set dic = CreateObject("scripting.dictionary")
rng = Range("B2:C" & Cells(Rows.Count, "B").End(xlUp).Row).Value
short = Range("G2:H" & Cells(Rows.Count, "G").End(xlUp).Row).Value
For i = 1 To UBound(short)
    dic.Add short(i, 1), short(i, 2)
Next
For i = 1 To UBound(rng)
    sp = Split(rng(i, 2), " ")
    If Not dic.exists(sp(0)) Then
        For Each key In dic.keys
            If dic(key) = rng(i, 1) Then
                rng(i, 2) = key & " " & rng(i, 2)
                Exit For
            End If
        Next
    End If
Next
Range("B2:C" & Cells(Rows.Count, "B").End(xlUp).Row).Value = rng
End Sub

 

[abdo meghari]

Excellent !
many thanks for the code.