Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting datasets on two rows
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 whe Albion Gate Albion Street W2 2LA belong to the same data record. Thanks a lot anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting datasets on two rows
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 On Jun 30, 5:22*pm, anyole wrote: 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 whe Albion Gate Albion Street W2 2LA belong to the same data record. Thanks a lot anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting datasets on two rows
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" wrote: 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 On Jun 30, 5:22 pm, anyole wrote: 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 whe Albion Gate Albion Street W2 2LA belong to the same data record. Thanks a lot anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting datasets on two rows
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 On Jul 1, 12:54*pm, anyole wrote: 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 * -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting datasets on two rows
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 -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "Pete_UK" wrote: 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 On Jul 1, 12:54 pm, anyole wrote: 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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a two axis chart - two datasets | Charts and Charting in Excel | |||
most efficient way to filter out and iterate through datasets in X | Excel Discussion (Misc queries) | |||
Matching formula results to datasets | Excel Worksheet Functions | |||
Manipulating subsets of large datasets | Excel Worksheet Functions | |||
charts macro for a number of datasets | Charts and Charting in Excel |