Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting single comma-separated row into multiple rows (Access 2010)

    If anyone could provide advice on how to solve my dilemma, that would be terrific.

    I have a table in Access 2010 that contains a comma delimited list of zip codes in a single cell by company#. This list is provided to my database from a different system and the list is unable to be modified. I would like to create a new table with the list where the single comma separated rows are converted into multiple rows with 1 zip code for each company#.

    Attached is this post is a sample of the data I am using. The table is called tblListingsAndZips.

    I would like to keep my original table and create a new table with a single zip code listed for each company#.

    Any help would be much appreciated!
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 783 Times in 717 Posts
    kgudgel,

    If this is what you're after:
    zips.JPG
    Then this code:
    Code:
    Option Explicit
    
    Sub ProcessZips()
    
       Dim vRawZips  As Variant
       Dim iZipCnt   As Integer
       Dim iCntr     As Integer
       Dim lDestRow  As Integer
       Dim zCCode    As String
       Dim zDestSht  As String
       
       Application.ScreenUpdating = False
       [C2].Select
       lDestRow = 2
       zDestSht = "NewList"
       Sheets(zDestSht).Cells(1, 1).Value = "Company#"
       Sheets(zDestSht).Cells(1, 2).Value = "ZipCode"
       
       Do
         ActiveCell.Offset(1, 0).Select
         
         vRawZips = Split(ActiveCell.Offset(0, -1).Value, ",")
         zCCode = ActiveCell.Value
         iZipCnt = UBound(vRawZips) - 1  '*** Array zero based!
         
         For iCntr = 0 To iZipCnt
            Sheets(zDestSht).Cells(lDestRow, 1).Value = zCCode
            Sheets(zDestSht).Cells(lDestRow, 2).Value = vRawZips(iCntr)
            lDestRow = lDestRow + 1
         Next iCntr
         
       Loop Until ActiveCell.Value = ""
       
    End Sub   'Process Zips
    Will accomplish the task in Excel and you can then import the table into Access. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •