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

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

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

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

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

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

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?