Need help in automating data from multiple sheets into a single sheet

veedee · #1 06-11-2014, 01:04 AM

Hello,

I am new to excel and trying to figure out the best way to put this together. I have mockup data (file attached) from two tabs - sales and inventory. I would have to combine sales and inventory data and put it together in Summary tab. Mockup data is small but real data runs into thousands of rows. Hence planning to automate this.

Any help of this would be highly appreciated.

Thanks in advance,
Veed

SarahBear · #2 06-11-2014, 05:32 AM

You'd have to have a unique lookup value assigned to each thing you want to pull data on, but look into the vlookup function.

BobBridges · #3 06-11-2014, 06:35 AM

Nah, Veed said "Mockup data is small but real data runs into thousands of rows. Hence planning to automate this." Probably a good choice (says the guy who likes VBA anyway).

So Veed, how far have you gotten? Have you written any code yet?

EC37 · #4 06-11-2014, 12:31 PM

Another option could be to utilize Pivot Tables.
I used to run queries for a company previously that would return 100,000+ rows of data. Sometimes you have to get creative. Unless there is a true reason behind keeping the tabs for Sales and Inventory separate, you may want to combine the 2 together. Looks like you have similar data contained on both. I would then insert another column, such as, "Type" or something of the sort. Identify the rows accordingly 'sales' vs. 'inventory' data.

Then utilize the Pivot Table function to summarize the data. It presents really well, and is very simple to use. Just have to get the hang of setting up the table itself.

Bob- do you think that is also a viable solution as well?

BobBridges · #5 06-11-2014, 12:49 PM

I might...if I knew anything about pivot tables.

One of my sons is always telling me I gotta get into them, but so far I've never done one. Sorry, this is an area in which I'm almost totally ignorant.

No; scratch "almost".

EC37 · #6 06-11-2014, 12:56 PM

They are quite useful when producing summary reports! I tried playing around with the sample provided- but I couldn't come up with a way to present the inventory on the same table. But it summarized the sales exactly as it was shown on the Summary tab that was provided- it would be great for an export containing several thousand lines. Perhaps inventory does stay separate, unless someone else can determine a way to make it work.
..I tried!

macropod · #7 06-11-2014, 07:32 PM

Try:

Code:

Sub Summarize()
Application.ScreenUpdating = False
Dim i As Long, j As Long, x As Long, xlWkBk As Workbook, wsDest As Worksheet
Set xlWkBk = ThisWorkbook
With xlWkBk
  Set wsDest = .Sheets("Summary")
  With wsDest
    .UsedRange.ClearContents
    .UsedRange.CurrentRegion.Delete
    xlWkBk.Sheets("inventory").UsedRange.Columns("A:C").Copy
    .Paste Destination:=wsDest.Range("A1")
    .Columns("C:D").Insert Shift:=xlToRight
    .Cells(1, 3).Value = "Brand"
    .Cells(1, 4).Value = "Type"
  End With
  With .Sheets("sales").UsedRange
    For i = 2 To .Cells.SpecialCells(xlCellTypeLastCell).Row
      x = 0
      For j = 5 To wsDest.Cells.SpecialCells(xlCellTypeLastCell).Column
        If wsDest.Cells(1, j).Value = .Cells(i, 7).Value Then
          x = j
          Exit For
        End If
      Next
      If x = 0 Then
        x = j
        wsDest.Cells(1, x).Value = .Cells(i, 7).Value
      End If
      For j = 2 To wsDest.Cells.SpecialCells(xlCellTypeLastCell).Row
        If wsDest.Cells(j, 1).Value = .Cells(i, 1).Value Then
          If wsDest.Cells(j, 2).Value = .Cells(i, 2).Value Then
            If wsDest.Cells(j, 5).Value = .Cells(i, 5).Value Then
              If wsDest.Cells(j, 3).Value = "" Then
                wsDest.Cells(j, 3).Value = .Cells(i, 3).Value
              End If
              If wsDest.Cells(j, 4).Value = "" Then
                wsDest.Cells(j, 4).Value = .Cells(i, 4).Value
              End If
              wsDest.Cells(j, x).Value = .Cells(i, 6).Value
            End If
          End If
        End If
      Next
    Next
  End With
  With wsDest
    x = .Cells.SpecialCells(xlCellTypeLastCell).Column + 1
    xlWkBk.Sheets("inventory").UsedRange.Columns("D").Copy
    .Paste Destination:=wsDest.Range(Cells(1, x).Address)
    .Columns.AutoFit
  End With
End With
Application.ScreenUpdating = True
End Sub

carynsmith · #8 06-12-2014, 05:57 AM

The Consolidate tool allows you to combine worksheets where data is defined .

macropod · #9 06-12-2014, 10:04 PM

Quote:

Originally Posted by carynsmith

The Consolidate tool allows you to combine worksheets where data is defined .

Perhaps, then, you'd care to explain to the OP how to get the desired output using the Consolidate tool?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help in automating data from multiple sheets into a single sheet	veedee	Excel	1	06-11-2014 04:01 AM
Combine Data From 2 Sheets into a new sheet	bremen22	Excel	1	09-11-2013 12:59 PM
If two geographical data match in two sheets, copy unique id/code found in one sheet	alliage	Excel	1	09-01-2011 05:23 AM
moving data from single to multiple columns?	mzimmers	Excel	3	08-23-2010 08:20 AM
How do I merge data from one sheet in a workbook out into multiple sheets	nolesca	Excel	4	06-07-2010 08:13 AM