Transpose Address Data

[H J]

Is there any way to transpose a range of data automatically? I know about
using the transpose function in Excel, but to use it I need to manually
select what I want to transpose. I have about 13000 rows that I want to
transpose (end result would be about 1000 rows of address details).

The biggest problem I have is that I can't find any thing in the list of
data that highlights where I would want to start a new range. It also looks
like not everything is a set number of rows, so I can't even transpose every
10 rows.

Is there anything I can do other than select, copy, paste special,
transpose, repeat?

Thanks.

 

[Gary''s Student]

Is there at least a blank row between your records??

 

[Pete_UK]

Post a few examples of your data. It might be that the last line of
the address is always a postcode, or that the first line always begins
with Mr, so it might be possible to latch on to these.

Hope this helps.

Pete

 

[ala]

Row to column -------------INDIRECT("R"&ROW($A$1)&"c"&COLUMN(A1)+ROW
(A1)-1,FALSE)

column to row -------------INDIRECT("R"&ROW(E4)+COLUMN(A1)-1&"c"&COLUMN
($E$4),FALSE)

 

[H J]

I think that there is a blank row, the reason I say think is because there
is some extra data in columns B and C that are part of the same set, I am
only interested in what is in column A.

But, I think if I take what is in columns B and C and move it into column A,
then I will be left with a blank row after each set of data. I can then go
back and remove the data I don't want from each row.

So, yes, at some point I will have a blank row.

 

[H J]

Ok, it looks like I have 2 blank rows after each record.

 

[Leonel Hernandez]

There is an Excel Add-In named Report Inverter which helps you to transpose Address Data in Excel. You can download the free trial version in www.databiliosuite.com



H J wrote:

Transpose Address Data
05-Jan-09

Is there any way to transpose a range of data automatically? I know about
using the transpose function in Excel, but to use it I need to manually
select what I want to transpose. I have about 13000 rows that I want to
transpose (end result would be about 1000 rows of address details).

The biggest problem I have is that I can't find any thing in the list of
data that highlights where I would want to start a new range. It also looks
like not everything is a set number of rows, so I can't even transpose every
10 rows.

Is there anything I can do other than select, copy, paste special,
transpose, repeat?

Thanks.

Previous Posts In This Thread:

Transpose Address Data
Is there any way to transpose a range of data automatically? I know about
using the transpose function in Excel, but to use it I need to manually
select what I want to transpose. I have about 13000 rows that I want to
transpose (end result would be about 1000 rows of address details).

The biggest problem I have is that I can't find any thing in the list of
data that highlights where I would want to start a new range. It also looks
like not everything is a set number of rows, so I can't even transpose every
10 rows.

Is there anything I can do other than select, copy, paste special,
transpose, repeat?

Thanks.

Is there at least a blank row between your records??
Is there at least a blank row between your records??
--
Gary''s Student - gsnu2007k


:

I think that there is a blank row, the reason I say think is because there is
I think that there is a blank row, the reason I say think is because there
is some extra data in columns B and C that are part of the same set, I am
only interested in what is in column A.

But, I think if I take what is in columns B and C and move it into column A,
then I will be left with a blank row after each set of data. I can then go
back and remove the data I don't want from each row.

So, yes, at some point I will have a blank row.


Ok, it looks like I have 2 blank rows after each record.
Ok, it looks like I have 2 blank rows after each record.

Post a few examples of your data.
Post a few examples of your data. It might be that the last line of
the address is always a postcode, or that the first line always begins
with Mr, so it might be possible to latch on to these.

Hope this helps.

Pete

ks
ery

Re: Transpose Address Data
Row to column -------------INDIRECT("R"&ROW($A$1)&"c"&COLUMN(A1)+ROW
(A1)-1,FALSE)

column to row -------------INDIRECT("R"&ROW(E4)+COLUMN(A1)-1&"c"&COLUMN
($E$4),FALSE)


Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Effective C#, Second Edition [Addison Wesley]
http://www.eggheadcafe.com/tutorial...7af-c38852b3b455/book-review-effective-c.aspx

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message

There is an Excel Add-In named Report Inverter which helps you to
transpose Address Data in Excel. You can download the free trial version
in www.databiliosuite.com



H J wrote:

Transpose Address Data
05-Jan-09

Is there any way to transpose a range of data automatically? I know about
using the transpose function in Excel, but to use it I need to manually
select what I want to transpose. I have about 13000 rows that I want to
transpose (end result would be about 1000 rows of address details).

The biggest problem I have is that I can't find any thing in the list of
data that highlights where I would want to start a new range. It also
looks
like not everything is a set number of rows, so I can't even transpose
every
10 rows.

Is there anything I can do other than select, copy, paste special,
transpose, repeat?

Thanks.

Previous Posts In This Thread:

Transpose Address Data
Is there any way to transpose a range of data automatically? I know about
using the transpose function in Excel, but to use it I need to manually
select what I want to transpose. I have about 13000 rows that I want to
transpose (end result would be about 1000 rows of address details).

The biggest problem I have is that I can't find any thing in the list of
data that highlights where I would want to start a new range. It also
looks
like not everything is a set number of rows, so I can't even transpose
every
10 rows.

Is there anything I can do other than select, copy, paste special,
transpose, repeat?

Thanks.

Is there at least a blank row between your records??
Is there at least a blank row between your records??
--
Gary''s Student - gsnu2007k


:

I think that there is a blank row, the reason I say think is because there
is
I think that there is a blank row, the reason I say think is because there
is some extra data in columns B and C that are part of the same set, I am
only interested in what is in column A.

But, I think if I take what is in columns B and C and move it into column
A,
then I will be left with a blank row after each set of data. I can then go
back and remove the data I don't want from each row.

So, yes, at some point I will have a blank row.

message

Ok, it looks like I have 2 blank rows after each record.
Ok, it looks like I have 2 blank rows after each record.

Post a few examples of your data.
Post a few examples of your data. It might be that the last line of
the address is always a postcode, or that the first line always begins
with Mr, so it might be possible to latch on to these.

Hope this helps.

Pete

ks
ery

Re: Transpose Address Data
Row to column -------------INDIRECT("R"&ROW($A$1)&"c"&COLUMN(A1)+ROW
(A1)-1,FALSE)

column to row -------------INDIRECT("R"&ROW(E4)+COLUMN(A1)-1&"c"&COLUMN
($E$4),FALSE)


Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Effective C#, Second Edition [Addison Wesley]
http://www.eggheadcafe.com/tutorial...7af-c38852b3b455/book-review-effective-c.aspx