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 srcWS As Worksheet, arr1 As Variant, arr2 As Variant, lRow As Long
    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
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Range("C2").FormulaR1C1 = "=MID(RC[-2],FIND(""-"",RC[-2])+1,99999)"
                .Range("C2").AutoFill Destination:=.Range("C2:C11"), Type:=xlFillDefault
                .Range("C2:C11").Value = .Range("C2:C11").Value
                .Cells(1, 3).Sort Key1:=.Columns(3), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
                .Columns(3).Delete
                dic.RemoveAll
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

thanks again , but why delete the value column ? I want it . just I forgot issue value column in post#10
your code still doesn't arrange
sheet1

ARRANGE.xlsm
	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/101- TUNE 160G SS L/NN1
5	AA00-4	FOOD/102- TUNE 180G SS
6	AA00-5	FOOD/103- TU 120G SS
7	AA00-6	FOOD/104- TUNE 140G SS M
8	AA00-7	FOOD/104- TUNE 140G SS
9	AA00-8	FOOD/105- TUNE 200G
10	AA00-9	FOOD/106- TUNE 200G SS X-V1
11	AA00-10	FOOD/107- TUNE 200G SS
12	AA00-12	FOOD/108- TUNE 200G SS DDT
13	AA00-22	FOOD/109- TUNE 200G S
14	AA00-23	FOOD/109- TUNE 200G S
15	AA00-24	FOOD/109- TUNE 200G S
16	AA00-25	FOOD/109- TUNE 200G S
17	AA00-26	FOOD/109- TUNE 200G S
18	AA00-27	FOOD/109- TUNE 200G S
19	AA00-28	FOOD/109- TUNE 200G S
20	AA00-29	FOOD/109- TUNE 200G S
21	AA00-30	FOOD/109- TUNE 200G S
22	AA00-31	FOOD/109- TUNE 200G S
23	AA00-32	FOOD/109- TUNE 200G S
24	AA00-33	FOOD/109- TUNE 200G S
25	AA00-34	FOOD/109- TUNE 200G S
26	AA00-35	FOOD/109- TUNE 200G S
27	AA00-36	FOOD/109- TUNE 200G S
sheet1

sheet2

ARRANGE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	AA00-1	FOOD/100- TUNE 200G SSHGF	200
3	AA00-2	FOOD/101-12 TUNE 160G SS L/NN	78
4	AA00-4	FOOD/102- TUNE ASW 180G SS	23
5	AA00-5	FOOD/103- TU 120G SS	200
6	AA00-10	FOOD/107- TUNE 200G SS BBG	120
7	AA00-12	FOOD/108- TUNE 200G SS DDT S	100
8	AA00-22	FOOD/109- TUNE 200G S	45
9	AA00-24	FOOD/109- TUNE 200G S S	45
10	AA00-25	FOOD/109- TUNE 200G S	123
11	AA00-33	FOOD/109- TUNE 200G S	1223
12	AA00-7	FOOD/104- TUNE 140G SS	90
13	AA00-8	FOOD/105- TUNE 200G	90
14	AA00-9	FOOD/106- TUNE 200G SS X-V1 ASD	90
15	AA00-3	FOOD/101- TUNE 160G SS L/NN1	90
sheet2

result based on your code

ARRANGE.xlsm
	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-10	FOOD/107- TUNE 200G SS
7	AA00-12	FOOD/108- TUNE 200G SS DDT
8	AA00-22	FOOD/109- TUNE 200G S
9	AA00-24	FOOD/109- TUNE 200G S
10	AA00-25	FOOD/109- TUNE 200G S
11	AA00-33	FOOD/109- TUNE 200G S
12	AA00-7	FOOD/104- TUNE 140G SS
13	AA00-8	FOOD/105- TUNE 200G
14	AA00-9	FOOD/106- TUNE 200G SS X-V1
15	AA00-3	FOOD/101- TUNE 160G SS L/NN1
sheet2

what I want

ARRANGE.xlsm
	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/101- TUNE 160G SS L/NN1	90
5	AA00-4	FOOD/102- TUNE 180G SS	23
6	AA00-5	FOOD/103- TU 120G SS	200
7	AA00-7	FOOD/104- TUNE 140G SS	90
8	AA00-8	FOOD/105- TUNE 200G	90
9	AA00-9	FOOD/106- TUNE 200G SS X-V1	90
10	AA00-10	FOOD/107- TUNE 200G SS	120
11	AA00-12	FOOD/108- TUNE 200G SS DDT	100
12	AA00-22	FOOD/109- TUNE 200G S	45
13	AA00-24	FOOD/109- TUNE 200G S	45
14	AA00-25	FOOD/109- TUNE 200G S	123
15	AA00-33	FOOD/109- TUNE 200G S	1223
sheet2

I hope this help

 

[mumps]

Try:

Sub MatchData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, arr1 As Variant, arr2 As Variant, lRow As Long
    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
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Range("D2").FormulaR1C1 = "=MID(RC[-3],FIND(""-"",RC[-3])+1,99999)"
                .Range("D2").AutoFill Destination:=.Range("D2:D" & lRow), Type:=xlFillDefault
                .Range("D2:D" & lRow).Value = .Range("D2:D" & lRow).Value
                .Cells(1, 4).Sort Key1:=.Columns(4), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
                .Columns(4).Delete
                dic.RemoveAll
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

excellent ! this fixes the arranging problem .
much appreciated !

 

[mumps]

My pleasure.