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

 

[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,

Sorry for delay

Here is the example data where i need ranking from F1: F10, F12:F20, F23:F32 and so on

book1
	B	C	D	E	F	G	H	I	J
1	Capital One	17	17	16	17			B of A	45
2	Ally	3	3	4	2			Chase	40
3	B of A	49	43	44	45			Wells Fargo	37
4	Chase	45	42	42	40			Citibank	19
5	Wells Fargo	41	42	37	37			Capital One	17
6	USAA	3	3	3	3			PNC	9
7	Citibank	22	21	19	19			USBank	9
8	PNC	9	9	9	9			USAA	3
9	USBank	11	9	10	9			Ally	2
10
11
12	Capital One	34	37	41	42			Chase	73
13	Ally	19	20	16	17			B of A	71
14	B of A	71	71	71	71			Wells Fargo	64
15	Chase	69	71	72	73			PNC	43
16	Wells Fargo	66	64	67	64			Capital One	42
17	USAA	22	19	24	16			Citibank	38
18	Citibank	34	38	33	38			USBank	31
19	PNC	40	37	35	43			Ally	17
20	USBank	32	28	32	31			USAA	16
21
22
23	Capital One	69	67	68	65
24	Ally	34	34	34	32
25	B of A	73	70	70	69
26	Chase	73	70	70	67
27	Wells Fargo	70	68	66	65
28	USAA	42	41	41	40
29	Citibank	64	62	63	59
30	PNC	39	39	38	38
31	USBank	46	44	44	43
32
33
34	Capital One	26.5	27.3	27.1	25.9
35	Ally	10.6	10.6	10.6	11.4
36	B of A	22.5	22.5	22.5	23.6
37	Chase	29	27.9	27	27.6
38	Wells Fargo	21.5	20.7	19.6	20.2
39	USAA	13.5	13.6	13.6	13.8
40	Citibank	20	19.3	19.4	19.2
41	PNC	10.8	10.2	10.2	10.8
42	USBank	12.1	11.9	11.4	13
43
44
45
46
47	Capital One	32.3	30.5	29.7	30.2
48	Ally	12.8	11.1	11.5	12.7
49	B of A	30.3	29	28.9	29.9
50	Chase	36.5	32.9	32.6	31.8
51	Wells Fargo	26.7	23.1	24.6	24.3
52	USAA	14.6	13.9	13.8	13.5
53	Citibank	25.7	23.1	23.8	23
54	PNC	15.2	12.7	12.5	13.8
55	USBank	16.9	14.5	15	14.7
56
57	Capital One	-	29	29.2	28.6
58	Ally	-	10.5	10.5	11.5
59	B of A	-	23.3	24.3	23.7
60	Chase	-	30	29.2	27.9
61	Wells Fargo	-	18.4	19.1	19
62	USAA	-	11.9	12.4	12.2
63	Citibank	-	19.2	19.8	18.6
64	PNC	-	9.6	9	10.4
65	USBank	-	11.9	11.9	12.2
66
67	Capital One	26.5	27.3	27.1	25.9
68	Ally	10.6	10.6	10.6	11.4
69	B of A	22.5	22.5	22.5	23.6
70	Chase	29	27.9	27	27.6
71	Wells Fargo	21.5	20.7	19.6	20.2
72	USAA	13.5	13.6	13.6	13.8
73	Citibank	20	19.3	19.4	19.2
74	PNC	10.8	10.2	10.2	10.8
75	USBank	12.1	11.9	11.4	13
76
77	Capital One	26.5	27.3	27.1	25.9
78	Ally	10.6	10.6	10.6	11.4
79	B of A	22.5	22.5	22.5	23.6
80	Chase	29	27.9	27	27.6
81	Wells Fargo	21.5	20.7	19.6	20.2
82	USAA	13.5	13.6	13.6	13.8
83	Citibank	20	19.3	19.4	19.2
84	PNC	10.8	10.2	10.2	10.8
85	USBank	12.1	11.9	11.4	13
86
87
88	Capital One	53	55	56	56
89	Ally	55	47	45	49
90	B of A	49	47	50	54
91	Chase	51	52	51	56
92	Wells Fargo	42	46	47	45
93	USAA	48	45	50	45
94	Citibank	38	36	38	39
95	PNC	44	40	39	41
96	USBank	39	40	40	42
97
98
99
100
101	Capital One	53.2	54.8	55.7	55.8
102	Ally	54.6	47.3	45.3	49.2
103	B of A	49	47.3	50.5	54.4
104	Chase	51	52.3	51.3	56.2
105	Wells Fargo	41.8	46.5	47.2	45
106	USAA	48.3	45.2	50.4	45.2
107	Citibank	38.1	35.6	38.1	39.1
108	PNC	43.6	40.2	39.2	40.8
109	USBank	39.1	40.1	39.5	41.6
110
111
112	Capital One	63.2	58.6	61.1	63.4
113	Ally	52.7	48.5	52.4	49.7
114	B of A	64.6	65	64.4	65.3
115	Chase	68.4	68	67.6	67.4
116	Wells Fargo	63.6	60.4	61	57.9
117	USAA	55	47.9	55.1	47.9
118	Citibank	46.8	49.3	47.6	50.7
119	PNC	57.6	50.3	55.7	60.6
120	USBank	53.8	50.2	49.7	56.6
121
122
123	Capital One	-	61.2	59.2	60.7
124	Ally	-	49.9	49.8	55.2
125	B of A	-	62	60.8	64.6
126	Chase	-	65.4	63.7	65
127	Wells Fargo	-	57.6	61.5	54.5
128	USAA	-	50.7	53.7	46.8
129	Citibank	-	47.9	48.5	46.2
130	PNC	-	46.8	49.4	53.6
131	USBank	-	48.4	46.1	51.6
132
133
134	Capital One	60.1	58.6	56.8	61.8
135	Ally	55.5	49.9	45.3	57.3
136	B of A	57.3	55	57.2	60.8
137	Chase	59.1	59	54.7	61
138	Wells Fargo	50.8	52.4	50.9	53
139	USAA	57.8	50.9	51.1	51.2
140	Citibank	43.3	45	41.6	44
141	PNC	48.9	42.6	44	49.2
142	USBank	44.6	43.9	43.1	45.1
143
144
145	Capital One	32	32	31	31
146	Ally	10	9	9	9
147	B of A	21	22	22	22
148	Chase	33	33	32	32
149	Wells Fargo	16	16	16	17
150	USAA	11	11	11	12
151	Citibank	20	20	20	19
152	PNC	9	8	8	9
153	USBank	11	10	10	11
154
155
156	Capital One	71	70	69	74
157	Ally	59	53	58	62
158	B of A	66	67	69	74
159	Chase	72	74	72	74
160	Wells Fargo	61	60	63	62
161	USAA	60	58	57	61
162	Citibank	51	51	50	57
163	PNC	53	52	48	55
164	USBank	47	53	51	54
165
166
167
168
169	Capital One	38	37	38	37
170	Ally	19	18	18	18
171	B of A	56	54	53	53
172	Chase	52	49	49	48
173	Wells Fargo	52	50	48	48
174	USAA	25	25	25	25
175	Citibank	43	41	40	39
176	PNC	27	26	25	24
177	USBank	33	30	30	30
178
179	Capital One	37	35	35	34
180	Ally	18	18	17	18
181	B of A	53	53	52	51
182	Chase	49	47	46	45
183	Wells Fargo	50	48	47	47
184	USAA	24	25	25	24
185	Citibank	41	40	39	38
186	PNC	26	25	24	24
187	USBank	31	29	30	29
188
189	Capital One	55	56	55	58
190	Ally	57	54	54	54
191	B of A	76	77	76	76
192	Chase	71	69	70	71
193	Wells Fargo	74	74	73	74
194	USAA	59	61	62	62
195	Citibank	67	66	65	67
196	PNC	68	66	66	65
197	USBank	71	68	69	69
Sheet1

 

[Peter_SSs]

What are the answers to post #10 questions?

 

[sksanjeev786]

What are the answers to post #10 questions?

Hi Peter,

Thank you so much for looking into it.

1. Will this sheet still have the ranking as per the original question? (I need ranking for column B vs F column and out can be in Column IJ (yellow highlighted)
2. Does this need to be without a helper column? If so, the formulas will be much more complicated. ( Anything can work since I need ranking for each segment)

 

[Peter_SSs]

Thanks.
Could something like this work for you?

sksanjeev786.xlsm
	B	C	D	E	F	G	H	I	J
1							0
2	Capital One	17	17	16	17		1	B of A	45
3	Ally	3	3	4	2		1	Chase	40
4	B of A	49	43	44	45		1	Wells Fargo	37
5	Chase	45	42	42	40		1	Citibank	19
6	Wells Fargo	41	42	37	37		1	Capital One	17
7	USAA	3	3	3	3		1	PNC	9
8	Citibank	22	21	19	19		1	USBank	9
9	PNC	9	9	9	9		1	USAA	3
10	USBank	11	9	10	9		1	Ally	2
11
12
13	Capital One	34	37	41	42		2	Chase	73
14	Ally	19	20	16	17		2	B of A	71
15	B of A	71	71	71	71		2	Wells Fargo	64
16	Chase	69	71	72	73		2	PNC	43
17	Wells Fargo	66	64	67	64		2	Capital One	42
18	USAA	22	19	24	16		2	Citibank	38
19	Citibank	34	38	33	38		2	USBank	31
20	PNC	40	37	35	43		2	Ally	17
21	USBank	32	28	32	31		2	USAA	16
22
23
24	Capital One	69	67	68	65		3	B of A	69
25	Ally	34	34	34	32		3	Chase	67
26	B of A	73	70	70	69		3	Capital One	65
27	Chase	73	70	70	67		3	Wells Fargo	65
28	Wells Fargo	70	68	66	65		3	Citibank	59
29	USAA	42	41	41	40		3	USBank	43
30	Citibank	64	62	63	59		3	USAA	40
31	PNC	39	39	38	38		3	PNC	38
32	USBank	46	44	44	43		3	Ally	32
33
Sheet1

Cell Formulas
Range	Formula
H2:H33	H2	=IF(B2="","",IF(B1="",LOOKUP(9^9,H$1:H1)+1,H1))
I2:I33	I2	=IF(H2="","",INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$43)/((F$2:F$43=J2)*(H$2:H$43=H2)),COUNTIFS(J$2:J2,J2,H$2:H2,H2))))
J2:J33	J2	=IF(B2="","",AGGREGATE(14,6,F$2:F$43/(H$2:H$43=H2),COUNTIF(H$2:H2,H2)))

 

[sksanjeev786]

Thanks.
Could something like this work for you?

sksanjeev786.xlsm
	B	C	D	E	F	G	H	I	J
1							0
2	Capital One	17	17	16	17		1	B of A	45
3	Ally	3	3	4	2		1	Chase	40
4	B of A	49	43	44	45		1	Wells Fargo	37
5	Chase	45	42	42	40		1	Citibank	19
6	Wells Fargo	41	42	37	37		1	Capital One	17
7	USAA	3	3	3	3		1	PNC	9
8	Citibank	22	21	19	19		1	USBank	9
9	PNC	9	9	9	9		1	USAA	3
10	USBank	11	9	10	9		1	Ally	2
11
12
13	Capital One	34	37	41	42		2	Chase	73
14	Ally	19	20	16	17		2	B of A	71
15	B of A	71	71	71	71		2	Wells Fargo	64
16	Chase	69	71	72	73		2	PNC	43
17	Wells Fargo	66	64	67	64		2	Capital One	42
18	USAA	22	19	24	16		2	Citibank	38
19	Citibank	34	38	33	38		2	USBank	31
20	PNC	40	37	35	43		2	Ally	17
21	USBank	32	28	32	31		2	USAA	16
22
23
24	Capital One	69	67	68	65		3	B of A	69
25	Ally	34	34	34	32		3	Chase	67
26	B of A	73	70	70	69		3	Capital One	65
27	Chase	73	70	70	67		3	Wells Fargo	65
28	Wells Fargo	70	68	66	65		3	Citibank	59
29	USAA	42	41	41	40		3	USBank	43
30	Citibank	64	62	63	59		3	USAA	40
31	PNC	39	39	38	38		3	PNC	38
32	USBank	46	44	44	43		3	Ally	32
33
Sheet1

Cell Formulas
Range	Formula
H2:H33	H2	=IF(B2="","",IF(B1="",LOOKUP(9^9,H$1:H1)+1,H1))
I2:I33	I2	=IF(H2="","",INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$43)/((F$2:F$43=J2)*(H$2:H$43=H2)),COUNTIFS(J$2:J2,J2,H$2:H2,H2))))
J2:J33	J2	=IF(B2="","",AGGREGATE(14,6,F$2:F$43/(H$2:H$43=H2),COUNTIF(H$2:H2,H2)))

Wow...!!! Simply Awesome...!!!

Thank you so much, Peter...for your hard work and support on this

Regards
Sanjeev

 

[Peter_SSs]

Cheers. You are welcome.

 

[sksanjeev786]

Cheers. You are welcome.

Hi Peter,

Hope you are doing well

Everything is working very well in the formula you have provided but I have one more condition

like can I have a secondary ranking if if data is the same in Column F (i.e.PNC and US Bank are 9 )then need to check from Column E (US bnak 10 and PNC 9) so data in column J7 and J8 ranking data should be (US bank 9 and PNC 9) # Post 14 ref.

Regards
Sanjeev