Transposing Data -Rows not QUITE identical

[JimGunther]

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

--------------------------------------------------------------------------------

 

[Mohammad Basem]

To post a file, upload the file to any file hosting site (4shared.com, hotfile.com, rapidshare.com, etc.) and post the link.

Do you want to ensure consistency of transposed data, i.e. one column for each of names, companies, titles, cities, countries, etc.?

You have mentioned that there are 9-10 rows per record (information block of one person), while the sample (looks somehow consistent) shows 2 categories; one for those in the US (8 data lines) and the other for those in other countries (7 data lines). Are you considering blank lines and you want to allocate columns for them as well?

In principle, it is possible to transpose the rows into columns. But, to ensure consistency you have to come up with clear criteria, especially you did not confirm that all information will be available; Company, Title, City, State and Country.

 

[John Davis]

This probably doesn't get it, but based on you sample may get you started. At least I hope it gives you some ideas.

Sub JimGunther()
Dim i As Integer
Dim rcell As Range
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For Each rcell In Range("A2:A" & lr)

    If Right(rcell, 5) = "Japan" And rcell.Offset(-1) = "Tokyo" Or rcell.Offset(-1) = "Kyoto" Then
    
        
        rcell.Offset(1).Value = rcell.Value
        rcell.Offset(2).Insert SHIFT:=xlDown
        
    End If
    
Next rcell

Do While Range("A2") <> ""

Range("A2:A11").Copy

Range("B" & Rows.Count).End(3)(2).PasteSpecial Transpose:=True

Range("A2:A11").Delete SHIFT:=xlUp

Loop

Columns("A:A").Delete SHIFT:=xlToLeft

Cells.Columns.AutoFit

End Sub

 

[JimGunther]

Mohammad and John,

My apologies for not updating sooner.

Mohammad - unfortunately the rows are only ALMOST consistent. While the example I posted, shows a variation due to Country (and no city or state), most of the variations are due to ?No Company name" and / or "No title"
The Only consistent pattern is that the unique contact name should always be line#1;
The add "first name" will be position #2 and
"Send Messgae" will always be line #3
Line #4 is "blank
the following MIGHT vary
Line #5 (if there IS a line five) should be "Company"
Line #6 SHOULD be "Title"
Line #7 SHOULD be "City"
Line #8 SHOULD be "State"
Line #9 SHOULD be "Country"
Line #10 SHOULD be "blank"
Line # 11 USUALLY (but BLANK maybe 5% of the time) -------------

Unfortunately, if there is no Company or title or the location is non US - it breaks the pattern.

I was HOPING a routine could be written to find "Add_" then go up one space or find "Send Message" and go up one space to start the next record.
While some of the records might be goofy if the Company or title or state is missing, I could manually identify (and edit) the exceptions.

Thoughts?
------------
John, I'll try your Macro over the weekend.

Fingers crossed and Thanks again,
Jim

 

[JimGunther]

O.K - I took the "clunky & old fashioned" way out.

I concluded that some editing was going to be required no matter how slick the macro might be (Sorry John I never ran it.).

So, I inserted a Column A and repeated all 11 "labels" in Column A - all the way through almost 100,000 lines.

I then went screen by screen (ca 20 lines at a time) identifying exceptions (i.e. where Title , or State, or Company, (or combination of any) was missing) and would manually insert a - causing the following entries to "line-up" properly until the next exception was found.
This painstaking edit took, perhaps, 6 to 10 total hours to attain perfect alignment of Column a "labels" with the appropriate "field infotrmation" in Column B.

I then, deleted Column A, then, used the ASAP Utility to TRANSPOSE into 11 columns.

There errant exceptions still remaining were easily identified by simply scrolling through the aprox 9,000 lines (by 11 colums wide) and easily "fixed.

Given the hours I've agonized over this project (recently and when I did it in some manner in the past) the hours spent over the holiday weekend were, probably, time efficient and well invested.

Doesn't mean I won't seek a more automated way to fix this in the future but, at the very least, I ought to be able to replicate this faily simple, if clunky method, in the future.

Thanks for your help anyway!