can sort data based on replace ?

[abdelfattah]

hi
need macro to implement for about 1000 rows. the idea should match column A between two sheets sheet1,2 .if the data in column B into sheet2 are not matched with sheet1 then should replace data into column B for sheet2 based on sheet1 and arrange as the same thing in sheet1
sheet1

11.xlsx
	A	B
1	ITEM	BRAND
2	AA00-1	FOOD/100- TUNE 200G SS
3	AA00-2	FOOD/101- TUNE 160G SS L/NN
4	AA00-3	FOOD/102- TUNE 180G SS
5	AA00-4	FOOD/103- TU 120G SS
6	AA00-5	FOOD/104- TUNE 140G SS
7	AA00-6	FOOD/105- TUNE 200G
8	AA00-7	FOOD/106- TUNE 200G SS X-V1
9	AA00-8	FOOD/107- TUNE 200G SS
10	AA00-9	FOOD/108- TUNE 200G SS DDT
11	AA00-10	FOOD/109- TUNE 200G S
12
SHEET1

sheet2

11.xlsx
	A	B	C
1	ITEM	BRAND	QTY
2	AA00-1	FOOD/100- TUNE 200G N-G	200
3	AA00-6	FOOD/100- TUNE 2010G SS	123
4	AA00-7	FOOD/100- TUNE1 200G SS	1223
5	AA00-8	FOOD/100- TU 200G SS	90
6	AA00-2	FOOD/100- TU 200GBS	78
7	AA00-3	FOOD/100- TUNE 200G SS	23
8	AA00-4	FOOD/100- TUNE12 200G SS	45
9	AA00-5	FOOD/100- TUNE 200G SS	45
sheet2

result in sheet2

11.xlsx
	A	B	C
1	ITEM	BRAND	QTY
2	AA00-1	FOOD/100- TUNE 200G SS	200
3	AA00-2	FOOD/101- TUNE 160G SS L/NN	78
4	AA00-3	FOOD/102- TUNE 180G SS	23
5	AA00-4	FOOD/103- TU 120G SS	45
6	AA00-5	FOOD/104- TUNE 140G SS	45
7	AA00-6	FOOD/105- TUNE 200G	123
8	AA00-7	FOOD/106- TUNE 200G SS X-V1	1223
9	AA00-8	FOOD/107- TUNE 200G SS	90
sheet2

thanks

 

[mumps]

Try:

Sub MatchData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant
    Dim dic As Object, srcRng As Range, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    Set srcRng = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp))
    arr1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    arr2 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr1, 1)
        If Not dic.Exists(arr1(i, 1)) Then
            dic.Add arr1(i, 1), Nothing
        End If
    Next i
    For i = 1 To UBound(arr2, 1)
        If dic.Exists(arr2(i, 1)) Then
            If Not IsError(Application.Match(arr2(i, 1), srcRng, 0)) Then
                x = Application.Match(arr2(i, 1), srcRng, 0)
                desWS.Range("B" & i + 1).Value = srcWS.Range("B" & x + 1).Value
            End If
        End If
    Next i
    desWS.Cells(1, 1).Sort Key1:=desWS.Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

wow ! this is magnificent ! just question how can I implement your code for many sheets with the same structure ,please?
I have many sheets(sheet2,sheet3,sheet4)

 

[mumps]

Try:

Sub MatchData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, arr1 As Variant, arr2 As Variant
    Dim dic As Object, srcRng As Range, x As Long, ws As Worksheet
    Set srcWS = Sheets("Sheet1")
    With srcWS
        arr1 = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2).Value
        Set srcRng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            With ws
                arr2 = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2).Value
                Set dic = CreateObject("Scripting.Dictionary")
                For i = 1 To UBound(arr1, 1)
                    If Not dic.Exists(arr1(i, 1)) Then
                        dic.Add arr1(i, 1), Nothing
                    End If
                Next i
                For i = 1 To UBound(arr2, 1)
                    If dic.Exists(arr2(i, 1)) Then
                        If Not IsError(Application.Match(arr2(i, 1), srcRng, 0)) Then
                            x = Application.Match(arr2(i, 1), srcRng, 0)
                            ws.Range("B" & i + 1).Value = srcWS.Range("B" & x + 1).Value
                        End If
                    End If
                Next i
                ws.Cells(1, 1).Sort Key1:=ws.Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
            End With
            dic.RemoveAll
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

