Ranking data and Statments

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
873
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I am trying to rank columns "Q" and "R" based on the ref. "C" and "I"
But getting some error can anyone please help me on this

FS1.xlsx
CDEFGHIJKLMNOPQR
256AD-13 SERENA_CAR , Offers Quick Relief For Migraines :16571671TOP 2 BOX (NET)893691#VALUE!####
257AD-13 SERENA_CAR , Offers Complete Relief From Migraines :16791693TOP 2 BOX (NET)--2#VALUE!####
258AD-13 SERENA_CAR , Is A New Way To Treat Migraines :17011715TOP 2 BOX (NET)883393#VALUE!####
259AD-13 SERENA_CAR , Is Effective Even If You Take It A Few Hours After Your Migraine Starts :17231737TOP 2 BOX (NET)823394#VALUE!####
260AD-13 SERENA_CAR , Has Low Side Effects :17451759TOP 2 BOX (NET)803695
261AD-13 SERENA_CAR , Can Be Taken Without Worrying About Where You Are Or What You Are Doing :17671781TOP 2 BOX (NET)913396
262AD-13 SERENA_CAR , Offers Pain Freedom (Zero-Pain) :17891803TOP 2 BOX (NET)793397
263AD-13 SERENA_CAR , Stops Your Migraine In Its Tracks :18111825TOP 2 BOX (NET)893398
264AD-13 SERENA_CAR , 'Works To Relieve My Most Debilitating Migraine Symptoms (Light Sensitivity, Sound Sensitivity, Or Nausea)' :18331847TOP 2 BOX (NET)813399
265AD-13 SERENA_CAR , Is Effective With Just One Dose :18551869TOP 2 BOX (NET)8936910
266AD-13 SERENA_CAR , 'Provides Relief That Lasts Long, Without The Migraine Coming Back' :18771891TOP 2 BOX (NET)8233911
267AD-13 SERENA_CAR , Provides Affordable Relief For As Little As $1 Per Pill :18991913TOP 2 BOX (NET)577912
268AD-13 SERENA_CAR , Provides Affordable Relief For As Little As $0 Per Pill :19211935TOP 2 BOX (NET)6229013
269AD-13 SERENA_CAR , 'Is An Anytime, Anywhere Migraine Medication' :19431957TOP 2 BOX (NET)9329014
270AD-13 SERENA_CAR , Gets Right To Work To Prevent Migraine Attacks :19651979TOP 2 BOX (NET)--15
271AD-13 SERENA_CAR , Keeps Migraine Attacks Away Over Time :19872001TOP 2 BOX (NET)--16
272AD-13 SERENA_CAR , Works With Your Body's Chemistry :20092023TOP 2 BOX (NET)--17
273AD-13 SERENA_CAR , Is Proven To Both Treat And Prevent Migraines :20312045TOP 2 BOX (NET)--18
274AD-13 SERENA_CAR , Can Help Prevent Migraine Attacks :20532067TOP 2 BOX (NET)--19
275AD-13 SERENA_CAR , Lets Me Take Control Of My Migraines :20752089TOP 2 BOX (NET)--20
276AD-13 SERENA_CAR , Quickly Prevents Migraine Attacks :20972111TOP 2 BOX (NET)--21
277AD-13 SERENA_CAR , Helps Prevent Migraine Attacks From The Inside :21192133TOP 2 BOX (NET)--22
278AD-13 SERENA_CAR , 'Blocks The CGRP, A Protein Believed To Play A Role In Migraine Attacks' :21412155TOP 2 BOX (NET)703023
279AD-13 SERENA_CAR , Is A Quick Dissolve Tablet That Starts Fast And Lasts :21632177TOP 2 BOX (NET)--24
280AD-13 SERENA_CAR , Works Differently Than Other Medications :21852199TOP 2 BOX (NET)8330
281AD-14 NURTECODT_WHOOPIEGOLDBERG ONDERFUL_30s , Offers Quick Relief For Migraines :22072221TOP 2 BOX (NET)86304
FS_other MBNA
Cell Formulas
RangeFormula
E256:E281E256=MATCH($C256,INDIRECT("'"&$A256&$B256&"'!A:A"),0)
F256:F281F256=MATCH($A$5,INDIRECT("'"&$A256&$B256&"'!B"&$E256&":B900000"),0)+$E256-1
Q256:R259Q256=INDEX(FILTER(SORTBY($C$256:$I$281,I$256:I$281),-1),TRANSPOSE(({1;7})))
P256:P279P256=ROW()-255
C257:C281C257=INDEX(Sheet1!F:F,'FS_other MBNA'!AA257,1)
I256:I281I256=IFERROR(INDEX(INDIRECT("'"&$A256&$B256&"'!A"&$E256&":IV"&$F256&""),MATCH($H256,INDIRECT("'"&$A256&$B256&"'!B"&$E256&":B"&$F256&""),0)+1,I$5)*100,INDEX(INDIRECT("'"&$A256&$B256&"'!A"&$E256&":IV"&$F256&""),MATCH($H256,INDIRECT("'"&$A256&$B256&"'!B"&$E256&":B"&$F256&""),0)+1,I$5))
L256:L281L256=(INDEX(INDIRECT("'"&$A256&$B256&"'!A"&$E256&":IV"&$F256&""),MATCH($L$254,INDIRECT("'"&$A256&$B256&"'!B"&$E256&":B"&$F256&""),0)-2,L$283))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C256:C281Cell Valuecontains "offers quick"textNO
Cells with Data Validation
CellAllowCriteria
C256List=$AB$256:$AB$268
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
In that case clear columns Q & R and use
Excel Formula:
=INDEX(SORTBY($C$256:$I$281,I$256:I$281,-1),SEQUENCE(ROWS(C256:C281)),{1,7})
 
Upvote 0
Solution

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top