sorting datasets on two rows

A

anyole

if one set of data record is on two rows, how do i sort it so that the rows
retain data relevant to the dataset? eg.

Albion Gate
Albion Street W2 2LA
Alcazar, The
Phoenix Street WC2H 8BS
Alcove
Napier Place W14 8LG
Alders, The
Aldrington Road SW16 1TP
Aldershaw
Watkinson Road N7 8EX
Aldgate Barrs
Whitechapel High Street E1 7PJ
Alfred Villas
Barrett Road E17 3NB

where:
Albion Gate
Albion Street W2 2LA

belong to the same data record.

Thanks a lot
anyole
 
P

Pete_UK

Were you thinking of sorting them on some other field (like name)?

You can introduce a simple sequence 1,2,3,4 etc down a helper column
and if you include this in your sort criteria then you can get the
rows to follow the sequence, though it would depend on the other sort
field (name) appearing in all cells down the column.

If your data is not like this then post back with further details of
how your data is laid out, and I'm sure someone will be able to
suggest a way forward.

Hope this helps.

Pete
 
A

anyole

thanks pete. the sorting i need is alphabetical; that is to say when i sort a
large amount of data, i need the two rows containing related information to
remain related. are you able to give a formula i can use for this?
thanks in advance.
anyole
--
ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe.
khuli halala. nyasaye akhulinde. embwo


Pete_UK said:
Were you thinking of sorting them on some other field (like name)?

You can introduce a simple sequence 1,2,3,4 etc down a helper column
and if you include this in your sort criteria then you can get the
rows to follow the sequence, though it would depend on the other sort
field (name) appearing in all cells down the column.

If your data is not like this then post back with further details of
how your data is laid out, and I'm sure someone will be able to
suggest a way forward.

Hope this helps.

Pete
Click to expand...
 
P

Pete_UK

It all depends on how your data is laid out. If you have something
like this beginning in row 2 (with row 1 for headers):

Name_a Albion Gate more data ...
Albion Street W2 2LA
Name_b Alcazar, The more data ...
Phoenix Street WC2H 8BS
Name_c Alcove more data ...
Napier Place W14 8LG

and so on, and you want to sort by name, then you can introduce a new
column B and in B2 have a formula like:

=IF(A2="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2))

and copy this down. You would then have:

Name_a Name_a0 Albion Gate more data ...
Name_a1 Albion Street W2 2LA
Name_b Name_b0 Alcazar, The more data ...
Name_b1 Phoenix Street WC2H 8BS
Name_c Name_c0 Alcove more
data ...
Name_c1 Napier Place W14 8LG

and if you were to sort this on column B your two lines would be kept
in the same sequence under each name, but the data would be in name
order. Column B could then be deleted to get your data back as it was.

However, if you data is organised differently, then you will have to
let us know and it might need a different approach.

Hope this helps.

Pete
 
A

anyole

am not sure where to apply this formular but i tried using it under functions
but i got terribly lost. i can see column B but how do i creat B2 etc? i also
tried conditional formatting but nothing happened. sorry but i would do with
a fools guide as am not so adept at excel. mind you am using excel 2003 which
may be a bit dated.
basically the data i need to sort is name and address on two rows in
alphabetical order like in the examples i have given. i have about 4,000
pieces of records i need to sort and you can imagine doing it manually?

any help is highly appreciated.
regards
anyole
 
Top