As you analyze your data in a worksheet, one common task is to look for ways to simplify the amount of data need to work with.One way to do this is to "merge" several consecutive cells together in an Excel worksheet, leaving only the sum of the original cells as a value.There are many ways to consolidate data in Microsoft Excel.You can use the Table feature and its associated filters to filter a list so it shows only those items you are interested in seeing and you can create Pivot Tables to look at your data in different ways.This master worksheet might also contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
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. There are two ways to consolidate data: by Category or by Position.Consolidation by position: When the data in the source areas is arranged in the same order and uses the same labels.I have a data dump in Excel that consists of annual customer data for two different values. I have a method which takes a second to implement but is far more understandable and very easy to adjust depending on various other possibilities.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. In the example you've given above, paste these (4) functions into the cells, E2, F2, G2 and H2, respectively (the F&G functions reference cells above): Drag these formulae as far down as necessary.Here you will select the function to analyze your data and the references or ranges that you want to consolidate.For our data we want to add the values so we'll set the Function to Sum.This tip (3026) applies to Microsoft Excel 97, 2000, 2002, and 2003.You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Merging Cells to a Single Sum.And, though not represented in the example, some clients will have no data for either value in a year (and thus no row for that customer in that year). It generates a single row of data each time 2 rows are present, leaving single rows unaffected.In that situation, lacking a row for that customer in that year is fine. Paste special the values (to remove the formulas) of columns E-F-G-H elsewhere and sort them by customer to remove all the extra rows.