Search This Blog

Monday, January 4, 2021

Combine or Append Tables | Same Workbook | Multiple Worksheets

You want to use Excel to combine tables in different worksheet tabs in the same workbook file. You also want to preserve the title in those worksheets to use for a new column value in the combined output. Luckily the tables are all structured the same and have the same column headings. The catch is that the worksheet tabs are the generic name (i.e, Sheet1 or Sheet2) and there is a common field that houses the value that categorizes the tables. One worksheet will be California data and another worksheet would be New York data. 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 this is something you get from someone else on a recurring basis, 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 https://amzn.to/2Zj9ADY

Wednesday, December 16, 2020

Google Sheets - Separate or Split Text on Comma, Space or Other Delimiter

When you get a list of values that are separated by comma, semi-colons or other delimiters, you might want to separate the values into it's own cell whether it's across columns or down the rows. In Google Sheets this video will cover two ways to do this; one with the text to column command in the file menu and the second with the SPLIT function.

Monday, December 14, 2020

Numbering Groups or Categories of Data Records

If you had data in a group or category, you might want to number them for counts or ordering. In Excel this can be done with a COUNTIF function or if it's a small table you can manually do this. However if this is something you do on a recurring basis, have to do with large tables or it's a step in part of your data cleaning process you might want to use Power Query. To number rows by grouping in Power Query uses the Index function (but it's not that complicated). It's an exercise that will partition out the categories by using the group function and then applying an index number to each of the groups. This video is short so it must not be too hard to do, so check it out.