Ranking Array need to move as per the data

[sksanjeev786]

Hi Team,

I am using a Rank formula and i have a lot of data in columns A and B. so I have applied the rank formula in Column C1 :C9 and I just copied that formula in C12 to C20 so the Rank array is still in C1:C9 so I need to drag manually the array from C1: C9 to C12:C20. i have lot of data so need to drag for each Array will take time..

so do we have any option where Array will also come to the new data file instead of doing manual?

book1
	A	B	C
1	2	Ally Bank	9
2	45	Bank of America	1
3	17	Capital One/Capital One Bank/Capital One 360 (Q117+)	5
4	40	Chase	2
5	37	Wells Fargo	3
6	3	USAA	8
7	19	Citibank	4
8	9	PNC Bank	6
9	9	USBank	6
10
11
12	17	Ally Bank	5
13	71	Bank of America	#N/A
14	42	Capital One/Capital One Bank/Capital One 360 (Q117+)	#N/A
15	73	Chase	#N/A
16	64	Wells Fargo	#N/A
17	16	USAA	#N/A
18	38	Citibank	#N/A
19	43	PNC Bank	#N/A
20	31	USBank	#N/A
Sheet1

Cell Formulas
Range	Formula
C1:C9,C12:C20	C1	=RANK($A1,$A$1:$A$9,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C12:C20	Cell Value	top 1 values	text	NO
B12:B20	Cell Value	top 2 values	text	NO
B12:B20	Cell Value	top 1 values	text	NO
B1:B9	Cell Value	top 2 values	text	NO
B1:B9	Cell Value	top 1 values	text	NO
A1:C9	Cell Value	top 1 values	text	NO
A12:B20	Cell Value	top 1 values	text	NO

 

[hagia_sofia]

Hello, if you are not against a helper column, please test this:

1) helper column to be inserted in D1:

=LET(
array,A1:A20,
a,ISNUMBER(array),
b,SCAN(0,a,LAMBDA(a,b,IF(b=TRUE,a+1,0))),
c,SCAN(0,--(b=1),LAMBDA(a,b,a+b)),
IF(a*c=0,"",a*c))

2) rank to be inserted into C1:

=LET(
array,A1:A20,
group,D1#,
DROP(REDUCE("",SEQUENCE(ROWS(array)),LAMBDA(a,b,VSTACK(a,IF(INDEX(array,b,0)=0,"",COUNTIFS(group,INDEX(group,b,0),array,">"&INDEX(array,b,0))+1)))),1))

 

[sksanjeev786]

Hello, if you are not against a helper column, please test this:

1) helper column to be inserted in D1:

=LET(
array,A1:A20,
a,ISNUMBER(array),
b,SCAN(0,a,LAMBDA(a,b,IF(b=TRUE,a+1,0))),
c,SCAN(0,--(b=1),LAMBDA(a,b,a+b)),
IF(a*c=0,"",a*c))

2) rank to be inserted into C1:

=LET(
array,A1:A20,
group,D1#,
DROP(REDUCE("",SEQUENCE(ROWS(array)),LAMBDA(a,b,VSTACK(a,IF(INDEX(array,b,0)=0,"",COUNTIFS(group,INDEX(group,b,0),array,">"&INDEX(array,b,0))+1)))),1))

Hi Sofia,

Getting the error i have checked with 2nd formula

book1
	A	B	C	D
1	2	Ally Bank		#VALUE!
2	45	Bank of America		#VALUE!
3	17	Capital One/Capital One Bank/Capital One 360 (Q117+)		#VALUE!
4	40	Chase		#VALUE!
5	37	Wells Fargo		#VALUE!
6	3	USAA		#VALUE!
7	19	Citibank		#VALUE!
8	9	PNC Bank		#VALUE!
9	9	USBank		#VALUE!
10				#VALUE!
11				#VALUE!
12	17	Ally Bank		#VALUE!
13	71	Bank of America		#VALUE!
14	42	Capital One/Capital One Bank/Capital One 360 (Q117+)		#VALUE!
15	73	Chase		#VALUE!
16	64	Wells Fargo		#VALUE!
17	16	USAA		#VALUE!
18	38	Citibank		#VALUE!
19	43	PNC Bank		#VALUE!
20	31	USBank		#VALUE!
Sheet1

Cell Formulas
Range	Formula
D1:D20	D1	=LET( array,A1:A20, group,B1, DROP(REDUCE("",SEQUENCE(ROWS(array)),LAMBDA(a,b,VSTACK(a,IF(INDEX(array,b,0)=0,"",COUNTIFS(group,INDEX(group,b,0),array,">"&INDEX(array,b,0))+1)))),1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C12:C20	Cell Value	top 1 values	text	NO
B12:B20	Cell Value	top 2 values	text	NO
B12:B20	Cell Value	top 1 values	text	NO
B1:B9	Cell Value	top 2 values	text	NO
B1:B9	Cell Value	top 1 values	text	NO
A1:C9	Cell Value	top 1 values	text	NO
A12:B20	Cell Value	top 1 values	text	NO

 

[hagia_sofia]

Hello, you need to first insert the formula ad 1) into D1 and only then this one (ad 2)) into C1 - the latter will not work without the former.

 

