autofill values for 1000 rows based on compare between two sheets

M

MKLAQ

Active Member
Joined
Jan 30, 2021
Messages
428
Office Version
  1. 2016
Platform
  1. Windows
Hi experts
I hope finding macro to achieve my requirements. I have sheet MONTHLY should match whole data with the sheet REPORT then should brings the values and fill in columns E,F for sheet REPORT also should be sorting as in sheet MONTHLY . I have about 1000 rows .
EX (1) (3).xlsm
ABCDEF
1ITEMGOODSTYPEMODELPUSU
21 CR CCR-1 2010BMW38012
32 CR CCR-1 2011FV BMW1201203
43TR CCB-3 TCR 1-TT2009 MER12023
54CCR-2 FI 2012TIGUAN12045
65CCB-2 2008OPEL10011
76TR CCB-3 TMIR 1-TT2009 MER10144
87CCB-1 CR/MN-12011 AUDI8012
MONTHLY




EX (1) (3).xlsm
ABCDEF
1ITEMGOODSTYPEMODELPUSU
21CCR-2 FI 2012TIGUAN
32TR CCB-3 TCR 1-TT2009 MER
43 CR CCR-1 2011FV BMW
54TR CCB-3 TMIR 1-TT2009 MER
65 CR CCR-1 2010BMW
76CCB-2 2008OPEL
87CCB-1 CR/MN-12011 AUDI
REPORT



result

EX (1) (3).xlsm
ABCDEF
1ITEMGOODSTYPEMODELPUSU
21 CR CCR-1 2010BMW38012
32 CR CCR-1 2011FV BMW1201203
43TR CCB-3 TCR 1-TT2009 MER12023
54CCR-2 FI 2012TIGUAN12045
65CCB-2 2008OPEL10011
76TR CCB-3 TMIR 1-TT2009 MER10144
87CCB-1 CR/MN-12011 AUDI8012
REPORT
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
I am not sure I know what you want to do about sorting it in the Order of the Monthly sheet.
At this point I have updated the Item no to match the Monthly sheet item no and sorting on that. There are some pitfalls with that method.

But lets start with this:
VBA Code: 
Sub UpdateRpt()
    Dim shtMth As Worksheet, shtRpt As Worksheet
    Dim rngMth As Range, arrMth As Variant
    Dim rngRptInclHdg As Range, rngRpt As Range, arrRpt As Variant
    Dim dictMth As Object, dictKey As String
    Dim reseqRowNo As Long, i As Long
    
    Set shtMth = Worksheets("MONTHLY")
    Set rngMth = shtMth.Range("A1").CurrentRegion
    Set rngMth = rngMth.Offset(1).Resize(rngMth.Rows.Count - 1)
    arrMth = rngMth.Value
    
    Set shtRpt = Worksheets("REPORT")
    Set rngRptInclHdg = shtRpt.Range("A1").CurrentRegion
    Set rngRpt = rngRptInclHdg.Offset(1).Resize(rngRptInclHdg.Rows.Count - 1)
    arrRpt = rngRpt.Value
    reseqRowNo = 10 ^ Len(Application.Max(rngRpt.Columns(1)))
    
    Set dictMth = CreateObject("Scripting.dictionary")
    
    ' Load details range into Dictionary
    For i = 1 To UBound(arrMth)
        dictKey = arrMth(i, 2) & "|" & arrMth(i, 3) & "|" & arrMth(i, 4)
        If Not dictMth.exists(dictKey) Then
            dictMth(dictKey) = i
        End If
    Next i
    
    ' For Report get values from Dictionary
    For i = 1 To UBound(arrRpt)
        dictKey = arrRpt(i, 2) & "|" & arrRpt(i, 3) & "|" & arrRpt(i, 4)
        If dictMth.exists(dictKey) Then
            arrRpt(i, 1) = arrMth(dictMth(dictKey), 1)
            arrRpt(i, 5) = arrMth(dictMth(dictKey), 5)
            arrRpt(i, 6) = arrMth(dictMth(dictKey), 6)
        Else
            arrRpt(i, 1) = reseqRowNo + arrRpt(i, 1)
        End If
    Next i
    
    ' Write back Rpt updated data
    rngRpt.Value = arrRpt
    
    ' Re-sort Rpt
    shtRpt.Sort.SortFields.Clear
    shtRpt.Sort.SortFields.Add2 Key:=rngRptInclHdg.Columns(1) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With shtRpt.Sort
        .SetRange rngRptInclHdg
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub
 
Upvote 0
Solution
great ! the result as waht I want . but what do you mean ?
There are some pitfalls with that method.
Click to expand...
can cause the problem in the future ?
should sort from column B: D .the column A just to numbers sequences
 
Upvote 0
MKLAQ said:
can cause the problem in the future ?
Post #4 - should sort from column B: D .the column A just to numbers sequences
Click to expand...

MKLAQ said:
Post #1 - should be sorting as in sheet MONTHLY.
Click to expand...

Your statement in Post #1 was to Sort as per the Monthly sheet.
The sample data provided for the Monthly sheet does not appear to be sorting based on B:D and the only obvious sequence is ITEM no.

The only way I could replicate that on the REPORT sheet was to bring over the Monthly ITEM no and then give the "not found" Items a new no (I have added say 10000 to the front of the original no so you can still see the original no and the Not Found items have the original order)

