Sort with multiple Criteria
- Thread starter Kenshin
- Start date
Peter Bartholomew
Well-Known Member
May need some explanation!?
flag: = ( INDEX( data, MATCH( selected, name, 0 ), ) = "X") * number
pointer: = 1 + FLOOR(MATCH(order,flag,0) - 1, 4)
result: = INDEX( team, pointer)
flag: = ( INDEX( data, MATCH( selected, name, 0 ), ) = "X") * number
pointer: = 1 + FLOOR(MATCH(order,flag,0) - 1, 4)
result: = INDEX( team, pointer)
B14: =INDEX($B$3:$Q$3,,1+4*(INT(((MATCH(5-ROWS($A$14:A14)&"x",$B$4:$Q$4&OFFSET($B$4:$Q$4,MATCH($A$14,$A$5:$A$10,0),0),0))-0.1)/4)+0)) Ctrl+Shift+Enter
Copy down
C14: =INDEX($B$4:$Q$4,,MATCH(5-ROWS($A$14:A14)&"x",$B$4:$Q$4&OFFSET($B$4:$Q$4,MATCH($A$14,$A$5:$A$10,0),0),0))
Ctrl+Shift+Enter
Copy down
Copy down
C14: =INDEX($B$4:$Q$4,,MATCH(5-ROWS($A$14:A14)&"x",$B$4:$Q$4&OFFSET($B$4:$Q$4,MATCH($A$14,$A$5:$A$10,0),0),0))
Ctrl+Shift+Enter
Copy down
Bizarre as it works fine for me:
Two things to note
1. Did you press Ctrl+Shift+Enter to enter the formula in the B14 and C14 cells?
2. Are you aware that only David has a full set of x's in the 1, 2 ,3 & 4 cells
Note Jasmine doesn't have an X in the 4 cells and has two 1's
3. Mostly likely issue is that this isn't your actual data layout and you pasted it into a different cell? These formula are quite specific to there original location and the location of the Data, Peter's Formula should be more forgiving.
Two things to note
1. Did you press Ctrl+Shift+Enter to enter the formula in the B14 and C14 cells?
2. Are you aware that only David has a full set of x's in the 1, 2 ,3 & 4 cells
Note Jasmine doesn't have an X in the 4 cells and has two 1's
3. Mostly likely issue is that this isn't your actual data layout and you pasted it into a different cell? These formula are quite specific to there original location and the location of the Data, Peter's Formula should be more forgiving.
Peter Bartholomew
Well-Known Member
@Hui No, I do not think my formula is any more forgiving. It simply scans across until it finds an "x" in the required place and returns the group (team?) name.
@Kenshin
I had assumed (despite the fact that the actual data was non-conformant) that
1. Each number [1-4] would be present within each row
2. There would only be one instance of each number across a row
3. There would only be one number selected within a group
I could produce error messages to identify a missing number or the presence of duplicates but I can see no reliable way of 'correcting' the user input.
@Kenshin
I had assumed (despite the fact that the actual data was non-conformant) that
1. Each number [1-4] would be present within each row
2. There would only be one instance of each number across a row
3. There would only be one number selected within a group
I could produce error messages to identify a missing number or the presence of duplicates but I can see no reliable way of 'correcting' the user input.
Peter Bartholomew
Well-Known Member
This version has a test added to report duplicates and missing values in a row.
Or....................
1] In B14, copied down :
=IFERROR(INDEX(LOOKUP(COLUMN(B$4:Q$4),COLUMN(B$4:Q$4)/(B$3:Q$3<>""),B$3:Q$3),AGGREGATE(15,6,COLUMN(B$4:Q$4)-COLUMN(A$4)/((B$4:Q$4/(INDEX(B$5:Q$10,MATCH(A$14,A$5:A$10,0),0)="x"))=C14),COUNTIF(C$14:C14,C14))),"")
2] In C14, copied down :
=IFERROR(AGGREGATE(14,6,B$4:Q$4/(INDEX(B$5:Q$10,MATCH(A$14,A$5:A$10,0),0)="x"),ROWS($1:1)),"")
Regards
Bosco
1] In B14, copied down :
=IFERROR(INDEX(LOOKUP(COLUMN(B$4:Q$4),COLUMN(B$4:Q$4)/(B$3:Q$3<>""),B$3:Q$3),AGGREGATE(15,6,COLUMN(B$4:Q$4)-COLUMN(A$4)/((B$4:Q$4/(INDEX(B$5:Q$10,MATCH(A$14,A$5:A$10,0),0)="x"))=C14),COUNTIF(C$14:C14,C14))),"")
2] In C14, copied down :
=IFERROR(AGGREGATE(14,6,B$4:Q$4/(INDEX(B$5:Q$10,MATCH(A$14,A$5:A$10,0),0)="x"),ROWS($1:1)),"")
Regards
Bosco
Peter Bartholomew
Well-Known Member
Hi Bosco
Nice functionality! I just took the order {4;3;2;1} as fixed input but made it difficult to place the "x"s without creating 'duplicate' or 'missing' errors. Your idea of ranking the values returned by the "x"s makes using the sheet a far better experience. So much so that I set out to adapt my solution to match!
To avoid issues with duplicate values, I included the group index as a tie-breaker so the ranking
'order':
= IFERROR( LARGE( flag, {1;2;3;4} ), "" )
was based on 'flag' which I modified to read:
= IF( INDEX( data, MATCH( selected, name, 0 ), ) = "X", number+group/100 )
I hadn't really planned ahead so it was something of a surprise when the group/animal/team lookup
'pointer':
= 1 + FLOOR(MATCH(order,flag,0) - 1, 4)
went through without further change!
Nice functionality! I just took the order {4;3;2;1} as fixed input but made it difficult to place the "x"s without creating 'duplicate' or 'missing' errors. Your idea of ranking the values returned by the "x"s makes using the sheet a far better experience. So much so that I set out to adapt my solution to match!
To avoid issues with duplicate values, I included the group index as a tie-breaker so the ranking
'order':
= IFERROR( LARGE( flag, {1;2;3;4} ), "" )
was based on 'flag' which I modified to read:
= IF( INDEX( data, MATCH( selected, name, 0 ), ) = "X", number+group/100 )
I hadn't really planned ahead so it was something of a surprise when the group/animal/team lookup
'pointer':
= 1 + FLOOR(MATCH(order,flag,0) - 1, 4)
went through without further change!