Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel Spreadsheets Merging Data


  • Please log in to reply
10 replies to this topic

#1 ghostwriter

ghostwriter

  • Members
  • 91 posts
  • OFFLINE
  •  
  • Local time:08:27 PM

Posted 07 January 2007 - 07:48 PM

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
What you do in life does matter……Each choice and action you make has
consequences, good or bad. Which path will you take?

BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,577 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:09:27 PM

Posted 07 January 2007 - 10:33 PM

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"

#3 _Peter_

_Peter_

  • Members
  • 22 posts
  • OFFLINE
  •  
  • Local time:08:27 PM

Posted 07 January 2007 - 10:44 PM

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

#4 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:02:27 AM

Posted 08 January 2007 - 08:12 AM

In my experience with working with data this is the most simple solution below.

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.


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.
FocusToonSigStreched.jpg

#5 ghostwriter

ghostwriter
  • Topic Starter

  • Members
  • 91 posts
  • OFFLINE
  •  
  • Local time:08:27 PM

Posted 09 January 2007 - 05:26 AM

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. :thumbsup:
What you do in life does matter……Each choice and action you make has
consequences, good or bad. Which path will you take?

#6 jgweed

jgweed

  • Members
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:08:27 PM

Posted 09 January 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
Whereof one cannot speak, thereof one should be silent.

#7 ghostwriter

ghostwriter
  • Topic Starter

  • Members
  • 91 posts
  • OFFLINE
  •  
  • Local time:08:27 PM

Posted 09 January 2007 - 06:22 PM

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



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

Edited by ghostwriter, 09 January 2007 - 06:53 PM.

What you do in life does matter……Each choice and action you make has
consequences, good or bad. Which path will you take?

#8 jgweed

jgweed

  • Members
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:08:27 PM

Posted 09 January 2007 - 06:51 PM

Is only the data changing, or do the fields in each record change? I'm not sure I understand your last sentence.
Cheers,
John
Whereof one cannot speak, thereof one should be silent.

#9 ghostwriter

ghostwriter
  • Topic Starter

  • Members
  • 91 posts
  • OFFLINE
  •  
  • Local time:08:27 PM

Posted 09 January 2007 - 09:13 PM

Sorry, the fields stay the same. So only the data changes.
What you do in life does matter……Each choice and action you make has
consequences, good or bad. Which path will you take?

#10 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:02:27 AM

Posted 10 January 2007 - 08:33 AM

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.

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

Hope this helps.

Edited by projectfocus, 10 January 2007 - 08:33 AM.

FocusToonSigStreched.jpg

#11 markdavis

markdavis

  • Members
  • 2 posts
  • OFFLINE
  •  
  • Local time:09:27 PM

Posted 08 September 2010 - 04:54 AM

If you are not inclined to do a file merge with VBA, you can do the merge with automated software such as Bulk File Merger. Here is a link to merge excel spreadsheets. I just managed to combine over 70 files in a matter of minutes. Data is vertically appended for all file types mind you.

Not sure how the Mac version works but the Windows version worked quite well.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users