Combine 2 similar worksheets (live document)

[AlexMason]

Hi

I am wanting to use a largish excel sheet as a live document to monitor assembly parts, their quantities, their location etc.

Now, if project management didn't need to issue updated parts lists, then the guy on site would just keep this master document and perform all the house keeping himself as and when the parts arrive.

however, there may be a need for project management to update this master list (on a weekly basis). this update could contain more parts, less parts or the same number of parts but with altered attributes.

the main problem however is consolidating the two worksheets (ie merge the update into the master) without overwriting those parts which the site manager has already received and accounted for.

the code i have written is crude, but works.... however it cant handle it if the lists are different sizes. i.e. it wont add in additional parts, and wont remove parts that no longer exist in the update.

heres the code

[vba]Sub Consolidate()


Dim CheckRow, ListRow As Integer
Dim rMax, rMax2 As Integer
Dim OldPart As String
Dim NewPart As String
Dim Selection1 As String
Dim NUM As Integer
'If you tab out your functions like this then it is easier to see how the functions are organised


NUM = 0
rMax = FindLastRow1(3) 'finds the number of rows in master parts list
rMax2 = FindLastRow5(3) 'finds the number of rows in the check list

For CheckRow = 2 To rMax 'Cycle through the check

'Application.ScreenUpdating = False

If Sheets("KSL PARTS").Cells(CheckRow, 9) = "" Then 'If the check list line is blank

For ListRow = 2 To rMax2 'Cycle through the list

If Sheets("File Merge Sheet").Cells(ListRow, 1) = Sheets("KSL PARTS").Cells(CheckRow, 1) _
And Sheets("File Merge Sheet").Cells(ListRow, 2) = Sheets("KSL PARTS").Cells(CheckRow, 2) _
And Sheets("File Merge Sheet").Cells(ListRow, 3) = Sheets("KSL PARTS").Cells(CheckRow, 3) Then
'One big AND function, can be broken down if needed, this compares the first 3 columns between sheets

'MsgBox "Match found: Row " & CheckRow & " matches row " & ListRow


NewPart = ListRow & ":" & ListRow 'Makes a Cell location for updated row
OldPart = CheckRow & ":" & CheckRow 'makes a cekk location for row to be updated

Sheets("File Merge Sheet").Select 'activates the check list
Range(NewPart).Select 'selects the updated row
Selection.Copy 'copies
Sheets("KSL PARTS").Select 'selects master list
Range(OldPart).Select 'selects the row to be updated
ActiveSheet.Paste 'pastes the update over the old row
NUM = NUM + 1

End If

Next ListRow 'move onto next checklist row and repeat

End If

Next CheckRow ''after checking all checklist rows against single row on master list, cycle to next master list row, rinse and repeat

Selection1 = "2" & ":" & rMax2
Sheets("File Merge Sheet").Select
Range(Selection1).Select
Selection.Delete

Application.ScreenUpdating = True

MsgBox "Done: " & (NUM) & " Parts Updated"
Sheets("KSL PARTS").Select

End Sub[/vba]

this basically checks the "location" cell in the master list to see if a part needs updating. the rationale being that the site guy will have added the parts location to the sheet if he has received it. if he's received it then it shouldn't need to be changed.

you may also notice that, even on a fast machine...this takes a good few minutes to perform when you have over 1000 rows because it is linearly comparing all rows to all rows between sheets.

any help would be much appreciated. thanks, Alex

[AlexMason]

would a binary search work on data like this? i have numerous headings and although ive been told data needs to be pre sorted for binary search to work, this kind of data really doesnt have a specific way of being sorted...its just there and you can sort it how you please.

though i did hear binary search only finds one instance