They don't give me the label in A1, they don't bother to sort the data going down, and if someone didn't have a record in the first one, they give me blanks instead of zeroes.Alright, to fill the blanks with zeroes, Home, Find and Select, Go To Special, choose the Blanks, click OK, type a zero, Ctrl Enter will fill those in.
Alright hey, I want to thank for stopping by, we'll see you next time for another netcast from Mr Excel!
I got a call from a friend who wanted to combine multiple Excel files into one Excel workbook.
Tomorrow we're going to compare the multiple consolidation range, Pivot tables, but all of these tricks are in the book, click the “i” on the top-right hand corner to get to that book.
Consolidate, ancient, ancient feature in Excel, you specify multiple ranges to consolidate, I always check the box for top row and left column.
I have three datasets here, Q1, Q2, Q3, they all have a similar shape, and that they have names down the left hand side, months going across the top, but not exactly the same shape.
Q1 has Jan-Feb-Mar, Q2 has 5 months, I guess we got lazy and didn't figure out to do this at the end of June and someone finally did it at the end of August, and then Q4 has 4 months.
Alright, check this box for Use labels in the Top row and the Left column, that Browse button means that these data sets can be in different workbooks!
Create links to source data, we're going to talk about that one at the end.
Notice that the names are different, and there are a different number of months in each data set. If a person was missing from a data set, then cells are left empty instead of being filled with 0. Sorting by name involves using Flash Fill to get the last name in column N.
The first tool is the Consolidate command on the Data tab. Here is how to fill blank cells with 0: Learn Excel from Mr Excel podcast, episode 2046 - Consolidate Worksheets!
Alright so, the 1st one goes A: D, 2nd one A: F, 3rd one A: E.