View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK Pete_UK is offline
external usenet poster
 
Posts: 8,856
Default 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