compare between two sheets and replace and arrange with empty cells

[Hasson]

Hi
I want matching sheet MATCH with sheet REPORT based on column B between two sheets if they are matched then should replace items in column C for sheet MATCH based on sheet REPORT and arrange data for sheet MATCH based on column B for sheet REPORT and any items for column C contains empty cell for column B in sheet MATCH should put in last row .

s.xlsx
	A	B	C	D	E	F
1	ITEM	CODE	DESCIBE	QTY	UNIT PRICE	TOTAL
2	1	ATR-A100	CLA1 123	200	200	40000
3	2	ATR-A105	CLA2 VBG L1/m	300	300	90000
4	3	ATR-A106	CLA3-100	400	400	160000
5	4	ATR-A103	CLA41	500	500	250000
6	5		CLA12	22	11	242
7	6	ATR-A104	CLA5	600	600	360000
8	7		CLA10	12	12	144
9	8	ATR-A101	M230	700	700	490000
10	9	ATR-A102	CLA7	800	800	640000
11	10	ATR-A109	CLA8	900	900	810000
12	11	ATR-A110	CLA9 N	1000	1000	1000000
13	12	ATR-A111	CLA10	1100	1100	1210000
14	13	ATR-A112	LVD	1200	1200	1440000
15	14	ATR-A113	CLA12	1300	1300	1690000
16	15	ATR-A114	CLA13	1400	1400	1960000
17	16	ATR-A115	CLA14 SS230	1500	1500	2250000
18	17	ATR-A114	CLA15	1600	1600	2560000
19	18	ATR-A115	CLA16	1700	1700	2890000
20	19	ATR-A116	CLA17	1800	1800	3240000
21	20	ATR-A117	RRSDF	1900	1900	3610000
match

Cell Formulas
Range	Formula
F2:F21	F2	=D2*E2

s.xlsx
	A	B	C
2	1	ATR-A100	CLA1 23M-1
3	2	ATR-A101	CLA2 VBG L
4	3	ATR-A102	CLA3
5	4	ATR-A103	CLA4
6	5	ATR-A104	CRET3
7	6	ATR-A105	M230
report

result

s.xlsx
	A	B	C	D	E	F
1	ITEM	CODE	DESCIBE	QTY	UNIT PRICE	TOTAL
2	1	ATR-A100	CLA1 23M-1	200	200	40000
3	2	ATR-A101	CLA2 VBG L	700	700	490000
4	3	ATR-A102	CLA3	800	800	640000
5	4	ATR-A103	CLA4	500	500	250000
6	5	ATR-A104	CRET3	600	600	360000
7	6	ATR-A105	M230	300	300	90000
8	7	ATR-A106	CLA3-100	400	400	160000
9	8	ATR-A109	CLA8	900	900	810000
10	9	ATR-A110	CLA9 N	1000	1000	1000000
11	10	ATR-A111	CLA10	1100	1100	1210000
12	11	ATR-A112	LVD	1200	1200	1440000
13	12	ATR-A113	CLA12	1300	1300	1690000
14	13	ATR-A114	CLA13	1400	1400	1960000
15	14	ATR-A115	CLA14 SS230	1500	1500	2250000
16	15	ATR-A114	CLA15	1600	1600	2560000
17	16	ATR-A115	CLA16	1700	1700	2890000
18	17	ATR-A116	CLA17	1800	1800	3240000
19	18	ATR-A117	RRSDF	1900	1900	3610000
20	19		CLA12	22	11	242
21	20		CLA10	12	12	144
result

Cell Formulas
Range	Formula
F2:F21	F2	=D2*E2

thanks

 

[DanteAmor]

Try this:

Sub comparesheets()
  Dim f As Range, c As Range
  Dim lr As Long
  
  With Sheets("RESULT")
    Sheets("MATCH").Cells.Copy .Range("A1")
    lr = .Range("A" & Rows.Count).End(3).Row
    .Range("B1:F" & lr).Sort key1:=.Range("B1"), order1:=1, Header:=xlYes
    For Each c In .Range("B2:B" & lr)
      If c.Value <> "" Then
        Set f = Sheets("REPORT").Range("B:B").Find(c.Value, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          .Range("C" & c.Row).Value = f.Offset(, 1).Value
        End If
      End If
    Next
  End With
End Sub

 

[Hasson]

awesome!
this is really helpful . just question can I replace column C in sheet match from column C sheet report based on matching column B between two sheets , sometimes there is different items in column C between two sheets for the same items for column B betwwen two sheets.
for instance sheet match,report column B=ATR-100 but column C in sheet match=CL2-23A1 , column C for sheet report= CLA2-23A1 then item in column C for sheet match =CLA2-23A1.
the rsult should be in sheet MATCH
thanks again