Reformat data from 3 columns to 9 columns worksheet

D

drquickbooks

I would like to expand data from the above 3 columns worksheet to th
below 9 columns worksheet.
There are 338 names total on this database. I had rearranged about 3
names manually.
300 more to go : )
My boss said that there must be a smarter way. Would you agree wit
her. Can you help me ?
Thanks - John Burton

'View of 3 columns and 9 columns worksheet
(http://www.ezpayroll.net/excel_reformat.htm
 
W

whisperer

The following code will do your conversion for you based on th
following assumptions.

The existing data is in columns A through to C and occupies three row
for each entry.

The transcribed data will start at column E row 2 and continue acros
to column M.

Make a trial run on a backup copy first, although I am quite happy i
will work :)

Just paste the code into a module in the workbook



Code
-------------------

Sub SplitDb()
Dim iRow As Integer
Dim Id As Byte

iRow = 2
Id = 1
Do Until Cells(iRow, 1) = ""
Cells(iRow, 5) = Id
Cells(iRow, 6) = _
Left(Cells(iRow, 1), Application.Find(",", Cells(iRow, 1), 1) - 1)
Cells(iRow, 7) = Right(Cells(iRow, 1), _
Len(Cells(iRow, 1)) - Application.Find(" ", Cells(iRow, 1), 1))
Cells(iRow, 8) = Right(Cells(iRow, 2), Len(Cells(iRow, 2)) _
- Application.Find(" ", Cells(iRow, 2), 1))
Cells(iRow, 9) = Right(Cells(iRow + 1, 2), Len(Cells(iRow + 1, 2)) _
- Application.Find(" ", Cells(iRow + 1, 2), 1))
Cells(iRow, 10) = Cells(iRow, 3)
Cells(iRow, 11) = Cells(iRow + 1, 3)
Cells(iRow, 12) = _
Left(Cells(iRow + 2, 3), Application.Find(",", Cells(iRow + 2, 3), 1) - 1)
Cells(iRow, 13) = Right(Cells(iRow + 2, 3), 8)
Rows(iRow + 1).Select
Selection.Delete Shift:=xlUp
Rows(iRow + 1).Select
Selection.Delete Shift:=xlUp
iRow = iRow + 1
Id = Id + 1
Loop
End Sub
 
M

macropod

Hi,
Try this:

1. Insert the number of required extra columns after the first two columns
of your existing data.
2. For the first column, select the cells to be split, then use the
Data|Text to Columns feature to split the cells using the comma as the
delimiter.
For the second & third columns, It appears as if you've got wrapped text in
each cell, or that you've used multiple rows for the data. The treatment
varies, depending on which.
For wrapped text:
3. Select the cells to be split, then use the Data|Text to Columns feature
to split the cells using 'other' and typing Alt-010 in the space provided as
the delimiter.
For multiple rows of data:
4. Use a formula to refer to each row, as required. For example, say you're
in cell D3 and you need to have the data from cell C4 appear there, and for
the data in C5 to appear in E3. Simply put the formula =C4 in D3 and =C5 in
E3.
5. Repeat 4 until all rows are done - you should be able to copy the
formulae in the 'target' columns down as far as you need.
6. Select the 'target' columns and copy the data, then use Edit|Paste
Special|Values to replace the formulae with their results.
7. Delete the unwanted rows - the ones with no client names.
For both cases:
8. You will need to repeat step 2 on the last column of data.
9. Use Search/Replace to delete the unwanted 'Office: ', 'Fax: ' & 'CA '
strings.

Cheers
 
D

drquickbooks

Just came back from a weekend camping trip, your excellent replies too
away all my tiredmesses... Thank you ! Thank you ! Thank you

I will start to follow your instructions right away.......

And let you know the results


Joh
 
D

drquickbooks

I followed your excellent instructions first because it matched m
background. Despite of my inexperience in Excel, I am done : )
The task was completed !

I will look like a genius tomorrow !

Thank you ! Thank you ! Thank you

Joh
 
D

drquickbooks

I want to thank you so much for taking your time to write the code fo
me. I am sorry that I am still not able to use your code. I am a "rea
novice" in Excel. And I would like to learn how to use your code fo
the next assignment.

Thanks again for your code. I hope that I will be able to use it soon

Cheers : )

Joh
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top