Chronologically Number conditionally selected records using arrays

[ojaskulkarni]

Hi Chandoo Ninjas,

I need help with a complex situation. I have tried many attempts but not helping!!

I have a sheet (see attached) with the following columns:
Sales Rep - contains names of sales reps
Cust Name - contains names - not relevant to the problem
Record ID - contains unique, distinct record ids
Date - contains dates (multiple years)
CountCondition - contains "Eligible" or "Not Eligible"
Outcome - This needs to have a record number according to the conditions defined

The approach I am using right now isOR what I want to do) (but not helping)
Using defined conditions, create a dynamic array of records
Sort dates in this array chronologically
For each Sales Rep AND for each calendar quarter (in the array) number records from 1 to n (where n=max records for the particular sales rep in a particular quarter)

The work-around I use is to create a pivot of the conditions (once a quarter) and manually number the records. However, the challenge is I cannot do this real-time! If the Date or CountCondition changes, the numbering has to be done manually again.
Or if New records are added, all numbering has to be done again.

The record numbering is used further to associate sales performance stacks, so this is not the end of game! The original file has more than 4000 records and across multiple years, so in-cell arrays are only optimal solution. Pivot table makes process cumbersome!

Would appreciate if anyone can help.

Thanks a ton for trying!

-Ojas

 

[NARAYANK991]

Hi Kulkarni ,

If we can eliminate words which do not add to the explanation , it will help.

Can you confirm whether the following summary is correct ?

1. Your data will consist of 5 columns of data , columns A through E.

2. The data will not and cannot be sorted.

3. Column D will have dates from multiple years.

4. Column E will have 2 statuses viz. Eligible , Not Eligible.

5. Column A will have the identities of Sales Reps.

6. Column C will have unique identification numbers.

What is required :

Numbers starting from 1 for each Sales Rep.

Only rows which have the status in column E as Eligible will be numbered ; those having a status of Not Eligible will be excluded from the numbering.

Numbers will increment chronologically within a quarter. Does this mean that when a quarter is over , and another quarter data starts , the numbering will restart from 1 ?

For dates within a quarter , the records will be numbered based on the unique identification numbers in column C.

Please confirm / clarify all of the above.

Are you looking for a formula solution or a VBA solution ?

Narayan

 

[ojaskulkarni]

Hi Kulkarni ,

If we can eliminate words which do not add to the explanation , it will help.

Can you confirm whether the following summary is correct ?

1. Your data will consist of 5 columns of data , columns A through E.
[OJAS] These 5 columns are relevant for the logic. The entire data is a defined table with other logic parameters not relevant for this problem.

2. The data will not and cannot be sorted.
[OJAS] Yes.

3. Column D will have dates from multiple years.
[OJAS] Yes.

4. Column E will have 2 statuses viz. Eligible , Not Eligible.
[OJAS] Yes.

5. Column A will have the identities of Sales Reps.
[OJAS] Yes. Names. multiple entries across years

6. Column C will have unique identification numbers.
[OJAS] Yes. Unique and Distinct.

What is required :

Numbers starting from 1 for each Sales Rep.

Only rows which have the status in column E as Eligible will be numbered ; those having a status of Not Eligible will be excluded from the numbering.
[OJAS] Yes.

Numbers will increment chronologically within a quarter. Does this mean that when a quarter is over , and another quarter data starts , the numbering will restart from 1 ?
[OJAS] Yes. Every quarter the numbering will restart from 1.

For dates within a quarter , the records will be numbered based on the unique identification numbers in column C.
[OJAS] Yes. but for each Sales Rep.

Please confirm / clarify all of the above.

Are you looking for a formula solution or a VBA solution ?
[OJAS] Formula in-cell

Narayan