how to sort grouped data?

[andy1010]

dear all, please kindly see the attachment
i tried to group and sort with no hope
i would like to sort by the last row of each group matching with the first row another group without losing the group
thank you very much in advance

[popipipo]

With a help column C:
Put this formula in cel C3 and copy down
=IF(ISERROR(A3);C2;A3)

Then copy , paste special , value
Then you can sort on column C

[andy1010]

With a help column C:
Put this formula in cel C3 and copy down
=IF(ISERROR(A3);C2;A3)

Then copy , paste special , value
Then you can sort on column C

thanks a lot but it didn't work.
i suppose u replace , with ; in the formula? because it has an error if using ;
after i sorted, i got the attached file: lost the grouping

[popipipo]

all put it in a macro

[andy1010]

all put it in a macro

thanks a lot!
however, i found it difficult to fit into my data
attached is what i really want to do
I tried to do the ascending instead of descending in the macro, but it does not give what i want
sorry for the inconvenience caused

[Ali Kırksekiz]

I deleted my post

[Ali Kırksekiz]

For H3

IF(ROWS($H$3:H3)<=COUNT($B$3:$B$234),LARGE($B$3:$B$234,ROWS($H$3:H3)),"")

then pull down to H234

For G3

=INDEX($C$3:$C$234,SMALL(IF($B$3:$B$234=H3,ROW($B$3:$B$234)-ROW($B$3)+1),COUNTIF($H$3:H3,H3)))

This is an Array Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

then pull down to G234

[andy1010]

For H3

IF(ROWS($H$3:H3)<=COUNT($B$3:$B$234),LARGE($B$3:$B$234,ROWS($H$3:H3)),"")

then pull down to H234

For G3

=INDEX($C$3:$C$234,SMALL(IF($B$3:$B$234=H3,ROW($B$3:$B$234)-ROW($B$3)+1),COUNTIF($H$3:H3,H3)))

This is an Array Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

then pull down to G234

thank you.
but dear, it doesn't like working
do I make anything wrong as per attachment?

[Ali Kırksekiz]

For G3

=IF(ROWS($J$3:J3)<=COUNT($C$3:$C$234),SMALL($C$3:$C$234,ROWS($J$3:J3)),"")

For H3

=INDEX($B$3:$B$234,LARGE(IF($C$3:$C$234=G3,ROW($C$3:$C$234)-ROW($C$3)+1),COUNTIF($G$3:G3,G3)))

This in an array formula and so must be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

[andy1010]

For G3

=IF(ROWS($J$3:J3)<=COUNT($C$3:$C$234),SMALL($C$3:$C$234,ROWS($J$3:J3)),"")

For H3

=INDEX($B$3:$B$234,LARGE(IF($C$3:$C$234=G3,ROW($C$3:$C$234)-ROW($C$3)+1),COUNTIF($G$3:G3,G3)))

This in an array formula and so must be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

may I ask what is in column J? it is blank
thanks!

[Ali Kırksekiz]

This part (ROWS($J$3:J3)) is for sequence number.

You can change this part. For example ROWS($H$3:H3) Both formulas gives same result

[andy1010]

This part (ROWS($J$3:J3)) is for sequence number.

You can change this part. For example ROWS($H$3:H3) Both formulas gives same result

Thanks.
but dear, it still gives the same result as the macro written by popipipo
is it possble to give the "want" column in #6 groupsort-3.xlsm‎ ?

[andy1010]

a slight push for help