2014-08-18, 15:56 #1
- Join Date
- Jun 2004
- 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!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-08-18, 16:25 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 781 Times in 715 Posts
If this is what you're after:
Then this 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