Seeking help from a GURU

Try this:

It looks like your data starts in A1, so paste this formula in B1:

=OFFSET(INDIRECT("A"&(ROW()-1)*13+1),MOD(COLUMN()-2,13),0)

Then, copy this formula all the way to column "N" (the 13====== should
appear in N1 now). This should transpose A1:A13 into B1:N1. Then, drag this
entire selection (B1:N1) down to the next row. B2:N2 should now be the
transpose of A14:A26. You can drag this entire selection down as far as
needed, with each row it will transpose the next 13 rows of column A into
columns B to N.

Is that what you needed?
--
Regards,
Dave


"usf97j4x4" wrote:

>
> I have a list of taxpayers in a county and I am trying to break it up
> into a list of Names and Addresses.
>
> There is a lot of erroneous data included on the list and it is not an
> excel file or any other recognizeable format.
>
> When i import the data into excel, everything is put into a separate
> cell. It looks something like this: Each line would be in a separate
> cell... but all in one column
>
> A
> 1===========================
> 2Account: 000151270003
> 3Owner: A 4 HOME CENTER
> 4
> 5PO BOX 757
> 6Market Value: 250,190
> 7ROBBINSVILLE NC 28771
> 8
> 9Tax Description Asses
> 10__________________ _____
> 11COUNTY WIDE 250,
> 12
> 13===========================
> (this goes to row 65600 or something ridiculous)
>
> (Hope this is making sense thus far)
>
> Now i need to somehow separate this into a useable data base for
> instance, column A is name, column B is address etc.
>
>
> I guess i need each of the 13 rows above put into their own individual
> columns? Then i can delete the ones i dont need.
> I tried a paste - transpose which works fine for one block. But i have
> like 65,000 of these 13 row blocks.
>
>
> If anyone thinks they could help me out on this matter i would REALLY
> appreciate it. We are starting a food bank for a small mountain town
> in North Carolina so your effort is going to a good cause! If anyone
> would like to take a look at the raw data file or the excel data i have
> thus far i wouldnt have a problem emailing it to you. Its public record
> so its not like there are any confidentiality issues.
>
> Thanks so much!!!
>
>
> --
> usf97j4x4
> ------------------------------------------------------------------------
> usf97j4x4's Profile: http://www.excelforum.com/member.php...o&userid=30361
> View this thread: http://www.excelforum.com/showthread...hreadid=500160
>
>

If all of your data beginns in Cell A1 and continues down, try this:

B1: 2
C1: 3
D1: 5
E1: 6
F1: 7
Then put this formula in B3 and copy across thru F2
=INDEX($A$1:$A$65000,B$1+(ROW()-2)*13)

Then copy cells B2:F2 down as far as you need

Adjust the Row_1 numbers to select different fields

Does that help?

Regards,
Ron

Thanks for the responses guys

I did what you said and started filling down, when the first 10 worked i got all excited but then they started offsetting. I figured out that not every block is exactly 13 lines.. Some are 11, some 12, etc.. I believe this may be a lost cause.

Don't give up just yet.

Is there a pattern to the data?
For instance...does the address always begin 2 rows below the Owner field?

If yes...Try this:
D1: Owner
E1: Addr1
F1: Addr2
G1: Addr3

B2:B65000 (numeric sequence from 1 through 64999)

C2: =SMALL(IF(LEFT($A$1:$A$65000,5)="Owner",ROW($A$1:$A$65000 )),B2)
(Note: commit that array formula by holding down [Ctrl]+[Shift] and press [Enter])

Copy that formula down as far as you need.
(That formula finds each successive occurrence of the Owner field)

D2: =INDEX($A$1:$A$65000,$C2)
E2: =INDEX($A$1:$A$65000,$C2+2)
F2: =INDEX($A$1:$A$65000,$C2+3)
G2: =INDEX($A$1:$A$65000,$C2+4)

Copy cells D2:G2 down as far as you need.

Is that something you can work with?

Regards,
Ron