UtterAccess.com
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    
 
   
leopold
post Aug 21 2019, 02:50 PM
Post#1



Posts: 219
Joined: 11-May 04



Hello,

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,
Leopold
Go to the top of the page
 
MadPiet
post Aug 21 2019, 03:11 PM
Post#2



Posts: 3,333
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
Post#3


UtterAccess VIP
Posts: 10,305
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 ...

--------------------
Regards

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
 
Vince
post Aug 22 2019, 09:43 AM
Post#4



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
 
Vince
post Aug 23 2019, 05:20 AM
Post#5



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
 
ADezii
post Aug 23 2019, 07:52 AM
Post#6



Posts: 2,679
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:
    CODE
    29-Aug-19    1CFG44412      C109,C455,C987
    30-Aug-19    6BTYRE67GHT    X1234,Y564,E45555,J2345,L12990
    22-Sep-19    HNGTREE44      B12LLLUT
  3. Macro Definition:
    CODE
    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)
          Next
          lngRow = lngRow + 1
      Loop
    End With
    End Sub
  4. OUTPUT after Macro execution:
    CODE
    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    20th October 2019 - 01:58 PM