Formula - Position No In Unique List

Herbiec09

Active Member
Joined
Aug 29, 2006
Messages
250
Hi All,

As shown below, column A & B are my source data. D2 is a drop down to select a month, and E2 is a drop down to select a client code. What I am looking for is a formula that will go into cell F2, that tells me the position no i.e. rank that the client code is from a unique listing of the client nos available in that month.

i.e. formula will say

Step 1) How many unique client nos do I have in the selected month.
Step 2) In that list of unique client nos, what position no is the selected client no.

Excel Workbook
ABCDEF
1Client CodeMonthMonthClient CodePosition No.
22JanJan82
32Jan
48Jan
58Jan
68Jan
711Jan
811Jan
911Jan
1011Jan
1112Jan
1212Jan
132Feb
142Feb
158Feb
168Feb
178Feb
1811Feb
1911Feb
2011Feb
2112Feb
2212Feb
2312Feb
2412Feb
Sheet1


Thank you

H
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Assuming that the data is sorted as in your example:


Excel 2010
ABCDEF
1Client CodeMonthMonthClient CodePosition No.
22JanJan82
32Jan
48Jan
58Jan
68Jan
711Jan
811Jan
911Jan
1011Jan
1112Jan
1212Jan
132Feb
143Feb
158Feb
168Feb
178Feb
1811Feb
1911Feb
2011Feb
2112Feb
2212Feb
2312Feb
2412Feb
Sheet1
Cell Formulas
RangeFormula
F2{=SUM(IF(B2:B24=D2,IF(A2:A24<>A3:A25,--(A2:A24<=E2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Assuming that the data is sorted as in your example:


Excel 2010
ABCDEF
1Client CodeMonthMonthClient CodePosition No.
22JanJan82
32Jan
48Jan
58Jan
68Jan
711Jan
811Jan
911Jan
1011Jan
1112Jan
1212Jan
132Feb
143Feb
158Feb
168Feb
178Feb
1811Feb
1911Feb
2011Feb
2112Feb
2212Feb
2312Feb
2412Feb
Sheet1
Cell Formulas
RangeFormula
F2{=SUM(IF(B2:B24=D2,IF(A2:A24<>A3:A25,--(A2:A24<=E2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Thanks Paul,

This works brilliantly, would you mind walking me through the formula, just so that I get a better understanding iin constructing something similar for myself in future in particular the "--"

Thank you

H
 
Upvote 0
The -- coerces TRUE/FALSE to 1/0. Use the Evaluate Formula to see how it works.

Thanks for the reply Paul, however my data has changed a bit and I now need to consider another variable in the form of a year column. How can I adjust the formula to take into consideration this new variable?. I have pasted new data below:

Excel Workbook
ABCDEFGH
1Client CodeMonthYearMonthYearClient CodePosition No.
22Jan2012Jan201381
32Jan2012
48Jan2013
58Jan2013
68Jan2013
711Jan2013
811Jan2013
911Jan2014
1011Jan2014
1112Jan2014
1212Jan2014
132Feb2014
142Feb2014
158Feb2014
168Feb2014
178Feb2014
1811Feb2014
1911Feb2014
2011Feb2014
2112Feb2014
2212Feb2014
2312Feb2014
2412Feb2014
Sheet1


Thanks
 
Upvote 0
You can add another condition:

=SUM(IF(B2:B24=E2,IF(C2:C24=F2,IF(A2:A24<>A3:A25,--(A2:A24<=G2)))))

Don't forget to confirm with Ctrl+Shift+Enter.

By the way my name is Andrew not Paul.
 
Upvote 0
You can add another condition:

=SUM(IF(B2:B24=E2,IF(C2:C24=F2,IF(A2:A24<>A3:A25,--(A2:A24<=G2)))))

Don't forget to confirm with Ctrl+Shift+Enter.

By the way my name is Andrew not Paul.

Thank you very much Andrew, I appreciate, my apologies in getting you name wrong.

H
 
Upvote 0
Thank you very much Andrew, I appreciate, my apologies in getting you name wrong.

H

Hi Andrew,

Sorry to be a pain, I have translated the formula you provided into my data, and am getting a #N/A. I am not sure if this is because my source data is coming from another workbook and the way I have defined the links may not be quite right.

I have translated the formula you provided as follows:

=SUM(IF('[Chemistry Source Data.xlsm]Data'!$E$2:$E$40000=LEFT(E3,3),IF('[Chemistry Source Data.xlsm]Data'!$O$2:$O$40000=E4,IF('[Chemistry Source Data.xlsm]Data'!$B$2:$B$40000<>'[Chemistry Source Data.xlsm]Data'!$B$3:$B$41000,--('[Chemistry Source Data.xlsm]Data'!$B$2:$B$40000<=E2)))))

Would you be able to tell what could be causing the the #N/A?

Thanks

H
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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