Sunday, August 15, 2021

Combine Tables with Header Values in Different Rows

Excel can be used to to combine or append tables in multiple different worksheet tabs in the same workbook file. But there may also be a value that you'd want to preserve in those worksheets to use for a new column value in the combined output. The tables are all structured the same and have the same column headings but the worksheet tabs have the generic name (i.e, Sheet1 or Sheet2). The common field in each worksheet can be used as a value that categorizes the tables. One worksheet will be California data and another worksheet would be New York data. However there's a catch...the header for those state are in different rows for each sheet. If these were small data sets, then copy/paste and some column movements would suffice. However if these are large tables, you do this on a recurring basis or you had 10 or 20 worksheets in the file, it would be a chore. This is a great opportunity to use Power Query and this video will show how. This is a tip I learned from Gil Raviv's book. Get the book at

No comments:

Post a Comment