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

HTH
Kostis Vezerides


On Jul 2, 5:43 pm, anyole <any...@discussions.microsoft.com> 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, Tools>Options>Edit.........enable drag and drop.


Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 07:43:05 -0700, anyole <(E-Mail Removed)>
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?

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,B3+1))
>
> HTH
> Kostis Vezerides
>
>
> On Jul 2, 5:43 pm, anyole <any...@discussions.microsoft.com> 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 <any...@discussions.microsoft.com> 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,B3+1))
>
> > HTH
> > Kostis Vezerides
>
> > On Jul 2, 5:43 pm, anyole <any...@discussions.microsoft.com> 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 <any...@discussions.microsoft.com> 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,B3+1))
> >
> > > HTH
> > > Kostis Vezerides
> >
> > > On Jul 2, 5:43 pm, anyole <any...@discussions.microsoft.com> 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 <any...@discussions.microsoft.com> 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 <any...@discussions.microsoft.com> 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 towork?
>
> > > 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,B3+1))
>
> > > > HTH
> > > > Kostis Vezerides
>
> > > > On Jul 2, 5:43 pm, anyole <any...@discussions.microsoft.com> 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

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 <any...@discussions.microsoft.com> 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 <any...@discussions.microsoft.com> 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,B3+1))
> >
> > > > > HTH
> > > > > Kostis Vezerides
> >
> > > > > On Jul 2, 5:43 pm, anyole <any...@discussions.microsoft.com> 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
>
>

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 <any...@discussions.microsoft.com> 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
> --

that's great pete. what email shall i use? (E-Mail Removed)?
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 <any...@discussions.microsoft.com> 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
> > --
>