Consolodating sheets in excel
I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. it looks like this: I'm stuck with a row for Customer 1 for Value A in 2009 and a year. In the example above, you can see that Customer 1 has no Value B in 2011, so no row was generated for that. I want to get this into a worksheet where there's Sub test() 'define variables Dim Row Num as long, Last Row As long 'turn off screen updating Application. Select 'For loop for all rows in selection with cells For Each Row In Selection With Cells 'if customer name matches If Cells(Row Num, 1) = Cells(Row Num 1, 1) Then 'and if customer year matches If Cells(Row Num, 4) = Cells(Row Num 1, 4) Then 'move attribute 2 up next to attribute 1 and delete empty line Cells(Row Num 1, 3). Screen Updating = False 'start below titles and make full selection of data Row Num = 2 Last Row = Cells. Copy Destination:=Cells(Row Num, 3) Rows(Row Num 1). Delete End If End If End With 'increase rownum for next test Row Num = Row Num 1 Next Row 'turn on screen updating Application.
Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms. Delete End If Next End Sub Sub Combine Rows Revisited Again() Dim my Cell As Range Dim last Row As Long last Row = Cells(Rows.
If you have two or more Microsoft Excel worksheets that are identical to each other (except the values are different), you can have Excel's Data Consolidate feature consolidate the worksheets into a summary report.
For example, suppose you have a workbook that consists of two worksheets.
Regarding OP's question, the data looks like have The most effective way to do this is to dump all of the data into a Pivot Table and drop 'Customer' into Row Labels and then follow up with the other columns.
Delete End If Next End Sub Sub Combine Rows Revisited Step() Dim current Row As Long Dim last Row As Long last Row = Cells(Rows. Once you have selected blanks then you can enter the same formula for all selected blank cells.