Call Dates Sequencing by serial number

[raiyanimv]

Hello everyone,

I have calls reported on different days of the month with gaps between two calls. I need to sequence the dates. For further clarification, i have attached the sample file with raw data and desired outcome tables. Anyone can help please....

Thanks,
Mitul

 

[Chihiro]

Something like below.
=IFERROR(INDEX($D6:$R6,SMALL(IF(ISNUMBER($D6:$R6),COLUMN($D$6:$R$6)-3),COLUMN(A1))),"")

Confirmed as array (CSE). Copy across and down.

 

[raiyanimv]

Thank you Chihiro, somehow i am getting '1/0/1900' error with CSE. Not sure what would be the issue.

 

[Chihiro]

That means it's returning 0. 0 in date format is 1/0/1900.

Working fine on my end.

 

[raiyanimv]

True, it works fine in the sample file. But not working in my master file. Thanks for the prompt reply....

 

[Chihiro]

In that case, I'm guessing master file has invisible "0" or something else in the source table.

Try adding *($D6:$R7<>0) to the condition and see what happens.
=IFERROR(INDEX($D6:$R6,SMALL(IF(ISNUMBER($D6:$R6)*($D6:$R6<>0),COLUMN($D$6:$R$6)-3),COLUMN(A1))),"")

 

[raiyanimv]

Thanks Chihiro,

I just found out what the issue is. I had to converted my raw data into a pivot first so that i have longitudinal view of calls by individual rep. The value filed (as shown in the snap shot attached) is as 'sum'. By default where ever there is blank, cell has 0 value (like you mentioned) and it gives my wrong result. I tried with the revised option you mentioned, but still getting the same error. Not sure if there is any other option to correct this...

Best,
Mitul

 

[Chihiro]

Actually, it's easier to do analysis from raw data (instead of pivoted cross tab format). If you can upload sample file with raw data set up. I can take a look at it.

 

[raiyanimv]

here is the raw data. There is differentiation on type of calls i am not looking for granular outcome. I am only interested in sequencing by order of first call to last call...

 

[raiyanimv]

Just a nadditional info - i have 7,600 reps and raw data has 70,000 entries

 

[Chihiro]

Do you have access to PowerQuery?

 

[raiyanimv]

No

 

[Chihiro]

Here's sample of how it can be done from raw data via Formula.
Extract unique list of Rep (in B3)
=IFERROR(INDEX(Sheet1!$B$3:$B$64,MATCH(0,COUNTIF($B$2:B3,Sheet1!$B$3:$B$64),0)),"")

To sort dates (in C3 down & across)
=IFERROR(SMALL(IF(Sheet1!$B$3:$B$64=$B3,Sheet1!$D$3:$D$64),COLUMN(A1)),"")

Both formulas confirmed as array.

If you can sort source table, you can add helper column to rank call date with condition (otherwise, formula is going to be pretty expensive in terms of resource usage).

See Sheet3 for another method using PivotTable as calculation source.

 

[raiyanimv]

I used the Pivot option and it is working... Thank you very much... You are a savior...