Data Filtering

[Jimipolo]

I have 22000 rows of data that are sorted into two columns. column a has 10
rows of address labels, a space, then repeated over and over again. i need to
sort this information into 10 columns but don't know how. can anybody help
with a formula that will enable me to have column headers of Name address etc
and the data filtered correctly

"current data"
Name 1st Move International Ltd
Add1 International House Worthy Road
Add2 Chittening Industrial Estate
Add3 Chittening
Add4 Bristol
PC BS11 0YB
Tel 0117-982-8123
Fax 0117-982-2229
Mail (e-mail address removed)
web www.shipit.co.uk

I need it to be like this

A B and so on
Name Add1 and so on

Please help!!

 

[Gord Dibben]

Jimi

On a copy of the worksheet..........................

Assuming name is cell B1 enter in C1 and drag across to L1 and down until you
get zeros.

=OFFSET($B$1,(ROW()-1)*10+COLUMN()-3,0)

When happy,select Columns C:L then copy>paste special(in place)>values>OK

Insert a row at row 1 then copy>paste special>transpose the headers in A1:A10
to C1:L1

Delete columns A and B.


Gord Dibben Excel MVP

 

[Jimipolo]

many thanks for your time and effort in responding to my query. I;m not that
great in excel and although your reply is 100% correct i cannot get it to
work. maybe i am using an olser version of ecxcel as it doesn't give me the
option to transpose certain labels - only a tick box! can i e-mail you my
spreadsheet and you can talk me through it, or alternatively post a reply as
if you were advising an absolute computer 1st timer in order that i will
understand!!

Once again i thank you for your assistance

 

[Dave Peterson]

Did you get the =offset() formula to work ok in C1:L1?
(It worked fine for me.)

Then I followed Gord's instruction to copy it down the rows until I got a bunch
of 0's.

Then I selected columns C:L
edit|copy
edit|paste special|Values

Then I inserted a new row 1
Then I selected A2:A11
edit|copy
then I selected C1
edit|paste special|check Transpose

If all that works fine (and it did for me), then I deleted columns A:B and saved
it as a new name.

I bet it was just a minor mistake--try it again. If it doesn't work, keep track
of what you did and where it went wrong and post back with your results.

But I bet you'll be sending a "thank you" to Gord!

 

[Gord Dibben]

Change the DOT and AT to appropriate characters and send it along.

Gord

 

[Gord Dibben]

Oops!

Told Jimi to send book to me via email.

Did not see(BAAB) your reply and explanation.

Second time through with yours should get him sorted, but I will set up his
book if he sends it.


Gord

 

[Jimipolo]

Gord,

I got it to work!!! I am a very happy man and cannot thank you enough, I
have spent days on reformatting the data then realised that i couldn't do
with it what i needed.

I had to change your formula to *11 instead of *10 as i had an extra line in
between each set of addresses.

Once again thanks

 

[Gord Dibben]

Good find.

You did mention in the first post you had a space between each set.

Missed that.


Gord