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!
Subscribe to our Windows Secrets Newsletter - It's Free!
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 765 Times in 699 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