JimGunther
Board Regular
- Joined
- Oct 22, 2006
- Messages
- 57
I seem to have lost the ability to post an Excel sheet...so I've appended a few rows of data to illustrate.
ASAP Untilities "almost" solves my issue but, not quite.
In any event, I have a single column of data (over 100,000 lines) that I'd like to transpose into "roughly" 11 columns or fields yielding almost 9,000 records. Unfortunately, the data doesn't repeat EXACTLY. (.i.e. where there is no company or state listed, it may show only 9 or 10 rows)
The "good" news, the second and third line in each record is consistent. That is, line 1 will always be the contact name (obviously, unique), every second line of each record begins with "add_", every third line is "Send Message". Using line 2 and line 3 as an "anchor" is there a way to the data consistently - the remaining lines are Company (if any) Title (if any), City (if any), State(if any), Country(if any)
To anticipate a question - therer is the dotted line between only 8500 of the 9,000 or so records and no apparent pattern.
Suggestians? TIA
Jones, Robert
Add Robert as contact
Send Message
AXZ Comopany
Administrative Services Director
Zerly
, UT
, United States
--------------------------------------------------------------------------------
Esssel, Carla
Add Carla as contact
Send Message
Aphap Ind
AVP, Buig Shot
Pittsburgh
, PA
, United States
--------------------------------------------------------------------------------
Roberts, Doug
Add Doug as contact
Send Message
Parliament Motors
Executive Director, Insurance
Parumph
, NV
, United States
--------------------------------------------------------------------------------
Jelloman, Brigid
Add Brigid as contact
Send Message
Yoo-Hoo Corp
State Director
Las Vegas
, NV
, United States
Fong, Xiaoying
Add Xiaoying as contact
Send Message
U Cal Berkley
Assistant professor
Oakland
, CA
, United States
--------------------------------------------------------------------------------
Yackovic, Yiran
Add Yiran as contact
Send Message
University Hartford
Student
Hartford
, CT
, United States
Bronson, Anthony
Add Anthony as contact
Send Message
Gas and Go
Filling Manager
Houston
, TX
, United States
--------------------------------------------------------------------------------
Gallo, Keith
Add Keith as contact
Send Message
Roundabout Metals
Management Trainee
Pittsburgh
, PA
, United States
--------------------------------------------------------------------------------
Blakeley, Hisao
Add Hisao as contact
Send Message
Anyword Corp
Manager
Tokyo
, Japan
--------------------------------------------------------------------------------
Aori, Terry
Add Terry as contact
Send Message
Nestle Cookies
Director, Comings and Goings
Dallas
, TX
, United States
--------------------------------------------------------------------------------
Brownstein, Tadashi
Add Tadashi as contact
Send Message
Lottomania
Bingo Director, Director
Kyoto
, Japan
--------------------------------------------------------------------------------
Yamamoto, Masashi
Add Masashi as contact
Send Message
NKSJ Risk Management, Inc.
Director
Tokyo
, Japan
--------------------------------------------------------------------------------
ASAP Untilities "almost" solves my issue but, not quite.
In any event, I have a single column of data (over 100,000 lines) that I'd like to transpose into "roughly" 11 columns or fields yielding almost 9,000 records. Unfortunately, the data doesn't repeat EXACTLY. (.i.e. where there is no company or state listed, it may show only 9 or 10 rows)
The "good" news, the second and third line in each record is consistent. That is, line 1 will always be the contact name (obviously, unique), every second line of each record begins with "add_", every third line is "Send Message". Using line 2 and line 3 as an "anchor" is there a way to the data consistently - the remaining lines are Company (if any) Title (if any), City (if any), State(if any), Country(if any)
To anticipate a question - therer is the dotted line between only 8500 of the 9,000 or so records and no apparent pattern.
Suggestians? TIA
Jones, Robert
Add Robert as contact
Send Message
AXZ Comopany
Administrative Services Director
Zerly
, UT
, United States
--------------------------------------------------------------------------------
Esssel, Carla
Add Carla as contact
Send Message
Aphap Ind
AVP, Buig Shot
Pittsburgh
, PA
, United States
--------------------------------------------------------------------------------
Roberts, Doug
Add Doug as contact
Send Message
Parliament Motors
Executive Director, Insurance
Parumph
, NV
, United States
--------------------------------------------------------------------------------
Jelloman, Brigid
Add Brigid as contact
Send Message
Yoo-Hoo Corp
State Director
Las Vegas
, NV
, United States
Fong, Xiaoying
Add Xiaoying as contact
Send Message
U Cal Berkley
Assistant professor
Oakland
, CA
, United States
--------------------------------------------------------------------------------
Yackovic, Yiran
Add Yiran as contact
Send Message
University Hartford
Student
Hartford
, CT
, United States
Bronson, Anthony
Add Anthony as contact
Send Message
Gas and Go
Filling Manager
Houston
, TX
, United States
--------------------------------------------------------------------------------
Gallo, Keith
Add Keith as contact
Send Message
Roundabout Metals
Management Trainee
Pittsburgh
, PA
, United States
--------------------------------------------------------------------------------
Blakeley, Hisao
Add Hisao as contact
Send Message
Anyword Corp
Manager
Tokyo
, Japan
--------------------------------------------------------------------------------
Aori, Terry
Add Terry as contact
Send Message
Nestle Cookies
Director, Comings and Goings
Dallas
, TX
, United States
--------------------------------------------------------------------------------
Brownstein, Tadashi
Add Tadashi as contact
Send Message
Lottomania
Bingo Director, Director
Kyoto
, Japan
--------------------------------------------------------------------------------
Yamamoto, Masashi
Add Masashi as contact
Send Message
NKSJ Risk Management, Inc.
Director
Tokyo
, Japan
--------------------------------------------------------------------------------