Hi All,
Please help, need to know how to merge one workbook of old data to another workbook with new data and then delete duplicate lines.
Can it be done without cutting, pasting and then sorting, and manually deleting each line? Or could Chrystal do it all for me?
Thanks
Full Version: Excel Spreadsheets Merging Data
Many ways exist. It depends on the size of data really and what it contains, what identifies whether it's a duplicate or not. One sheet? Multiple sheets?
Simple solution, assuming some unique id in just one column (A moved to B for the formula below):
Copy and paste into destination sheet. Sort.
Insert column A. Write a formula "=b2=b1" or whatever is needed to tell the records are different
Propagate this formula down through all the data (pain in the neck to do)
Use auto filter's Custom selection, and select all TRUE rows. That's duplicated previous value.
On the edit menu, select DELETE ROWS.
More complicated but likely quicker - see Help for "Filter for unique records" - this method uses Advanced Filter, not the simplest thing to use, but works fine. The flexibility is there but requires building a query of sorts. So read help about "Advanced Filter"
Simple solution, assuming some unique id in just one column (A moved to B for the formula below):
Copy and paste into destination sheet. Sort.
Insert column A. Write a formula "=b2=b1" or whatever is needed to tell the records are different
Propagate this formula down through all the data (pain in the neck to do)
Use auto filter's Custom selection, and select all TRUE rows. That's duplicated previous value.
On the edit menu, select DELETE ROWS.
More complicated but likely quicker - see Help for "Filter for unique records" - this method uses Advanced Filter, not the simplest thing to use, but works fine. The flexibility is there but requires building a query of sorts. So read help about "Advanced Filter"
I'm not sure if this will work but you can give it a try. Before you do anything though make sure that you backup both workbooks.
http://www.ehow.com/how_13023_merge-workbooks-excel.html
http://www.ehow.com/how_13023_merge-workbooks-excel.html
In my experience with working with data this is the most simple solution below.
I use this function quite often and have made a VBA piece of code to do this function for me. If this is going to used often then that maybe a quicker way to go. I will even pass you the code. If you know where to put it.
QUOTE(tos226 @ Jan 8 2007, 03:33 AM) 
Simple solution, assuming some unique id in just one column (A moved to B for the formula below):
Copy and paste into destination sheet. Sort.
Insert column A. Write a formula "=b2=b1" or whatever is needed to tell the records are different
Propagate this formula down through all the data (pain in the neck to do)
Use auto filter's Custom selection, and select all TRUE rows. That's duplicated previous value.
On the edit menu, select DELETE ROWS.
Copy and paste into destination sheet. Sort.
Insert column A. Write a formula "=b2=b1" or whatever is needed to tell the records are different
Propagate this formula down through all the data (pain in the neck to do)
Use auto filter's Custom selection, and select all TRUE rows. That's duplicated previous value.
On the edit menu, select DELETE ROWS.
I use this function quite often and have made a VBA piece of code to do this function for me. If this is going to used often then that maybe a quicker way to go. I will even pass you the code. If you know where to put it.
Peter - Thank you very much for your input, but I wasn't able to find what I was looking for on that site.
Toss226 - Thank you also for your input and will be using what you have given.
Projectfocus - Really interested in that code- if you don't mind?
Would it work on a large spreadsheet? For eg:
Column A - business name
" B - address
" C - date
" E - job #
etc, etc. Which can occupy anywhere from 500 to 1200 lines.
We work on these every day (with new data being uploaded on a weekly and monthly basis), and the workbork can get quite large, so manipulating the data can take us half a day to a full day, so any kind of shortcut or formula that can do the job for us is a life saver!
Thank you all very much
greatly appreciated.
Toss226 - Thank you also for your input and will be using what you have given.
Projectfocus - Really interested in that code- if you don't mind?
Would it work on a large spreadsheet? For eg:
Column A - business name
" B - address
" C - date
" E - job #
etc, etc. Which can occupy anywhere from 500 to 1200 lines.
We work on these every day (with new data being uploaded on a weekly and monthly basis), and the workbork can get quite large, so manipulating the data can take us half a day to a full day, so any kind of shortcut or formula that can do the job for us is a life saver!
Thank you all very much
greatly appreciated.
Is the new data cumulative? If so, it would be much faster to completely repopulate the spreadsheet when the data changes in the original database.
Regards,
John
Regards,
John
QUOTE(jgweed @ Jan 9 2007, 10:34 AM)
Is the new data cumulative? If so, it would be much faster to completely repopulate the spreadsheet when the data changes in the original database.
Regards,
John
Regards,
John
Yes it is cumulative. The problem is though that the data is drawn from a web based programme via a reporting function. Which makes it hard as the data is always changing, so repopulating is too difficult because of mass production.
Any ideas??
Also, on another, it comes in text format which is run on a daily basis, which I convert to a csv file before cutting and pasting to Excel.
Any ideas on this one would also be appreciated.
Thanks
Is only the data changing, or do the fields in each record change? I'm not sure I understand your last sentence.
Cheers,
John
Cheers,
John
Sorry, the fields stay the same. So only the data changes.
Try this out for size.
Add code into a module or the Thisworkbook part of the VB.
If you need help on this just ask.
If you need to have a more in depth and complicated de duping then ask and I will see if I can manipulate the code.
Hope this helps.
Add code into a module or the Thisworkbook part of the VB.
If you need help on this just ask.
If you need to have a more in depth and complicated de duping then ask and I will see if I can manipulate the code.
CODE
Sub DeDupRows()
Dim i As Integer
Dim DupColumn As String
Dim Header As String
DupColumn = InputBox("Enter the column for the depulicates to be validated on")
Header = InputBox("Is there a Header Row? Y or N")
If Header = "Y" Or Header = "y" Then
ActiveSheet.Cells.Select
Selection.Sort Key1:=Range(DupColumn & "2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
If Header = "N" Or Header = "n" Then
ActiveSheet.Cells.Select
Selection.Sort Key1:=Range(DupColumn & "1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
i = 1
Do
If ActiveSheet.Range(DupColumn & i).Value = ActiveSheet.Range(DupColumn & i + 1).Value Then
ActiveSheet.Rows(i).Delete
i = i - 1
End If
i = i + 1
Loop Until ActiveSheet.Range(DupColumn & i).Value = ""
MsgBox ("Duplicates have been removed")
End Sub
Dim i As Integer
Dim DupColumn As String
Dim Header As String
DupColumn = InputBox("Enter the column for the depulicates to be validated on")
Header = InputBox("Is there a Header Row? Y or N")
If Header = "Y" Or Header = "y" Then
ActiveSheet.Cells.Select
Selection.Sort Key1:=Range(DupColumn & "2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
If Header = "N" Or Header = "n" Then
ActiveSheet.Cells.Select
Selection.Sort Key1:=Range(DupColumn & "1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
i = 1
Do
If ActiveSheet.Range(DupColumn & i).Value = ActiveSheet.Range(DupColumn & i + 1).Value Then
ActiveSheet.Rows(i).Delete
i = i - 1
End If
i = i + 1
Loop Until ActiveSheet.Range(DupColumn & i).Value = ""
MsgBox ("Duplicates have been removed")
End Sub
Hope this helps.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.