Creating an Excel Lookup Table Sheet from a Comma Delimited and ID column

Question

We exported a customer's table who was using AirTable to keep track of their client's information and locations in an attempt to import into a SQL database. Because of the way AirTable exports, the references to other tables in their "AirTable Base" are not via ID's, but exported in a single column as basically power labels for lack of a better explanation.

There's about 4,000 client rows in this table. Clients can have one or more locations. Excluding many of the other columns it looks like:

| Client_ID | Client_Name   | ... | Locations
| 3456      | Acme Grocery  | ... | "Memphis, TN","Orlando, FL","Philadelphia, PA"
| 3457      | Addition Financial  | ... | "Miami, FL","Plano, TX","New York, NY"
| 3458      | Barros Pizza  | ... | "Queen Creek, AZ"

We are trying to get the data ready for import into SQL, so we are attempting to find a formula/method which could take the Client_ID and then insert that into rows in a new data sheet made from the comma-delimited column. Using the above example the new data should look like the following:

| ClientInLocation_ID | Client_ID | Location         |
| 10000               | 3456      | Memphis, TN      |
| 10001               | 3456      | Orlando, FL      |
| 10002               | 3456      | Philadelphia, PA |
| 10003               | 3457      | Miami, FL        |
| 10004               | 3457      | Plano, TX        |
| 10005               | 3457      | New York, NY     |
| 10006               | 3458      | Queen Creek, AZ  |

Doing so will allow us to then grab the unique locations, assign ID's to them and then replace the Location text with a Location_ID field.

I was thinking pivot tables, text to rows, etc. but perhaps I'm not experienced enough with them to pull this off. Also, any solutions can obviously exclude the ClientInLocation_ID auto increment as we could always have that autofilled once the other two fields are populated. Any help greatly appreciated.

Answer 1

There are many ways to tackle this problem. You can use PowerQuery (PQ) to do some of the lifting if you have an appropriate version of Excel. PQ is built into recently released Excel versions and is a free add-on for Excel 2013 and 2010 but is not available for anything older than Excel 2010. If you see a Power Query tab on the ribbon then you're good to go.

Use your data as the source for a new query and split the location column by delimiter "," To clarify, you are using three characters as the delimiter: the last quote of a location, the comma delimiting two locations, and the first quote of the second location. This puts one location in a cell with subsequent locations in columns to the right.

Every cell in the first column well have a quote in front of the text and the cell holding the final location for that row will have a quote at the end of the text. This is easily cleared in PQ but we're done here so it's probably faster to click Save & Load to close the editor and use Ctrl+H in Excel to clear them.

Your data will automatically be converted into a table that is connected to your source data. That means that refreshing the table does two things: it wipes any edits you've made and it updates the table with any changes in your source data. So either delete the query (if this is a one and done project) or copy the table to a new sheet (if you want to rapidly rebuild with new source data)

From there, I'd turn to VBA and use three nested For loops. The outer loop iterates every row in your data from the bottom up (Step -1). The middle loop iterates the columns to add new rows. The inner loop populates the rows.

This is quick, dirty, makes several assumptions and is in no way tested because it was written on my phone:

Option Explicit

Sub TransformTable ()
    Dim ws As Worksheet
    Dim myTable As ListObject
    Dim rng As Range
    Dim j As Long
    Dim k As Long
    Dim l as Long

    Set ws = ActiveSheet
    Set myTable = ws.ListObjects(1)

    Application.ScreenUpdating = False

    For j = myTable.ListRows.Count to 2 Step -1
        For k = 1 to Application.WorksheetFunction.CountA(ws.Range(ws.Cells(j,1),ws.Cells(j,myTable.ListColumns.Count) - 3
            Set rng = ws.Cells(j,1)
            myTable.ListRows.Add j+k
            For l = 0 to 1
                rng.Offset(k,l) = rng.Offset(0,l)
            Next l
            rng.Offset(k,3) = rng.Offset(0,3+k)
            rng.Offset(0,3+k).Cells.Clear
        Next k
    Next l

    Application.ScreenUpdating = True

End Sub