Merging spreadsheets on excel

March 2, 2015 at 15:51:25
Specs: Windows 8
Hi, Is it possible to merge 2 different spreadsheets according to say IDs and dates. The problem is spreadsheet1 is larger than spreadsheet2 (i.e. there are more rows/some dates that are in spreadsheet 1 aren't in spreadsheet 2). I want to align them according to IDs and dates(allowing me to bring over corresponding data from spreadsheet 2 into spreadsheet1), creating new blank rows for dates that are in spreadsheet 1 but not in spreadsheet 2. Does that make sense?

See More: Merging spreadsheets on excel

Report •


#1
Report •

#2
March 2, 2015 at 17:20:44
Merging the spreadsheets could certainly be done with VBA but we would need to know lots of details related to the layout of spreadsheets.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
March 2, 2015 at 17:49:42
Hi DerbyDad03,

I have 2 spreadsheets of longitudinal data. So for exg. column A is ID, and column B contains dates, so in cells A2 to A40, the ID is 10001, cells B2 to B40 have dates (04/05/2010,05/05/2010....), For the next ID, 10002 would be in cells A41 to say, A50, and column B (B41 to B50) will have the corresponding dates There is more data in the other columns (say C to CY)which i want to retain. Both spreadsheets are set up this way. What I need to do is match up columns A and B in spreadsheet 1 with columns A and B in spreadsheet 2 and once they are matched, copy columns C to CF from spreadsheet 2 into spreadsheet 1 (placing them in empty columns at the end of filled existing columns, (say the last column with data is CY, then these columns from spreadsheet 2 will take up residence in CZ onwards). Problem is, spreadsheet 2 is slightly smaller than spreadsheet 1 meaning there will be some ID/date pairs in spreadsheet 1 that are not present in spreadsheet 2. When this is the case, all I need are blank rows as there is nothing to copy over from spreadsheet 2 for those dates. Hoping this can be done with VBA Is there any other information needed? I tried to be as clear as possible, if you have any questions or need more clarification re the layout please let me know.

Many thanks in advance!!


Report •

Related Solutions

#4
March 3, 2015 at 13:09:46
Try this...

This code assumes that Sheet 1 contains at least as many Rows and Columns of data as Sheet 2. As written, more data on Sheet 1 is fine, less data on Sheet1 will be a problem.

It also assumes that the each row on Sheet1 contains the same amount of data. In other words, if the last column is e.g. CY in Row 2, then the last column is CY in every row.

The code works as follows:

First, the code inserts a temporary Column A on both sheets. (this is typically referred to as a "Helper Column") It then concatenates the ID and Date from Columns B & C and puts the combination in the new Column A. This give the code a string to search for.

The code then loops through the temp Column A on Sheet 1 looking for each ID-Date combination in the temp Column A on Sheet 2. Whenever it finds an ID-Date combination on Sheet 2, it copies the data from column D through the last column in that row to the next empty column on Sheet 1.

Once it has copied all of the data, the code deletes the temporary Column A from both sheets.

I hope that makes sense...

Option Explicit
Sub MergeSheets()
Dim lastRw, lastCol, srcRw
Dim m
'Determine last Row and Column with data in Sheet 1
   lastRw = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
   lastCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

'Insert Temp Column A on Sheet 1 and Sheet2, add Concatenation formula
    With Sheets(1)
      .Columns(1).Insert
      .Range(.Cells(2, 1), .Cells(lastRw, 1)).FormulaR1C1 = "=RC[1]&RC[2]"
    End With
    
    With Sheets(2)
     .Columns(1).Insert
     .Range(.Cells(2, 1), .Cells(lastRw, 1)).FormulaR1C1 = "=RC[1]&RC[2]"
    End With

'Loop through data in Sheet 1 Temp Column A
    For srcRw = 2 To lastRw

'Search for Sheet1 Temp Column A data in Sheet 2 Temp Column A
      With Sheets(2)
       Set m = .Columns(1).Find(Sheets(1).Cells(srcRw, 1), _
                lookat:=xlWhole, LookIn:=xlValues)

'If ID/Date combination found on Sheet 2, Copy Sheet 2 data to next Column on Sheet 1
         If Not m Is Nothing Then
          .Range(.Cells(2, 4), .Cells(2, lastCol + 1)).Copy _
            Destination:=Sheets(1).Cells(srcRw, lastCol + 2)
         End If
      End With
    Next

'Delete Temp Column A On Sheet 1 & 2
      Sheets(1).Columns(1).EntireColumn.Delete
      Sheets(2).Columns(1).EntireColumn.Delete
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
March 4, 2015 at 06:55:36
That makes sense, thank you sooo much! Excited to try this code out!!!

Report •

#6
March 4, 2015 at 09:23:25
BTW...If all of the columns on Sheet 1 are not of equal length (e.g. CY, CP, DA, etc.) that is not a deal breaker.

We would just have to modify the code to determine the last column in each row for which a match was found. If they are all the same, it's just more efficient to find the last column once and use that single value throughout the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
March 4, 2015 at 10:05:27
Oh, I see what is going on. Unfortunately, not all the rows are equal in length (i.e. end at the same column)because some have data missing. However, since the last column with a heading in spreadsheet 1 is HT, would be possible to specify that columns in spreadsheet 2 can start taking up residence in spreadsheet 2 starting in column HU onwards? Would that complicate things a lot? =(

Report •

#8
March 4, 2015 at 17:36:47
Untested...

EDIT!

Try changing this line...

   Destination:=Sheets(1).Cells(srcRw, lastCol + 2)

To

   Destination:=Sheets(1).Cells(srcRw, "HU")

   Destination:=Sheets(1).Cells(srcRw, "HV")

That should take care pasting the data into column HU.

I forgot about the fact that we are inserting a temp Column A, therefore the Sheet 2 Data has to be pasted into HV so that when the temp column is deleted, it ends up in HU). That's what happens when I post without testing. ;-)

However, that leaves things a little sloppy. The following line is used to determine the last column in Sheet 1, Row 1.

  lastCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Originally that value was used for 2 reasons:

1 - Find out how many columns contain data on Sheet 1 to determine where to paste the Sheet 2 data
2 - Use that number to set the range to be copied from Sheet 2.

Now that we no longer need that value to determine where to paste the Sheet 2 data, it's kind of sloppy to be using a value from Sheet 1 to "indirectly" determine the range to copy on Sheet 2.

The lastCol instruction should probably refer to Sheet 2, not Sheet 1. I think that should work, but I can't test anything tonight.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
March 5, 2015 at 06:24:02
Please see the edit to my previous post re:

Destination:=Sheets(1).Cells(srcRw, "HV")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 8, 2015 at 18:47:48
Thank you so much, its running now, fingers crossed!!

Report •

#11
March 8, 2015 at 19:08:16
Hmmm, its not copying for some reason =( But the concatenation is happening because I see it when the program is running...

Report •

#12
March 8, 2015 at 20:46:46
Did you make the changes I suggested? What is in Row 1 of Sheet 2? Are there column headings out to the end of the Sheet 2 data?

Perhaps you could spend some time with this tutorial and do a little troubleshooting. If you are going to be relying on VBA to get your work done, it couldn't hurt to become familiar with its inner workings.

The steps contained in this tutorial are the ones I would use if I had a copy of your workbook and the code wasn't working.

http://www.computing.net/howtos/sho...

message edited by DerbyDad03


Report •

Ask Question