Splitting Data for Values in Specific Fields

[EnginerdUNH]

Hi,

I am working on pulling data into a temporary table before it gets split off and added to the corresponding main table and associated sub tables. The issue that I am running into as you'll see in the attached excel file that the data gets pulled in to the temporary table from, some but not all of the rows have a couple of fields which contain multiple "rows" of data as denoted by a "space + carriage return" in the cells that I've highlighted. The data for these highlighted cells will be stored in their own separate tables apart from the main table. What I am looking to be able to do is break the data up as I've shown a couple rows down on the example file attached and have the data listed in tblMainInfo (all columns except for columns B, C, J & K) be unique but columns B & C and J & K can list the same VIRNumber or VIRID in its corresponding table but the data which in the excel file is separated by "space + carriage return" will be its own separate row of data. Right now, the only way I can get the data separated by "space + carriage return" to be their own rows in the corresponding tables is to manually go in after I've imported the data and separate it myself.

[HansV]

Do you want to do this in Excel or in Access?

[EnginerdUNH]

Hi Hans, either in excel or in access is fine, whichever you think will be easiest for the database user to understand what to do

[HansV]

Here is a macro that can be run on the data in Excel:

Code: Select all

Sub SplitData()
    Dim r As Long
    Dim m As Long
    Dim a2() As String
    Dim a3() As String
    Dim a10() As String
    Dim a11() As String
    Dim n As Long
    Dim i As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
        n = 0
        a2 = Split(Cells(r, 2).Value, " " & vbLf)
        n = UBound(a2)
        If n > 0 Then
            a3 = Split(Cells(r, 3).Value, " " & vbLf)
            a10 = Split(Cells(r, 10).Value, " " & vbLf)
            a11 = Split(Cells(r, 11).Value, " " & vbLf)
            For i = n To 1 Step -1
                Cells(r, 1).EntireRow.Copy
                Cells(r + 1, 1).EntireRow.Insert
                Application.CutCopyMode = False
                Cells(r + 1, 2).Value = a2(i)
                Cells(r + 1, 3).Value = a3(i)
                Cells(r + 1, 10).Value = a10(i)
                Cells(r + 1, 11).Value = a11(i)
            Next i
            Cells(r, 2).Value = a2(0)
            Cells(r, 3).Value = a3(0)
            Cells(r, 10).Value = a10(0)
            Cells(r, 11).Value = a11(0)
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

[EnginerdUNH]

Hi Hans, thanks for the example macro! Quick follow-up question for you though. I should have specified in my original post that the highlighted columns can vary in that in a given row, columns B/C may contain one or more “space + carriage return” but columns J/K may not and vice versa. How can I modify the code to account for this?

[HansV]

I hope that this catches all situations:

Code: Select all

Sub SplitData()
    Dim r As Long
    Dim m As Long
    Dim a2() As String
    Dim a3() As String
    Dim a10() As String
    Dim a11() As String
    Dim n As Long
    Dim n2 As Long
    Dim n3 As Long
    Dim n10 As Long
    Dim n11 As Long
    Dim i As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
        n = 0
        a2 = Split(Cells(r, 2).Value, " " & vbLf)
        a3 = Split(Cells(r, 3).Value, " " & vbLf)
        a10 = Split(Cells(r, 10).Value, " " & vbLf)
        a11 = Split(Cells(r, 11).Value, " " & vbLf)
        n2 = UBound(a2)
        n3 = UBound(a3)
        n10 = UBound(a10)
        n11 = UBound(a11)
        n = Application.Max(n2, n3, n10, n11)
        If n > 0 Then
            For i = n To 1 Step -1
                Cells(r, 1).EntireRow.Copy
                Cells(r + 1, 1).EntireRow.Insert
                Application.CutCopyMode = False
                Cells(r + 1, 2).Value = a2(Application.Min(i, n2))
                Cells(r + 1, 3).Value = a3(Application.Min(i, n3))
                Cells(r + 1, 10).Value = a10(Application.Min(i, n10))
                Cells(r + 1, 11).Value = a11(Application.Min(i, n11))
            Next i
            Cells(r, 2).Value = a2(0)
            Cells(r, 3).Value = a3(0)
            Cells(r, 10).Value = a10(0)
            Cells(r, 11).Value = a11(0)
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

[EnginerdUNH]

Hi Hans,

Yes that appears to catch everything. I just have one last question. When I pull in the data to my temporary table, I’d there a specific way that I need to structure my queries for the sub tables so that I don’t end up with duplicates for the rows where there’s no additional lines for columns B/C but there is for columns J/K and vice versa? Would I pull everything in as it is and then run a delete query to remove duplicates?

[HansV]

You might link to the Excel data, and create an append query with Unique Values set to Yes (in the query's SQL this is represented by SELECT DISTINCT instead of just SELECT).

[EnginerdUNH]

Hi Hans,

I don't think that linking to an excel file will work because each person using the database will be uploading new data using an excel file stored locally on their computer. I don't think I'll be able to use an append query with Unique Values set to Yes either because the same drawing or PO may apply to more than one VIRNumber and each VIRNumber will need to be repeated for every drawing or PO listed tied to that VIRNumber. I have attached an updated example excel file of my data now that it has been broken out using the macro you provided.

[HansV]

So what should the result be?

[EnginerdUNH]

Hopefully I'm understanding your question correctly. In the main table, which contains everything except columns B, C, J & K, everything should only be listed once. In the table for all the associated drawings and PNs, the VIRNumber can be listed more than once but a drawing and its associated PNs should only be listed once for the same VIR and similarly for the PO/POLIs. I have attached a new spreadsheet which shows the following:
Columns A-O: the original data
Columns Q-AA: the structure of the main table
Columns AC-AE: the structure of the drawing/PN table
Columns AG-AI: the structure of the PO/POLI table

Hopefully that makes sense to you but please let me know if you need more information.

[HansV]

Oh dear, that looks frightfully complicated. I'll have to think about it.

[HansV]

Could you post the source of your last attachment (i.e. with multi-line cells)?

[EnginerdUNH]

I'm not quite sure what you mean by the source of my last attachment but I've re-attached the original data that I started with to this post

[HansV]

The VIRListImport_TableStructure.xlsm workbook has VIRNumbers VIR0001 to VIR0007, but the workbook that you attached now has only VIR0001 to VIR0006.

[EnginerdUNH]

My apologies, please see attached.

[HansV]

Yes, that's the one I wanted. I'll have a look.

[HansV]

Question: why does cell C10 in the VisListImport_TableStructure.xlsm workbook contain "10, 11, 16"? I don't see that value in VIRListImport.xlsm.

Try the macro in the attached version. It will create 3 new sheets that can be imported into Access.

VIRListImport2.xlsm

[EnginerdUNH]

Hi Hans, my apologies for not getting back to you sooner, I’m just getting back from being on vacation for a week. I had a chance to test out your macros and the file works exactly as expected and everything is pulled in as I need it using the code that I have to pull in the data from the file.

As for your question, it’s possible that I changed some of the values for something else I was testing and didn’t realize it when I uploaded both files. In either case, the data I gave you is just dummy data to make sure any macros and such that we came up with were working as expected and the two files datasets don’t necessarily have to match. Hope this helps.