So in the REPORT sheet, do you want the REPORT Item no or the Monthly Item no ?
How do you want to Sort the REPORT sheet ?
 
Upvote 0
actually you've solved my question . just curiosity to see how sort based on sheet MONTHLY instead of using column A I wanted how sort form column B: D and when autofill and sort then add sequences in column A into sheet REPORT .

anyway thanks for your help ;)
 
Upvote 0
See if this helps:
It sorts based on B,C,D in that order and then adds a sequence number in column A.

VBA Code: 
Sub UpdateRpt_v02_AlternateSort()

    Dim shtMth As Worksheet, shtRpt As Worksheet
    Dim rngMth As Range, arrMth As Variant
    Dim rngRptInclHdg As Range, rngRpt As Range, arrRpt As Variant
    Dim dictMth As Object, dictKey As String
    Dim reseqRowNo As Long, i As Long
    
    Set shtMth = Worksheets("MONTHLY")
    Set rngMth = shtMth.Range("A1").CurrentRegion
    Set rngMth = rngMth.Offset(1).Resize(rngMth.Rows.Count - 1)
    arrMth = rngMth.Value
    
    Set shtRpt = Worksheets("REPORT")
    Set rngRptInclHdg = shtRpt.Range("A1").CurrentRegion
    Set rngRpt = rngRptInclHdg.Offset(1).Resize(rngRptInclHdg.Rows.Count - 1)
    arrRpt = rngRpt.Value
    reseqRowNo = 10 ^ Len(Application.Max(rngRpt.Columns(1)))
    
    Set dictMth = CreateObject("Scripting.dictionary")
    
    ' Load details range into Dictionary
    For i = 1 To UBound(arrMth)
        dictKey = arrMth(i, 2) & "|" & arrMth(i, 3) & "|" & arrMth(i, 4)
        If Not dictMth.exists(dictKey) Then
            dictMth(dictKey) = i
        End If
    Next i
    
    ' For Report get values from Dictionary
    For i = 1 To UBound(arrRpt)
        dictKey = arrRpt(i, 2) & "|" & arrRpt(i, 3) & "|" & arrRpt(i, 4)
        If dictMth.exists(dictKey) Then
            arrRpt(i, 5) = arrMth(dictMth(dictKey), 5)
            arrRpt(i, 6) = arrMth(dictMth(dictKey), 6)
        End If
    Next i
    
    ' Write back Rpt updated data
    rngRpt.Value = arrRpt
    
    ' Re-sort Rpt
    shtRpt.Sort.SortFields.Clear
    shtRpt.Sort.SortFields.Add2 Key:=rngRptInclHdg.Columns(2) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    shtRpt.Sort.SortFields.Add2 Key:=rngRptInclHdg.Columns(3) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    shtRpt.Sort.SortFields.Add2 Key:=rngRptInclHdg.Columns(4) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With shtRpt.Sort
        .SetRange rngRptInclHdg
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Recreate ITEM / INDEX no based on result of Sort
    rngRpt.Cells(1, 1) = 1
    rngRpt.Columns(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Trend:=False
    
End Sub
 
Upvote 0
thanks again !
it doesn't seem to sort correctly based on sheet MONTHLY . may you check it please?
this is what I got
alex.xlsm
ABCDEF
1ITEMGOODSTYPEMODELPUSU
21 CR CCR-1 2010BMW38012
32 CR CCR-1 2011FV BMW1201203
43CCB-1 CR/MN-12011 AUDI8012
54CCB-2 2008OPEL10011
65CCR-2 FI 2012TIGUAN12045
76TR CCB-3 TCR 1-TT2009 MER12023
87TR CCB-3 TMIR 1-TT2009 MER10144
REPORT
 
Upvote 0
Unless you know the logic of the MONTHLY sheet sort because it doesn't seem to have any obvious logic to it,
the only way to get it to sort "based on sheet MONTHLY" is to do what I did originally and that is to use the MONTHLY ITEM no either in column A of REPORT like I did or in a helper column.
Anything not found in the MONTHLY sort would need to be sorted some other way.

A descending order sort on Column E would get you close but there doesn't appear to be any logic for sub-sorts where the values are the same.
Actually even that is not correct since the 101 is below the 100.
 
Upvote 0
Unless you know the logic of the MONTHLY sheet sort because it doesn't seem to have any obvious logic to it,
Click to expand...
this is dummy data. my idea enforce sorting based on sheet because if I accept normal sorting ,then I face problem when search for the items . my way to make easy process of searching . I thought to can achieve that depends on range from B:D without using helper column A .
the only way to get it to sort "based on sheet MONTHLY" is to do what I did originally
Click to expand...
as you like ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Merge duplicates Batches based on column contains the same price
2
Replies
13
Views
283
MKLAQ
M
K
  • Solved
Populate summary by sorting data for customer for each sheet alone based on dates
Replies
4
Views
175
KalilMe
K
C
  • Question Question
Linking two sheets in different workbooks takes a long time even with only 500 rows of data. Is there a quicker, more efficient way to do this?
Replies
0
Views
178
chazrab
C
M
  • Solved
delete new items in sheet and arrange again to put into adjacent cells
Replies
2
Views
341
MKLAQ
M
M
  • Question Question
add & merge new items across sheets
Replies
3
Views
337
MKLAQ
M

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top