|
how do i sort data record on two rows?
am reposting this query as an sos. i had received some help from pete_uk but
maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
I am assuming that the 2-cell records are adjacent, i.e. no blank
cells between them. I am also assuming that you want them sorted in another column, still maintaining the 2-cell structure. Say your data is in A4:A15. Auxiliary column (assume start at same row, hence in B4). Array formula (commit with Shift+Ctrl+Enter): =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$15)-ROW($A$4), 2)=0)*(COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)=INT(ROWS ($B$4:B4)-1)/2), 0),"") Sorted column (starts at C4): =IF(B4<"",INDEX($A$4:$A$15,B4),INDEX($A$4:$A$15,B 3+1)) HTH Kostis Vezerides On Jul 2, 5:43 pm, anyole wrote: am reposting this query as an sos. i had received some help from pete_uk but maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
Copy down means select B2 with the formula entered then hover the cursor over
the lower right corner black lump untill you see a small black cross. Click on the cross and drag down the column to "copy down". The black lump is also known as the "fill handle" If you don't see one, ToolsOptionsEdit.........enable drag and drop. Gord Dibben MS Excel MVP On Wed, 2 Jul 2008 07:43:05 -0700, anyole wrote: Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? |
how do i sort data record on two rows?
Thanks but still no joy am afraid. Yes there are no gaps between the cells.
am a bit confused about auxilliary column B and the sorting cloumn C. do i have to copy the data in A and paste in both B and C for the formula to work? And in which column shall i press shift+Ctrl+Enter for the formula to work? Any further help is much appreciated. anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: I am assuming that the 2-cell records are adjacent, i.e. no blank cells between them. I am also assuming that you want them sorted in another column, still maintaining the 2-cell structure. Say your data is in A4:A15. Auxiliary column (assume start at same row, hence in B4). Array formula (commit with Shift+Ctrl+Enter): =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$15)-ROW($A$4), 2)=0)*(COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)=INT(ROWS ($B$4:B4)-1)/2), 0),"") Sorted column (starts at C4): =IF(B4<"",INDEX($A$4:$A$15,B4),INDEX($A$4:$A$15,B 3+1)) HTH Kostis Vezerides On Jul 2, 5:43 pm, anyole wrote: am reposting this query as an sos. i had received some help from pete_uk but maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
Don;t be afraid, you will not lose any data. Keep a copy of the file
before you attempt this. You don't have to copy the data in A. Just put the suggested formula in B. In the formula I assume that data start from row 4 and go up to row 15. This is not the case in you. In this formula change the 4 with the row number where your data start (and the aux column also). Also change the 15 to the last row number of data. Leave the $$ unchanged. The easiest is to copy the formula I am suggesting to an empty text file. Edit the changes (4-something, 15 to something else). Then copy the edited formula. Then, go to Excel, press the function key F2 and paste the formula. Then instead of Enter press Shift+Ctrl+Enter This completes entering the aux formula. For the formula to bring the data, make the same changes. Change B3 to Bx, where x is one row above your data. E.g. if you start from A2, make it B1. This formula does not need array-entering. Once you have the two formulas you can copy them down as far as necessary. HTH Kostis On Jul 3, 3:43 pm, anyole wrote: Thanks but still no joy am afraid. Yes there are no gaps between the cells. am a bit confused about auxilliary column B and the sorting cloumn C. do i have to copy the data in A and paste in both B and C for the formula to work? And in which column shall i press shift+Ctrl+Enter for the formula to work? Any further help is much appreciated. anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: I am assuming that the 2-cell records are adjacent, i.e. no blank cells between them. I am also assuming that you want them sorted in another column, still maintaining the 2-cell structure. Say your data is in A4:A15. Auxiliary column (assume start at same row, hence in B4). Array formula (commit with Shift+Ctrl+Enter): =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$15)-ROW($A$4), 2)=0)*(COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)=INT(ROWS ($B$4:B4)-1)/2), 0),"") Sorted column (starts at C4): =IF(B4<"",INDEX($A$4:$A$15,B4),INDEX($A$4:$A$15,B 3+1)) HTH Kostis Vezerides On Jul 2, 5:43 pm, anyole wrote: am reposting this query as an sos. i had received some help from pete_uk but maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
THANKS VERERID. I SEEM TO BE GETTING SOMEWHERE. THE RANGE I WANT SORTED IS
HIGHLIGHTED USING THE FORMULA BUT WHEN I SHIFT+CTRL+ENTER, IT SAYS FORMULA CONTAINS ERROR AND IT SEEMS TO BE POINTING AT (COUNTIF...) IAM SORTING THE RANGE A4 - A33 USING THE FOLLOWING FORMULA: =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$33)-ROW($A$4),2)=0)*(COUNTIF($A$4:$A$33,<&$A$4:$ A$33)=INT(ROWS($B$4:B4)-1)/2),0),) ANY IDEA WHY IT'S NOT WORKING? AS ALWAYS, YOUR INPUT MUCH APPRECIATED. ANYOLE -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: Don;t be afraid, you will not lose any data. Keep a copy of the file before you attempt this. You don't have to copy the data in A. Just put the suggested formula in B. In the formula I assume that data start from row 4 and go up to row 15. This is not the case in you. In this formula change the 4 with the row number where your data start (and the aux column also). Also change the 15 to the last row number of data. Leave the $$ unchanged. The easiest is to copy the formula I am suggesting to an empty text file. Edit the changes (4-something, 15 to something else). Then copy the edited formula. Then, go to Excel, press the function key F2 and paste the formula. Then instead of Enter press Shift+Ctrl+Enter This completes entering the aux formula. For the formula to bring the data, make the same changes. Change B3 to Bx, where x is one row above your data. E.g. if you start from A2, make it B1. This formula does not need array-entering. Once you have the two formulas you can copy them down as far as necessary. HTH Kostis On Jul 3, 3:43 pm, anyole wrote: Thanks but still no joy am afraid. Yes there are no gaps between the cells. am a bit confused about auxilliary column B and the sorting cloumn C. do i have to copy the data in A and paste in both B and C for the formula to work? And in which column shall i press shift+Ctrl+Enter for the formula to work? Any further help is much appreciated. anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: I am assuming that the 2-cell records are adjacent, i.e. no blank cells between them. I am also assuming that you want them sorted in another column, still maintaining the 2-cell structure. Say your data is in A4:A15. Auxiliary column (assume start at same row, hence in B4). Array formula (commit with Shift+Ctrl+Enter): =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$15)-ROW($A$4), 2)=0)*(COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)=INT(ROWS ($B$4:B4)-1)/2), 0),"") Sorted column (starts at C4): =IF(B4<"",INDEX($A$4:$A$15,B4),INDEX($A$4:$A$15,B 3+1)) HTH Kostis Vezerides On Jul 2, 5:43 pm, anyole wrote: am reposting this query as an sos. i had received some help from pete_uk but maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
Anyole,
I copied and pasted your formula into a text editor and the double quotes " were not the characters expected. They were another character and this is not accepted by Excel. What keyboard are you using? In "regular" keyboards you will use the double quote key found exactly at the left of the Enter key. Usually, pressing Shift+' (single quote). This is the problem of the formula. If necessary, copy the following character: " and edit the formula and paste it wherever your formula thinks that it has the double quotes. HTH Kostis On Jul 4, 4:37 pm, anyole wrote: THANKS VERERID. I SEEM TO BE GETTING SOMEWHERE. THE RANGE I WANT SORTED IS HIGHLIGHTED USING THE FORMULA BUT WHEN I SHIFT+CTRL+ENTER, IT SAYS FORMULA CONTAINS ERROR AND IT SEEMS TO BE POINTING AT (COUNTIF...) IAM SORTING THE RANGE A4 - A33 USING THE FOLLOWING FORMULA: =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$33)-ROW($A$4),2)=0)*(COUNTIF($A$4:$A$33,<&$A$4:$A$33 )=INT(ROWS($B$4:B4)-1)/2),0),) ANY IDEA WHY IT'S NOT WORKING? AS ALWAYS, YOUR INPUT MUCH APPRECIATED. ANYOLE -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: Don;t be afraid, you will not lose any data. Keep a copy of the file before you attempt this. You don't have to copy the data in A. Just put the suggested formula in B. In the formula I assume that data start from row 4 and go up to row 15. This is not the case in you. In this formula change the 4 with the row number where your data start (and the aux column also). Also change the 15 to the last row number of data. Leave the $$ unchanged. The easiest is to copy the formula I am suggesting to an empty text file. Edit the changes (4-something, 15 to something else). Then copy the edited formula. Then, go to Excel, press the function key F2 and paste the formula. Then instead of Enter press Shift+Ctrl+Enter This completes entering the aux formula. For the formula to bring the data, make the same changes. Change B3 to Bx, where x is one row above your data. E.g. if you start from A2, make it B1. This formula does not need array-entering. Once you have the two formulas you can copy them down as far as necessary. HTH Kostis On Jul 3, 3:43 pm, anyole wrote: Thanks but still no joy am afraid. Yes there are no gaps between the cells. am a bit confused about auxilliary column B and the sorting cloumn C. do i have to copy the data in A and paste in both B and C for the formula to work? And in which column shall i press shift+Ctrl+Enter for the formula to work? Any further help is much appreciated. anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: I am assuming that the 2-cell records are adjacent, i.e. no blank cells between them. I am also assuming that you want them sorted in another column, still maintaining the 2-cell structure. Say your data is in A4:A15. Auxiliary column (assume start at same row, hence in B4). Array formula (commit with Shift+Ctrl+Enter): =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$15)-ROW($A$4), 2)=0)*(COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)=INT(ROWS ($B$4:B4)-1)/2), 0),"") Sorted column (starts at C4): =IF(B4<"",INDEX($A$4:$A$15,B4),INDEX($A$4:$A$15,B 3+1)) HTH Kostis Vezerides On Jul 2, 5:43 pm, anyole wrote: am reposting this query as an sos. i had received some help from pete_uk but maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
am about to raise the white flag but something tells me not to. i have opied
the " mark and pasted where appropriate but when i shift+ctr+enter, what i get in colum B is #N/A and nothing happens in column C either wher i have pasted the second formula. for your information, am pasting teh formulae here. plse see if am doing the right thing. =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$33)-ROW($A$4),2)=0)*(COUNTIF($A$4:$A$33, "< "&$A$4:$A$33)=INT(ROWS($B$4:B4)-1)/2),0),"") =IF(B4<"",INDEX($A$4:$A$33,B4*INDEX($A$4:$A$33,B3 +1)) as always many thanks anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: Anyole, I copied and pasted your formula into a text editor and the double quotes " were not the characters expected. They were another character and this is not accepted by Excel. What keyboard are you using? In "regular" keyboards you will use the double quote key found exactly at the left of the Enter key. Usually, pressing Shift+' (single quote). This is the problem of the formula. If necessary, copy the following character: " and edit the formula and paste it wherever your formula thinks that it has the double quotes. HTH Kostis On Jul 4, 4:37 pm, anyole wrote: THANKS VERERID. I SEEM TO BE GETTING SOMEWHERE. THE RANGE I WANT SORTED IS HIGHLIGHTED USING THE FORMULA BUT WHEN I SHIFT+CTRL+ENTER, IT SAYS FORMULA CONTAINS ERROR AND IT SEEMS TO BE POINTING AT (COUNTIF...) IAM SORTING THE RANGE A4 - A33 USING THE FOLLOWING FORMULA: =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$33)-ROW($A$4),2)=0)*(COUNTIF($A$4:$A$33,<&$A$4:$ A$33)=INT(ROWS($B$4:B4)-1)/2),0),) ANY IDEA WHY IT'S NOT WORKING? AS ALWAYS, YOUR INPUT MUCH APPRECIATED. ANYOLE -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: Don;t be afraid, you will not lose any data. Keep a copy of the file before you attempt this. You don't have to copy the data in A. Just put the suggested formula in B. In the formula I assume that data start from row 4 and go up to row 15. This is not the case in you. In this formula change the 4 with the row number where your data start (and the aux column also). Also change the 15 to the last row number of data. Leave the $$ unchanged. The easiest is to copy the formula I am suggesting to an empty text file. Edit the changes (4-something, 15 to something else). Then copy the edited formula. Then, go to Excel, press the function key F2 and paste the formula. Then instead of Enter press Shift+Ctrl+Enter This completes entering the aux formula. For the formula to bring the data, make the same changes. Change B3 to Bx, where x is one row above your data. E.g. if you start from A2, make it B1. This formula does not need array-entering. Once you have the two formulas you can copy them down as far as necessary. HTH Kostis On Jul 3, 3:43 pm, anyole wrote: Thanks but still no joy am afraid. Yes there are no gaps between the cells. am a bit confused about auxilliary column B and the sorting cloumn C. do i have to copy the data in A and paste in both B and C for the formula to work? And in which column shall i press shift+Ctrl+Enter for the formula to work? Any further help is much appreciated. anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "vezerid" wrote: I am assuming that the 2-cell records are adjacent, i.e. no blank cells between them. I am also assuming that you want them sorted in another column, still maintaining the 2-cell structure. Say your data is in A4:A15. Auxiliary column (assume start at same row, hence in B4). Array formula (commit with Shift+Ctrl+Enter): =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$15)-ROW($A$4), 2)=0)*(COUNTIF($A$4:$A$15,"<"&$A$4:$A$15)=INT(ROWS ($B$4:B4)-1)/2), 0),"") Sorted column (starts at C4): =IF(B4<"",INDEX($A$4:$A$15,B4),INDEX($A$4:$A$15,B 3+1)) HTH Kostis Vezerides On Jul 2, 5:43 pm, anyole wrote: am reposting this query as an sos. i had received some help from pete_uk but maybe i didn't explain myself clear enough. thanks pete but am still lost. i need a fools guide on how to sort data alphabetically where two rows belong to the same record. The following example shows two records on two rows and I have 4,000 plus records like this that i need sorted so that only the first row is alphabetical but does not lose the secod row when sorted: Bank Buildings Ruthin Close NW9 7RP Baltic Exchange, The 38 St Mary Axe EC3A 8BH. Pete gave me a formula =IF(A2)="",B1&MOD(ROW(A2),2),B2&MOD(ROW(A2),2)) to put in a new column B2 and copy down. Excuse my ignorance but what does copy down mean in excel parlance? Please help? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
how do i sort data record on two rows?
If your addresses are not confidential, would you like to send the
file to me and I can sort it for you? Send to: pashurst at auditel dot net Change the obvious. Hope this helps. Pete On Jul 7, 9:44*am, anyole wrote: am about to raise the white flag but something tells me not to. i have opied the " mark and pasted where appropriate but when i shift+ctr+enter, what i get in colum B is #N/A and nothing happens in column C either wher i have pasted the second formula. for your information, am pasting teh formulae here. plse see if am doing the right thing. =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$33)-ROW($A$4),2)=0)*(*COUNTIF($A$4:$A$33, "< "&$A$4:$A$33)=INT(ROWS($B$4:B4)-1)/2),0),"") =IF(B4<"",INDEX($A$4:$A$33,B4*INDEX($A$4:$A$33,B3 +1)) as always many thanks anyole -- |
how do i sort data record on two rows?
|
how do i sort data record on two rows?
I gave you my email address:
pashurst at auditel dot net Pete On Jul 7, 10:16*am, anyole wrote: that's great pete. what email shall i use? ? anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "Pete_UK" wrote: If your addresses are not confidential, would you like to send the file to me and I can sort it for you? Send to: pashurst at auditel dot net Change the obvious. Hope this helps. Pete On Jul 7, 9:44 am, anyole wrote: am about to raise the white flag but something tells me not to. i have opied the " mark and pasted where appropriate but when i shift+ctr+enter, what i get in colum B is #N/A and nothing happens in column C either wher i have pasted the second formula. for your information, am pasting teh formulae here. plse see if am doing the right thing. =IF(MOD(ROW()-ROW($B$4),2)=0,MATCH(1,(MOD(ROW($A$4:$A$33)-ROW($A$4),2)=0)*(**COUNTIF($A$4:$A$33, "< "&$A$4:$A$33)=INT(ROWS($B$4:B4)-1)/2),0),"") =IF(B4<"",INDEX($A$4:$A$33,B4*INDEX($A$4:$A$33,B3 +1)) as always many thanks anyole --- Hide quoted text - - Show quoted text - |
how do i sort data record on two rows?
Just for completeness, I received the file - over 63,000 rows, all in
column A, with 2 rows per record. I sorted it and sent it back. Here's the comments I made in the email back to the OP: " ... If you need to do this again in the future, here are the necessary steps: a: put this formula in B1: =INT((ROW(A1)+1)/2)+MOD(ROW(A2),2)/10 b: Copy the formula down column B, for as many items as you have in column A. You can copy this down to the bottom of your file by double- clicking the fill handle (the small black square in the bottom right corner of the cursor, with B1 selected. It will give you alternately 1.0, 1.1, 2.0, 2.1 etc in order to keep the pairs together when sorted. c: Put this formula in C1: =A1 d: Put this formula in C2: =IF(MOD(ROW(),2)=1,A2,A1) e: Copy the formula in C2 down to the bottom, using the fill handle as described above. It will give you the first entry for each paired row. f: Fix the values in columns B and C. To do this, click on the column letters B and C at the top of the columns in order to highlight both, then click <copy and then Edit | Paste Special | Values | OK then <Esc. g: Highlight columns A to C and click on Data | Sort. Ensure you have No Header Row checked, and choose Column C as the first sort field and Column B as the second sort field, both in ascending order, then click OK. h: Your data is now sorted - highlight columns B and C and click on Edit | Delete to return your sheet to how it was. .... " Hope this helps anyone else in a similar situation. Pete |
how do i sort data record on two rows?
Pete,
thanks a million times pete for sorting this one for me. i couldn't thank you enough plus the rest of the wonderful team for all your perseverance. am one happy bunny this afternoon! cheers anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo "Pete_UK" wrote: Just for completeness, I received the file - over 63,000 rows, all in column A, with 2 rows per record. I sorted it and sent it back. Here's the comments I made in the email back to the OP: " ... If you need to do this again in the future, here are the necessary steps: a: put this formula in B1: =INT((ROW(A1)+1)/2)+MOD(ROW(A2),2)/10 b: Copy the formula down column B, for as many items as you have in column A. You can copy this down to the bottom of your file by double- clicking the fill handle (the small black square in the bottom right corner of the cursor, with B1 selected. It will give you alternately 1.0, 1.1, 2.0, 2.1 etc in order to keep the pairs together when sorted. c: Put this formula in C1: =A1 d: Put this formula in C2: =IF(MOD(ROW(),2)=1,A2,A1) e: Copy the formula in C2 down to the bottom, using the fill handle as described above. It will give you the first entry for each paired row. f: Fix the values in columns B and C. To do this, click on the column letters B and C at the top of the columns in order to highlight both, then click <copy and then Edit | Paste Special | Values | OK then <Esc. g: Highlight columns A to C and click on Data | Sort. Ensure you have No Header Row checked, and choose Column C as the first sort field and Column B as the second sort field, both in ascending order, then click OK. h: Your data is now sorted - highlight columns B and C and click on Edit | Delete to return your sheet to how it was. .... " Hope this helps anyone else in a similar situation. Pete |
how do i sort data record on two rows?
You're welcome - thanks for feeding back.
Sorry I misled you on your first post, as I thought you had names in another column. Pete On Jul 7, 2:56*pm, anyole wrote: Pete, thanks a million times pete for sorting this one for me. i couldn't thank you enough plus the rest of the wonderful team for all your perseverance. am one happy bunny this afternoon! cheers anyole -- ino ne sauti ya anyole okhurula ebusikhale emmayoka. orakhaenda omwoyo tawe. khuli halala. nyasaye akhulinde. embwo |
| All times are GMT +1. The time now is 10:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com