great assistance !
many thanks for your help

 

[mumps]

You are very welcome.

 

[abdelfattah]

sorry I came back , but I've found problem about when match column A . it doesn't seem arrange correctly based on sheet1
it seem filter based on number in column A .it should arrange based on sheet1
orginal in sheet1

11.xlsx
	A
1	ITEM
2	AA00-1
3	AA00-2
4	AA00-3
5	AA00-4
6	AA00-5
7	AA00-6
8	AA00-7
9	AA00-8
10	AA00-9
11	AA00-10
12	AA00-12
13	AA00-22
sheet1

based on your code it give this

11.xlsx
	A
1	ITEM
2	AA00-1
3	AA00-10
4	AA00-12
5	AA00-2
6	AA00-22
7	AA00-4
8	AA00-5
9	AA00-7
10	AA00-8
11	AA00-9
sheet2

but what I want how is arranged in sheet1 should be the same thing in sheet2 or others sheets

11.xlsx
	A
1	ITEM
2	AA00-1
3	AA00-2
4	AA00-4
5	AA00-5
6	AA00-7
7	AA00-8
8	AA00-9
9	AA00-10
10	AA00-12
11	AA00-22
sheet1

 

[mumps]

The macro is working properly for me. This is what I get:

Book2
	A	B	C
1	ITEM	BRAND	QTY
2	AA00-1	FOOD/100- TUNE 200G SS	200
3	AA00-2	FOOD/101- TUNE 160G SS L/NN	78
4	AA00-3	FOOD/102- TUNE 180G SS	23
5	AA00-4	FOOD/103- TU 120G SS	45
6	AA00-5	FOOD/104- TUNE 140G SS	45
7	AA00-6	FOOD/105- TUNE 200G	123
8	AA00-7	FOOD/106- TUNE 200G SS X-V1	1223
9	AA00-8	FOOD/107- TUNE 200G SS	90
Sheet2

 

[abdelfattah]

I'm still gussing your code filter based on the number not based on arranging into sheet1.
please test this data and inform me what happen for you.
sheet1

(version 1).xlsb
	A	B
1	ITEM	BRAND
2	AA00-1	FOOD/100- TUNE 200G SS
3	AA00-2	FOOD/101- TUNE 160G SS L/NN
4	AA00-4	FOOD/102- TUNE 180G SS
5	AA00-5	FOOD/103- TU 120G SS
6	AA00-7	FOOD/104- TUNE 140G SS
7	AA00-8	FOOD/105- TUNE 200G
8	AA00-9	FOOD/106- TUNE 200G SS X-V1
9	AA00-10	FOOD/107- TUNE 200G SS
10	AA00-12	FOOD/108- TUNE 200G SS DDT
11	AA00-22	FOOD/109- TUNE 200G S
sheet1

sheet2

(version 1).xlsb
	A	B
1	ITEM	BRAND
2	AA00-1	FOOD/100- TUNE 200G N-G
3	AA00-10	FOOD/100- TUNE 2010G SS
4	AA00-12	FOOD/100- TUNE1 200G SS
5	AA00-2	FOOD/100- TU 200G SS
6	AA00-22	FOOD/100- TU 200GBS
7	AA00-4	FOOD/100- TUNE 200G SS
8	AA00-5	FOOD/100- TUNE12 200G SS
9	AA00-7	FOOD/100- TUNE 200G SS
10	AA00-8	FOOD/105- TUNE 200G MM
11	AA00-9	FOOD/106- TUNE 200G SS X-V1 ZX
sheet2

this is what I got

(version 1).xlsb
	A	B
1	ITEM	BRAND
2	AA00-1	FOOD/100- TUNE 200G SS
3	AA00-10	FOOD/107- TUNE 200G SS
4	AA00-12	FOOD/108- TUNE 200G SS DDT
5	AA00-2	FOOD/101- TUNE 160G SS L/NN
6	AA00-22	FOOD/109- TUNE 200G S
7	AA00-4	FOOD/102- TUNE 180G SS
8	AA00-5	FOOD/103- TU 120G SS
9	AA00-7	FOOD/104- TUNE 140G SS
10	AA00-8	FOOD/105- TUNE 200G
11	AA00-9	FOOD/106- TUNE 200G SS X-V1
sheet2

 

[abdelfattah]

sorry I forgot column value in the picture . just see the two columns A,B and you will note it