Update Master List if required...

deepheat101

Board Regular
Joined
Jul 26, 2006
Messages
138
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....
Code:
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...
Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

Board Regular
Joined
Jul 26, 2006
Messages
138
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

How is this for starters?

Code:
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

Board Regular
Joined
Jul 26, 2006
Messages
138

ADVERTISEMENT

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 :biggrin:



Many thanks
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

Board Regular
Joined
Jul 26, 2006
Messages
138
Using your script as the kicking off point, the following speeds through in seconds...

Code:
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

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
Top