Data Orientation in Excel 1

[Scott24x7]

Hi all,
I have a text data dump that has the following fields:

Company
Building
City, State
Country

Each listing has a blank line between it.
If I bring it into EXCEL then column A has a row for each entry.
But what I want is a sheet that has Column:
A Company B Building C City, State D Country

Is there some "easy" way I can do this, without having to create a macro button that I press over and over again to get the vertical data into horizontal data?
(Does this make sense?)
Thanks.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[mintjulep]

Paste special transpose.

 

[Andrzejek]

column A has a row for each entry" - That could be interpreted in different ways.
Do you have something like:
[tt]
1 ATT Phone Boston MA USA
2 ABCD Bikes Huston TX USA
[/tt]
or[pre][blue]
1 ATT
Phone
Boston
MA
USA[/blue][green]
2 ABCD
Bikes
Huston
TX
USA[/green][/pre]


---- Andy

There is a great need for a sarcasm font.

 

[Scott24x7]

Hi Andy, it was the second example you show. With a blank line between entries. So there are 5 lines in total.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Andrzejek]

It would be VERY helpful if you would say something like:

This is what I have now:

[pre]
A
1 ATT

Phone

Boston

MA

USA
[/pre]

And this is what I would like to have:
[pre]
A B C D E
1 ATT Phone Boston MA USA
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[Scott24x7]

Sorry, I listed the fields in the order that the data repeated in... I explained that in my OP...

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Andrzejek]

So when you said: " it was the second example you show", that was NOT the case.
Plus another confusing statement: "With a blank line between entries. So there are 5 lines in total"
So even if you data looks like this:

[pre]
A
1 ATT

Phone

Boston MA

USA
[/pre]
That still does not make it "5 lines in total".
4 lines of text plus 3 "blank line between entries" that makes 7 lines total.

I will stop now and let somebody else figure out exactly what you have. :-(


---- Andy

There is a great need for a sarcasm font.

 

[Scott24x7]

I stated:

Company
Building
City, State
Country

Each listing has a blank line between it.

4 fieds, then a blink line between the entreis... sorry you don't understand that. And this isn't at all helpful.

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Andrzejek]

I try to be helpful. In all my responses what I am trying to do is to say: Don't explain what you have, show the example of what you have. Either as a sample data presented straight in the post, or attach a workbook with your data.
Solution is probably 5 minutes away with some simple piece of code, but it all depends what you have.
I know it is obvious to you, but - as you can see - can be very ambiguous to others (or is it just me, but I don't see anybody else jump in). So help me so I can help you. I am willing...


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

OK, assuming you have in cell A1:
Company
Building
City, State
Country
empty line

and in cell A2:
Another Company
Building X
Some City, State
Country
empty line

And so on, and there are no empty rows in column A until the end of the data.
And you want to have your data in Columns C, D, E, and F

Place this code in a standard module and run it:

Option Explicit

Sub DoIt()
Dim r As Integer
Dim c As Integer
Dim ary() As String
Dim x As Integer

r = 1

Do While Range("A" & r).Value <> ""
    ary = Split(Range("A" & r).Value, Chr(10))
    c = 3
    For x = LBound(ary) To UBound(ary)
        Cells(r, c) = ary(x)
        c = c + 1
    Next x
    r = r + 1
Loop
MsgBox "I am Done :-)"

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

1 Select all data

2. Turn on filter

3 Check BLANKS only and Filter

4 Delete all ROWS in data

5 Remove filter

6 Copy all data

7 Select cell Target

8 Right-click PASTE-TRANSPOSE in Target cell

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]