Solved

How to convert Excel list with commas to Excel list without commas?

Posted on 2014-09-28
8
124 Views
Last Modified: 2014-10-04
I have a list in an Excel file where each row has a column A and a column B.

Here are some example rows in the tab Input from the attached file:
0x0      Hair, Football, Cats, Bees
1x01      Cats, Michael Jackson, Beards, Coffee
1x02      Human Body, Coca-Cola, Morris Dancing, Carrots
1x03      Chickens, London Underground, Queen Elizabeth I, Ancient Egyptians
1x04      George W. Bush, Women, Ants, Olympic Games

Input Data
A new tab should be created as Output with the data converted to:
0x0      Hair
0x0      Football
0x0      Cats
0x0      Bees
1x01      Cats
1x01      Michael Jackson
1x01      Beards
1x01      Coffee

Output DataCreateList.xlsm
0
Comment
Question by:Alex972
  • 4
  • 3
8 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40348898
The following VBA subroutine will parse and transpose your data into two columns as requested:
Option Explicit
Sub Parse_and_Transpose()
    Dim rng As Range
    Dim cl As Object
    Dim arrItems As Variant
    Dim strCat As String
    Dim x As Integer
    Dim r As Long
    
    'Remove any existing Output sheet first
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Output").Delete
    
    Sheets.Add After:=Sheets("Input")
    ActiveSheet.Name = "Output"
    Sheets("Input").Select
    
    Set rng = Range("A1", Range("A1").End(xlDown))
    r = 1
    For Each cl In rng
        strCat = cl.Value
        arrItems = Split(cl.Offset(0, 1).Value, ", ")
        For x = 0 To UBound(arrItems)
            Sheets("Output").Cells(r, 1).Value = strCat
            Sheets("Output").Cells(r, 2).Value = arrItems(x)
            r = r + 1
        Next x
        arrItems = Null
    Next cl
    Application.DisplayAlerts = True
    MsgBox "Done."
End Sub

Open in new window


This presumes there are no blank cells in column A.

Example macro-enabled file is attached.

Regards,
-Glenn
EE-CreateList.xlsm
0
 
LVL 1

Author Closing Comment

by:Alex972
ID: 40349108
Exactly what I was looking for.
0
 

Expert Comment

by:Berry Metzger
ID: 40353638
@Alex972
I believe your formulas in F17:F22 are incorrect. The first two formulas are identical as follows...
F17: =D17*VLOOKUP(E17,$A$5:$D$13,3,TRUE)
F18: =D17*VLOOKUP(E17,$A$5:$D$13,3,TRUE)  where it should be: =D17*VLOOKUP(E18,$A$5:$D$13,3,TRUE)
...and so the one-row reference mistake continues for the remainder of the range.
Berry
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:Alex972
ID: 40360471
Thanks for the notificatios, but I not sure what file this is in regard to. The data file I uploaded doesn't have any formulas.
0
 

Expert Comment

by:Berry Metzger
ID: 40360742
It is in reference to your Commission-split Comparison-EDIT-UPDATE.xlsx file that Glenn Ray assisted you with.
0
 
LVL 1

Author Comment

by:Alex972
ID: 40360821
What question is that?
0
 

Expert Comment

by:Berry Metzger
ID: 40361173
Ahaaa! it was not your question, it was by  DEROUCHE solved on 9/30/14.  Don't know how your name got referenced.
Berry
0
 
LVL 1

Author Comment

by:Alex972
ID: 40361207
Ah... all has been revealed. 8^)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
populate  some cells after data verification 45 26
Clear a Text Box 7 28
simple excel formula 4 18
Excel 2010 Pivot Table Calculated Fields 2 15
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question