Update Master List if required...

[deepheat101]

Greetings Excel Experts

I've got four columns of data on two different sheets (Master and Update) that I need to amend subject to the following rules -

• If a key appears on the Update list that is on the Master list then add the data item in the next available column.
  
  If a "key" appears on the Update list that is not in the Master list then it should be inserted into the correct place (alphabetically) along with its data item in the "appropriate column".
  
  However, if an item appears on the Master list that is not on the Update list then a blank entry needs to be added to the next available column.

Hopefully, the following example will help illustrate the issue....

Before....

Master         Update
Key             Key DataItem
 A  11 10        A    13
 C  14 14        B    10
 D  11 10        C    14
 F  12 11        D    10
 G  16 16        G    16

After...

Master
Key
 A  11 10 13
 B        10
 C  14 14 14
 D  11 10 10
 F  12 11
 G  16 16 16

Any assistance or guidance is much appreciated,

Thanks for looking

Dave

 

[onlyadrafter]

Hello,

A couple of questions to begin with

1) WHen do you want these events to happen?

If a "key" appears on the Update list that is not in the Master list then it should be inserted into the correct place (alphabetically) along with its data item in the "appropriate column".

What is the appropriate column?

 

[deepheat101]

Hi There OnlyaDrafter

Many thanks for your response...

The update should kick-off when I click on a command button that currently resides on the Master sheet (at the moment, the command button retrieves the raw Update source file and amends the format to that shown in the example.

The "appropriate column" would be the next available column to the right - all of the DataItems from a particular Update (blank or otherwise) have to appear under the same column.

Hope this answers your queries...

Many Thanks for looking

 

[onlyadrafter]

Hello,

How is this for starters?

Sub MATCH_UPDATE_WITH_MASTER()
MY_NEXT_COLUMN = Sheets("MASTER").UsedRange.Columns.Count
For MY_UPDATE_ROWS = 1 To Sheets("UPDATE").Range("A65536").End(xlUp).Row
    MATCH_FOUND = 0
    MY_UPDATE_KEY = Sheets("UPDATE").Range("A" & MY_UPDATE_ROWS).Value
        For MY_MASTER_ROWS = 1 To Sheets("MASTER").Range("A65536").End(xlUp).Row + 1
            MY_MASTER_KEY = Sheets("MASTER").Range("A" & MY_MASTER_ROWS).Value
            If MY_UPDATE_KEY = MY_MASTER_KEY Then
                Sheets("MASTER").Range("IV" & MY_MASTER_ROWS).End(xlToLeft).Offset(0, 1).Value = _
                    Sheets("UPDATE").Range("B" & MY_UPDATE_ROWS).Value
                MATCH_FOUND = 1
            End If
            If MY_MASTER_ROWS = Sheets("MASTER").Range("A65536").End(xlUp).Row + 1 And _
                MATCH_FOUND = 0 Then
                Sheets("MASTER").Range("A65536").End(xlUp).Offset(1, 0) = _
                    Sheets("UPDATE").Range("A" & MY_UPDATE_ROWS)
                Sheets("MASTER").Range("A65536").End(xlUp).Offset(0, MY_NEXT_COLUMN).Value = _
                    Sheets("UPDATE").Range("B" & MY_UPDATE_ROWS).Value
            End If
        Next MY_MASTER_ROWS
Next MY_UPDATE_ROWS
    Sheets("MASTER").Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub

 

[deepheat101]

Hi There

Many thanks for the script!

It works wonderfully with a small sample size. However, with my full dataset of just over a thousand items (and projected to double within a year) it takes around 3 to 4 minutes to complete. Admittedly that's a lot faster than the hour or so that it currently takes me to do manually



Many thanks

 

[onlyadrafter]

Hello,

Does putting

Application.screenupdating=false after the SUB..

and

Application.screenupdating=true before the End Sub

make any difference? I don't think it will.

 

[deepheat101]

Using your script as the kicking off point, the following speeds through in seconds...

Sub MATCH_UPDATE_WITH_MASTER()

Application.ScreenUpdating = False
MY_NEXT_COLUMN = Sheets("MASTER").UsedRange.Columns.Count
Range("MASTER!A1").Activate
For MY_UPDATE_ROWS = 1 To Sheets("UPDATE").Range("A65536").End(xlUp).Row
    
    MATCH_FOUND = 0
    
    MY_UPDATE_KEY = Sheets("UPDATE").Range("A" & MY_UPDATE_ROWS).Value
        
            Set rngFound = Cells.Find(What:=MY_UPDATE_KEY, After:=ActiveCell, _
                                      LookAt:=xlPart, LookIn:=xlValues, _
                                      SearchOrder:=xlByColumns, _
                                      SearchDirection:=xlNext, MatchCase:=False)
            If Not rngFound Is Nothing Then        '     i.e.  if a match is found
                rngFound.Activate
                MY_MASTER_ROW = ActiveCell.Row
                Sheets("MASTER").Range("A" & MY_MASTER_ROW).Offset(0, MY_NEXT_COLUMN).Value = _
                       Sheets("UPDATE").Range("B" & MY_UPDATE_ROWS).Value
            Else
                Sheets("MASTER").Range("A65536").End(xlUp).Offset(1, 0) = _
                       Sheets("UPDATE").Range("A" & MY_UPDATE_ROWS)
                Sheets("MASTER").Range("A65536").End(xlUp).Offset(0, MY_NEXT_COLUMN).Value = _
                       Sheets("UPDATE").Range("B" & MY_UPDATE_ROWS).Value
            End If

Next MY_UPDATE_ROWS
    
    Sheets("MASTER").Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
    Application.ScreenUpdating = True

End Sub

Couldn't have got there without your help

Many Thanks once again.