[sksanjeev786]

Hello, you need to first insert the formula ad 1) into D1 and only then this one (ad 2)) into C1 - the latter will not work without the former.

Perfect! thank you so much.... now it is working

 

[Peter_SSs]

If you wanted to do it without the helper column but are happy to insert a blank row at the top you could try this.

24 08 15.xlsm
	A	B	C
1
2	2	Ally Bank	9
3	45	Bank of America	1
4	17	Capital One/Capital One Bank/Capital One 360 (Q117+)	5
5	40	Chase	2
6	37	Wells Fargo	3
7	3	USAA	8
8	19	Citibank	4
9	9	PNC Bank	6
10	9	USBank	6
11
12
13	17	Ally Bank	8
14	71	Bank of America	2
15	42	Capital One/Capital One Bank/Capital One 360 (Q117+)	5
16	73	Chase	1
17	64	Wells Fargo	3
18	16	USAA	9
19	38	Citibank	6
20	43	PNC Bank	4
21	31	USBank	7
Rank

Cell Formulas
Range	Formula
C2:C21	C2	=IF(A2="","",RANK(A2,INDEX(A$1:A2,AGGREGATE(14,6,(ROW(A$1:A1)-ROW(A$1)+1)/(A$1:A1=""),1)+1):INDEX(A2:A$1000,MATCH(TRUE,INDEX(A2:A$1000="",0),0)-1)))

 

[sksanjeev786]

If you wanted to do it without the helper column but are happy to insert a blank row at the top you could try this.

24 08 15.xlsm
	A	B	C
1
2	2	Ally Bank	9
3	45	Bank of America	1
4	17	Capital One/Capital One Bank/Capital One 360 (Q117+)	5
5	40	Chase	2
6	37	Wells Fargo	3
7	3	USAA	8
8	19	Citibank	4
9	9	PNC Bank	6
10	9	USBank	6
11
12
13	17	Ally Bank	8
14	71	Bank of America	2
15	42	Capital One/Capital One Bank/Capital One 360 (Q117+)	5
16	73	Chase	1
17	64	Wells Fargo	3
18	16	USAA	9
19	38	Citibank	6
20	43	PNC Bank	4
21	31	USBank	7
Rank

Cell Formulas
Range	Formula
C2:C21	C2	=IF(A2="","",RANK(A2,INDEX(A$1:A2,AGGREGATE(14,6,(ROW(A$1:A1)-ROW(A$1)+1)/(A$1:A1=""),1)+1):INDEX(A2:A$1000,MATCH(TRUE,INDEX(A2:A$1000="",0),0)-1)))

Thank you So much Peter for help me with this

Can I get an automatic ranking based on the column A and G so that I can output on I and J column along with above conditions

book2
	A	B	C	D	E	F	G	H	I	J
2	Capital One	22	21.2	21	21.4	22	21.2		Capital One	21.2
3	Ally	8.4	8.4	8.5	8.4	8.4	8.4		Chase	17.7
4	B of A	15.5	16.2	14.7	14.9	15.5	16.2		B of A	16.2
5	Chase	17.7	17.7	18.6	18.6	17.7	17.7		Wells Fargo	12.5
6	Wells Fargo	12.4	12.5	11.7	12.2	12.4	12.5		Citibank	12.2
7	USAA	10	9.5	9.1	9.9	10	9.5		USAA	9.5
8	Citibank	12.2	12.2	11.7	12.1	12.2	12.2		Ally	8.4
9	PNC	6.6	7.1	7.1	6.7	6.6	7.1		USBank	7.4
10	USBank	7.7	7.4	7.8	8.1	7.7	7.4		PNC	7.1
Sheet2

 

[Peter_SSs]

Is this a separate question with just a single group of data or are there really several groups with space between each group like earlier in the thread?
If several groups please give sample data and expected results for at least two groups.

 

[sksanjeev786]

Is this a separate question with just a single group of data or are there really several groups with space between each group like earlier in the thread?
If several groups please give sample data and expected results for at least two groups.

Hi Peter,

It is the same questions related to the above I need ranking from A and G columns (output is I and J column ) and the array is now is rows 2- 10 and same data I have from 13-21 and 23-31.

so instead of changing array can I get in one go.... with out changing the arrow....

Regards
Sanjeev

 

[Peter_SSs]

1. Will this sheet still have the ranking as per the original question?
2. Does this need to be without a helper column? If so, the formulas will be much more complicated.