how do i sort data record on two rows?

  • Thread starter Thread starter anyole
  • Start date Start date
A

anyole

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
 
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,B3+1))

HTH
Kostis Vezerides
 
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, Tools>Options>Edit.........enable drag and drop.


Gord Dibben MS Excel MVP
 
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 said:
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,B3+1))

HTH
Kostis Vezerides
Click to expand...
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top