Hi

Thanks for that, it all works apart from the changing of the updated data.
My Formula for the checklist is as follows, there might be an easier/more
efficient way of doing this as well...

For each cell a check formula is being run whcih is :
=COUNTIF(Master!A$5:A$164,Update!A5)

then a summary is pulled together...the Check Cell:
=IF(Update!K5=0,"No",IF(Update!L5=0,"No",IF(Update !M5=0,"No",IF(Update!N5=0,"No",IF(Update!O5=0,"No" ,IF(Update!P5=0,"No",IF(Update!Q5=0,"No",IF(Update !R5=0,"No","Yes"))))))))

instead of TRUE or FALSE it will show No and Yes (TRUE = No, FALSE = Yes)

Any suggestions, have tried playing around with it, but still can't seem to
make it work.

Again thanks for the vb

"JLatham" wrote:

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan