X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Data In Columns To Rows, Office 2013    
post Aug 21 2019, 02:50 PM

Posts: 219
Joined: 11-May 04


I have a problem I need help with. I have three columns and the third column has data separated by commas.

This is what is looks like:

Col 1 Col 2 Col 3
29-Aug-19 1CFG44412 C109,C455,C987

This what I need it to look like:

Col 1 Col 2 Col 3
29-Aug-19 1CFG44412 C109
29-Aug-19 1CFG44412 C455
29-Aug-19 1CFG44412 C987

I have tried Text to Column and then copy and transpose but it doesn't give this result. I have over 900 rows and many cells in Col3 have several data in them. I am looking for an easies way to move the data around with out doing it manually. I am afraid of making a mistake.

Take care,
Go to the top of the page
post Aug 21 2019, 03:11 PM

Posts: 3,331
Joined: 27-February 09

I can't do it in Excel, but I could do it in Access in a few seconds using SPLIT() and then looping through that and writing the split values to new records.

Oh, I could attach the Excel file as a linked table in Access, use SPLIT() to turn the comma-separated values to a recordset of values, and then append them to a permanent table, and then export them back to Excel. Ugly, maybe, but not hard to do.

How about Data tab> Text to Columns, select <comma space> as delimiter? Test it out on a COPY of your Excel file. No sense in messing the original data up if something goes wrong.
This post has been edited by MadPiet: Aug 21 2019, 03:59 PM
Go to the top of the page
Jeff B.
post Aug 21 2019, 03:31 PM

UtterAccess VIP
Posts: 10,301
Joined: 30-April 10
From: Pacific NorthWet

Consistency matters …

If (big IF) that third column has three (and only three) 'values', and those three are ALWAYS separated by commas, take a look at string handling functions to see if you could (first) get the three values separated into individual columns.

Then, if each 'new' record would have 'one from column A, one from column B, one from column D (the first of the three separated), E, F … you might be able to create three rows for the first record, first two columns, then use the three individual columns, one at a time.

Another possibility, if this isn't a "one-off" situation, and you'd need to do this over and over again (e.g., monthly records coming from somewhere else), you could look at writing a VBA routine to parse the incoming data …

… or link the data to Access and manipulate it there, as MadPiet mentions ...


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Aug 22 2019, 09:43 AM

Posts: 68
Joined: 18-August 16
From: Bristol, UK

Are you looking for a one off operation to change the sheet? If so try importing it into a new Excel document and it will give you an option to specify a separator. I've not tried this as I don't have sample data, but it won't affect your original file.
Go to the top of the page
post Aug 23 2019, 05:20 AM

Posts: 68
Joined: 18-August 16
From: Bristol, UK

I have managed to test this, but in Office 365 ProPlus so I'm not sure if the same or available in Office 2013. Open a new Excel document and go to data tab/get data from file, workbook. Navigate to your existing file and click import. Select the sheet and then click Transform Data. The Power Query Editor will open. Highlight your column 3 and click Split Column by delimiter. Click advanced options and select split into rows then click OK. You should see a preview of what you are looking for. Click Close & Load and job done.
Go to the top of the page
post Aug 23 2019, 07:52 AM

Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. If all else fails, this can be done quite easily in Excel with a relatively simple Macro. I made a couple of assumptions ahead of time:
    1. The From Worksheet is named Sheet1.
    2. The Data exists in Columns A, B, and C in any Worksheet.
    3. The Data is contiguous in Column A and starts at Row 1.
  2. Sample Data:
    29-Aug-19    1CFG44412      C109,C455,C987
    30-Aug-19    6BTYRE67GHT    X1234,Y564,E45555,J2345,L12990
    22-Sep-19    HNGTREE44      B12LLLUT
  3. Macro Definition:
    Public Sub ColumnsToRows()
    Dim shtFrom As Excel.Worksheet
    Dim lngRow As Long
    Dim varC As Variant     'Will hold Data in Column C
    Dim lngCtr As Long      'Will Loop thru Values in Column C

    Set shtFrom = ActiveWorkbook.Worksheets("Sheet1")

    lngRow = 1  'Start Row for Data in Sheet1

    With shtFrom
      Do While .Cells(lngRow, "A") <> ""
        varC = Split(.Cells(lngRow, "C"), ",")
          For lngCtr = LBound(varC) To UBound(varC)
            Debug.Print .Cells(lngRow, "A"), .Cells(lngRow, "B"), varC(lngCtr)
          lngRow = lngRow + 1
    End With
    End Sub
  4. OUTPUT after Macro execution:
    8/29/2019     1CFG44412     C109
    8/29/2019     1CFG44412     C455
    8/29/2019     1CFG44412     C987
    8/30/2019     6BTYRE67GHT   X1234
    8/30/2019     6BTYRE67GHT   Y564
    8/30/2019     6BTYRE67GHT   E45555
    8/30/2019     6BTYRE67GHT   J2345
    8/30/2019     6BTYRE67GHT   L12990
    9/22/2019     HNGTREE44     B12LLLUT
  5. The Results can very easily be written to another Worksheet or the same Sheet if so desired.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    17th October 2019 - 05:19 AM