I have two sheets in Excel that both contain data and would like to merge data.

[waldz]

Hi Guys,

I have two sheets in Excel that both contain data.

Both pages have a unique column which is labeled 'ItemNumber'. This runs from 100000 through to 106144. Apart from this column, both sheets have different columns with different data. I would consider the 'ItemNumber' to be the unique identifier of the row.

I would like to create another sheet that brings in and merges all the data from both sheet.

How would I do this? I guess it would be some sort of merge? Hoping someone can point me in the right direction!

(sorry if I'm not as clear as I could be)

 

[hiker95]

waldz,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

both sheets have different columns with different data.

So that we can get it right the first time, can we see your workbook with the two raw data worksheets, and, in a new worksheet (manually formatted by you) the results you are looking for?


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

waldz,

The above workbook containing about 20 rows in each worksheet.

 

[waldz]

Thanks Hiker95,

So in response to your questions . . .

1) I am using Excel 2010 with Windows 7.
2) I'm using a PC.

I have created a sample file, including some working data as requested . . .

goo.gl/cuazsW

Please note the following:

i) The 'ItemNumber' runs in sequence with no gaps - I have recreated this on the sheet 'merged (3)'. However the data from the other 2 sheets often has gaps in the sequence.
ii) I would regularly copy and paste data into both 'inventory (1)' and 'consumption rate (2)'

Do let me know if you need any more information.

Waldz

 

[hiker95]

waldz,

Can I have another workbook (like the above) with worksheet merged (3) manually formatted by you for the results you are looking for?

 

[waldz]

Sure,

I have written over the original file with 'merged (3)' populated as requested. I believe the link would be the same but here it is just in case . . .

http://goo.gl/NMTJIj

 

[hiker95]

waldz,

Thanks for the latest workbook.

With your workbook containing worksheets inventory (1), and, consumption rate (2), the macro will create worksheet merged (3) with its titles in row 1, columns A thru W, and, with the ItemNumber's in column A, beginning in cell A2, with the data series 100000 thru 106144.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub MergeSheets()
' hiker95, 04/04/2014, ME769043
Dim wi As Worksheet, wc As Worksheet, wm As Worksheet
Dim MyStart As Long, MyStop As Long, n As Long
Dim c As Range, inrng As Range
Application.ScreenUpdating = False
Set wi = Sheets("inventory (1)")
Set wc = Sheets("consumption rate (2)")
If Not Evaluate("ISREF('merged (3)'!A1)") Then Worksheets.Add(After:=wc).Name = "merged (3)"
Set wm = Worksheets("merged (3)")
wm.UsedRange.Clear
wm.Cells(1).Resize(, 23).Value = Array("ItemNumber", "ItemTitle", "SoldQty", "CurrentStock", _
    "Available", "DailyConsumptionRate", "OnOrder", "AvailableStockDaysLeft", "StandardDeviation", _
    "CreationDate", "bLogicalDelete", "RetailPrice", "Weight", "BinRack", "PurchasePrice", _
    "BarcodeNumber", "ShippedSeparately", "bContainsComposites", "VariationsGroupName", _
    "IsMainVariation", "ModifiedDate", "ModifiedUserName", "ModifyAction")
MyStart = 100000
MyStop = 106144
n = (MyStop - MyStart) + 2
wm.Range("A2") = MyStart
wm.Range("A2:A" & n).DataSeries Step:=1, Stop:=MyStop
With wi
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set inrng = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not inrng Is Nothing Then
      wi.Range("B" & c.Row).Copy wm.Range("B" & inrng.Row)
      wi.Range("C" & c.Row).Copy wm.Range("J" & inrng.Row)
      wi.Range("D" & c.Row & ":P" & c.Row).Copy wm.Range("K" & inrng.Row)
      Application.CutCopyMode = False
    End If
  Next c
End With
With wc
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set inrng = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not inrng Is Nothing Then
      wc.Range("C" & c.Row & ":I" & c.Row).Copy wm.Range("C" & inrng.Row)
      Application.CutCopyMode = False
    End If
  Next c
End With
With wm
  .Columns("A:W").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MergeSheets macro.

 

[hiker95]

waldz,

If worksheets inventory (1) , and, consumption rate (2), have the same ItemNumber, sometimes the ItemTitle's:

Excel 2007
	A	B
6	100029	Flashback Case (calculator)
7	100030	Flashback Case (camera)
8	100032	Flashback Case (capsules)
9	100035	Funny Dummy (extreme goof)
10	100036	Funny Dummy (goof)
11	100038	Funny Dummy (happy as larry)
12	100039	Funny Dummy (I'm furious)
13	100040	Funny Dummy (I'm waiting)
14	100041	Funny Dummy (extreme goof)
inventory (1)

Excel 2007
	A	B
6	100029	Rubber Dust Caps - Black
7	100030	Ring Bottle Opener (chrome)
8	100032	Hanging Stylus (silver)
9	100035	Rubber Dust Caps - White
10	100036	Plugy (leaf)
11	100038	Plugy (bolt)
12	100039	Plugy (tap)
13	100040	Plugy (toadstool)
14	100041	Plugy (key)
consumption rate (2)

Which worksheet for columns A, and, B, do you want to use in worksheet merged (3), columns A, and, B?

I do have another macro that will copy columns A, and, B, from worksheet consumption rate (2), but, it will overwrite the data from worksheet inventory (1).

 

[hiker95]

waldz,

I am off on a fishing trip this weekend with my son, and, will be back on Monday, 04/07/2014.

If you need further assistance now, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[waldz]

Hiker95,

You've been a great help, it appears to work beautifully. Your fantastic efforts will allow me now to figure out what I would like to adapt next.

If you're available to continue helping me with this after your trip then let me know (I'm quite happy to pay you for the time spent on the project).

Fishing trip? Wow, sounds fantastic - Enjoy!