Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1 normal form
Hi, I hv an excel file which contain 2 colums, the first column is STATE and 2nd column is zipcode. The 2nd column contain a list of zipecodes seperated by ",". My question : how do I separet each of the zipcode and copy it along with column 1 to another worksheet in the same w/book? Eg : the source file = state1 | 5500, 5511, 5522 state2 | 6300, 6800 state3 | 45000 state4 | state5 | 4100, 4200 the expected file = state1 | 5500 state1 | 5511 state1 | 5522 state2 | 6300 state2 | 6800 state3 | 45000 state4 | state5 | 4100 state5 | 4200 Thanks in advance. -- swchee ------------------------------------------------------------------------ swchee's Profile: http://www.excelforum.com/member.php...o&userid=24279 View this thread: http://www.excelforum.com/showthread...hreadid=378942 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1 normal form
This worked for me--but it destroys the original worksheet. Save before you run
it and close without saving if it doesn't work: Option Explicit Sub testme01() Dim wks As Worksheet Dim newWks As Worksheet Dim ValuesToCopy As Long Dim iRow As Long Dim oRow As Long Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("sheet1") Set newWks = Worksheets.Add With wks.Range("b:b") .Cells.TextToColumns Destination:=.Columns(1), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _ Comma:=True, Space:=False, Other:=False End With With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 1 For iRow = FirstRow To LastRow ' ' newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value ' newWks.Cells(oRow, "B").Value = .Cells(oRow, "B").Value ValuesToCopy = Application.CountA(.Range(.Cells(iRow, "B"), _ .Cells(iRow, .Columns.Count))) If ValuesToCopy = 0 Then ValuesToCopy = 1 End If newWks.Cells(oRow, "A").Resize(ValuesToCopy, 1).Value _ = .Cells(iRow, "A").Value .Cells(iRow, "B").Resize(1, ValuesToCopy).Copy newWks.Cells(oRow, "B").PasteSpecial Transpose:=True oRow = oRow + ValuesToCopy Next iRow End With End Sub swchee wrote: Hi, I hv an excel file which contain 2 colums, the first column is STATE and 2nd column is zipcode. The 2nd column contain a list of zipecodes seperated by ",". My question : how do I separet each of the zipcode and copy it along with column 1 to another worksheet in the same w/book? Eg : the source file = state1 | 5500, 5511, 5522 state2 | 6300, 6800 state3 | 45000 state4 | state5 | 4100, 4200 the expected file = state1 | 5500 state1 | 5511 state1 | 5522 state2 | 6300 state2 | 6800 state3 | 45000 state4 | state5 | 4100 state5 | 4200 Thanks in advance. -- swchee ------------------------------------------------------------------------ swchee's Profile: http://www.excelforum.com/member.php...o&userid=24279 View this thread: http://www.excelforum.com/showthread...hreadid=378942 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Normal distribution | Excel Worksheet Functions | |||
normal.dot | New Users to Excel | |||
currency different from normal | Excel Discussion (Misc queries) | |||
Normal Distribution? | Excel Discussion (Misc queries) | |||
Normal Distribution | Excel Programming |