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