Programitically Transpose Data and Parse it

R

Rashid Khan

Hello All Experts,

I have several thousand rows of data in the following format in Column A
with sample data shown under each line. I am using Office XP.

1 Name MiddleName LastName withou commas
John Henry Joseph
2 Address line1 separated by commas
705, Heritage Avenue,
3 Address line2 separated by commas
Hill Street, Mount Area,
4 City Name with pincode separated by '-'
Delhi-12345
5 Telephone for Office and Residence separated by '-'
O-3456789, R-4567890
6 Mobile No and Fax No.
Mobile: 98200 12345 Fax: 09122 334567
6 Email Address
Email: (e-mail address removed)
7 DOB and Blood Group
DOB: Jan 13 B.G: B+ve

I need to achieve the results on Sheet2 as follows:
as per the example data mentioned above.
Sheet2
A1: John
B1: Henry
C1: Joseph

D1: 705,
E1: Heritage Avenue,

F1: Hill Street,
G1: Mount Area,

H1: London
I1: 12345

J1: Mobile: 98200 12345
K1: Fax: 09122 334567

L1: Email: (e-mail address removed)

M1: DOB: Jan 13
N1: B.G: B+ver

Pls note that the data is in standard group of Seven Rows each separated by
a blank row .... running down to thousand of rows.

Can this be achieved.. At present I am doing manual copy/paste which is very
very time consming.

Any help or better ideas, suggestion to achieve the desired results would be
very much appreciated

Thanks to all in advance

Rashid Khan
 
G

George Stevenson

I would submit that you don't want to continue the problem of having fields
that are related to a single record stored horizontally as multiple rows.
You need to get all of your data for 1 record into a single row that you can
treat like a database and parse it into as many constiuent parts as you
need, but all stored as different columns on 1 row.

You'll need to write some code that will traverse your 7 rows of data for
each record, and put it into columns on a single row in a 2nd worksheet.
You can do all of that under program control.

Here is an example of how to find the last row in a spreadsheet. You would
need something like this to get started being able to move through your 7
rows of data for each record. You can remove the ' in the '
CurrentCell.Activate statement to have the routine highlight each cell that
it works on as it moves through the data. Nice for debugging, but it slows
down the processing if you are crunching lots of rows.

This should help get you started.

sub TestLastRow()
dim mylastrow as integer
Range("D1").Select
myLastRow = FindLastRow()
end sub

Public Function FindLastRow()
Set currentcell = ActiveCell
Dim MyRow
MyRow = 0
' Look for 3 consecutive rows of empty cells
Do Until IsEmpty(currentcell) And IsEmpty(currentcell.Offset(1, 0)) And
IsEmpty(currentcell.Offset(2, 0))
Set currentcell = currentcell.Offset(1, 0)
MyRow = currentcell.Row
' CurrentCell.Activate
Loop
FindLastRow = MyRow - 1
End Function
 
R

Rashid Khan

Hello George,
Thanks for your reply but pardon me for my ignorance. I could not get what
u r trying to suggest. My knowledge about VBA for that matter is very
minimal.. The NG is a huge dome under which many experts like u are there to
lend out a helping hand. Uptil now I have been posting on the NG with my
problems and someone or other came up with a solution which I could just
copy/paste it as a code and run it. That is as far as my knowledge goes.

Yes u r right that I do not want to my fields into row wise and not column
wise. But thats how I dont know what to do..

I visited Greg Ritchie's Website but could not find anything to suit my
problem in a single go. The document I have is from an OCR software and
thats why I am having double data in single Column eg DOB and BG on a single
line and Mobile and Fax on a single line..

I hope u can suggest something to me now.

Rashid Khan
 
G

George Stevenson

What you are asking for is that have someone write the code for you. That
is consulting which you should be paying for.
If you are going to write it yourself, then you need help to get over the
hurdles, but you need to expend a fair amount of research effort yourself.
That is the only way to get better at programming in Excel. Try a few
things.

What I posted was an example of how you put a program together that can
traverse rows of data and know when to stop.

Sorry I can't be of more help beyond this.
 
R

Rashid Khan

Ok. Thanks for the reply
Rashid Khan
George Stevenson said:
What you are asking for is that have someone write the code for you. That
is consulting which you should be paying for.
If you are going to write it yourself, then you need help to get over the
hurdles, but you need to expend a fair amount of research effort yourself.
That is the only way to get better at programming in Excel. Try a few
things.

What I posted was an example of how you put a program together that can
traverse rows of data and know when to stop.

Sorry I can't be of more help beyond this.


there your Column
Click to expand...
 
Top