Array Formulae to Alphabetise on Two Columns

Afternoon, all!

I'm having one of my mental block moments. How do I get the two columns on the left into alphabetical order in pairs so that they look like those on the right? Must be by formula.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1
2	AAA	A		AAA	A
3	BBB	C		AAA	B
4	DDD	A		AAA	C
5	BBB	A		BBB	A
6	AAA	C		BBB	B
7	AAA	B		BBB	C
8	DDD	C		CCC	A
9	CCC	A		CCC	B
10	DDD	B		CCC	C
11	CCC	B		DDD	A
12	CCC	C		DDD	B
13	BBB	B		DDD	C

Sheet: Sheet1

Sample file attached.

Hi,

I think this will work. In D2
=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$2:$A$13,"<="&$A$2:$A$13)),$A$2:$A$13)
in E2
=LOOKUP(1,0/FREQUENCY(COUNTIF(D$1:D1,D2)+1,COUNTIFS($A$2:$A$13,D2,$B$2:$B$13,"<="&$B$2:$B$13)),$B$2:$B$13)
and fill down.

Originally Posted by ranman256

select the columns
click DATA on menu
click SORT
select your sort order, COL1, COL2

Thanks, but I specifically stated that I need formulae for this.

Originally Posted by xlnitwit

Hi,

I think this will work. ...

Thanks for the suggestion - I will give it a go tomorrow.

The first formula works perfectly, and so does the second until I tweak the data:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
1
2	DKH	Barnes		AAA	A		BBB	A
3	BBB	C		AAA	B		BBB	Barnes
4	DDD	A		AAA	C		BBB	C
5	BBB	A		BBB	A		CCC	A
6	DKH	Carroll		BBB	B		CCC	Barnes
7	DKH	Anson		BBB	C		CCC	C
8	DDD	C		CCC	A		DDD	A
9	CCC	A		CCC	B		DDD	Barnes
10	DDD	B		CCC	C		DDD	C
11	CCC	B		DDD	A		DKH	Anson
12	CCC	C		DDD	B		DKH	Barnes
13	BBB	B		DDD	C		DKH	Carroll

Sheet: Sheet1

Any ideas?

Oh dear!

Perhaps this in E2 instead
=LOOKUP(1,0/FREQUENCY(ROWS($1:1),MMULT(--($A$2:$A$13&$B$2:$B$13>=TRANSPOSE($A$2:$A$13&$B$2:$B$13)),ROW(INDIRECT("A1:A"&ROWS($A$2:$A$13)))^0)),$B$2:$B$13)
which must be array entered.

A helper column concatenating the names might make life simpler? You could use
=LOOKUP(1,0/FREQUENCY(ROWS($1:1),MMULT(--($A$2:$A$13&$B$2:$B$13>=TRANSPOSE($A$2:$A$13&$B$2:$B$13)),ROW(INDIRECT("A1:A"&ROWS($A$2:$A$13)))^0)),ROW($B$2:$B$13))
to return the required row number and then use two simple INDEX formulas.

Thanks again. I'll try this when I get to work.

This also works for E2.
ARRAY formula

=INDEX($B$2:$B$13,MATCH(COUNTIF($J$2:$J2,$J2),MMULT(--(IF($A$2:$A$13=J2,$B$2:$B$13,"")>=TRANSPOSE(IF($A$2:$A$13=J2,$B$2:$B$13,REPT("Z",20)))),--(ROW($A$2:$A$13)>0)),0))

A bit shorter here. Arrayformulas of course.

Formula:

Please Login or Register  to view this content.

Thank you all. Case closed.