Unpivot and analyse data

Still using Excel 2010?

OK, so take a look at this... it's all the data re-sorted and tarted up. Can you a) explain exactly what graphs you want or b) do it yourself from here?

Delete ALL expected results.

Formula:

=LET(

A,Table1,

B,TOCOL(IF(SEQUENCE(,MAX(3))<=3,INDEX(A,,1),NA()),3),

C,TOCOL(IF(SEQUENCE(,MAX(3))<=3,INDEX(A,,2),NA()),3),

D,WRAPROWS(TOROW(DROP(A,,3)),3),

E,HSTACK(B,C,D),

F,FILTER(E,ISNUMBER(INDEX(E,,3))),

IF(F="","",F))

6000 rows is nothing. A formula can do that... and 10x more easily. Test it on your real data. I'm away for the night, but will be back in the morning.

Well? I tested my formula with 10,000 rows and it took 0.3 seconds to calculate. So, AFAIK, no performance issues. HOWEVER, I am a bit baffled by your expected results.

Please explain HOW you derived the expected results for February.

I have not successfully applied it to the real data yet as that is in different columns to the representative sample I provided here.
My real dataset has many additional columns which are not relevant for this analysis.
When I dropped in the formula to test, it turned the 6000 odd rows into approx 86000 which is consuming a lot of my PCs resources to process.
So I need to figure out how to modify the formula to suit my real dataset!

regarding how I arrived to my expected results for Feb:

• 4 new requests = 6020 x 2 FNs, 6021, 6022
• 5 assigned = 5695 FN1, 6022 FN1, 6020 FN2, 6021 FN2, 6020 FN3
• 3 complete = 5695 FN1, 6021 FN2, 6020 FN3
• 6 carryover = (4 + 5)-3 .... which is coming from 4 new requests in feb + the 5 requests assigned in Jan but not complete in Jan, - 3 complete in feb

iteration 2.

i do not see how 6000 rows can be expanded into even one more more than 18,000 rows... as there are (at worst) 3 areas per row, times 6000 rows. But leave that for now.

1. Please check the values in purple. Explain why different answers are expected (if any). Pay particular attention to the two pairs of red cells... completed, but without a date.

2. Explain the carryover values for all months shown. I just cannot get my head around this!! My attempt to calculate manually (outside the main formula) is in yellow...

Completed is fixed. All I need to do is figure out how to get the last column incorporated dynamically into the formula... As Sherlock Holmes would have said... "a two-pipe problem".

I didn't even need to start smoking again... let alone smoke two pipes-worth.

Finally... I hope... complete with graph.

that is amazing, thank you for all your help

unfortunately I still have some issues implementing this to my data set

as mentioned the dataset contains many more columns which are not required for this analysis
when I implemented the formula to the full sheet, it chewed up my resources and made my PC unusable

so I used 'choosecols' to replicate the columns from main dataset into a new sheet, and applied the formula there
I think this is required since the dataset will be continually updated
The formula executes in seconds this way

so when I apply formula to those columns, it appears accurate for logged, assigned is throwing a #num error and complete looks accurate (since complete dates have not been recorded completely)
Carry-over is giving #calc error presumably because of the #num error on assigned

I will review my data to figure out where the #num error is arising

Screenshot 2024-05-14 211743.png

Thanks again

I cannot diagnose what I cannot see. I need some sort of sample showing the error... not a picture.

Well... that was a two-pipe problem... mostly caused by the way you selected the data for sheet 2. It took me well more than an hour to solve the issue!!

It's not the way that I'd have done it, but there we are. I can't refine it becase your original data were not included in the file and the "data" were all linked to an external file to which I do not have access.


Try it now.

Repost the file with the data, as presented to me, actually IN the file... not here:

'https://medtronicemea-my.sharepoint.com/personal/donovd3_medtronic_com/Documents/

WITH a header row... in case I'm losing the plot

Actually, can I be a pain and change what I asked for... Please supply the RAW data. Delete the CONTENT of all un-necessary columns, but LEAVE the columns themselves in place

OK. I am starting over again. Please explain WHY there should be 365 logged in Jan 2024.

It is the number of requests times the number of functions required in each request.
For example, some requests require R&D only, others require are other combinations of functions. Separated by ;

OK. I had misinterpreted that. To avoid further confusion, please define EACH of the 4 parameters again, with special regard to which column(s) the date(s) are to be found in.

OK. Penultimate step. Please check a few rows for accuracy. If OK, I'll set up the Named Ranges to do the chart and (with a bit of luck) we'll be finished.

If I'm miles out, I going to set out to become an alcoholic!!

It's a bit of a beast... but pretty quick.

Formula:

=LET(Raw,CHOOSECOLS(Table1,1,3,14,27,28,29,32,33,34,37,38,39),

A,IF(Raw=0,"",Raw),

B,FILTER(A,INDEX(A,,2)<>""),

C,UNIQUE(INDEX(B,,1)),

D,EOMONTH(INDEX(B,,2),-1)+1,

E,UNIQUE(D),

F,BYROW(INDEX(B,,3),LAMBDA(z,LEN(z)-LEN(SUBSTITUTE(z,";","")))),

G,BYROW(E,LAMBDA(x,SUMPRODUCT(--(D=x)*F))),

H,EOMONTH(TOCOL(CHOOSECOLS(B,4,7,10)),-1)+1,

I,BYROW(E,LAMBDA(x,SUM(IFERROR(IF(H=x,1,0),0)))),

J,EOMONTH(TOCOL(CHOOSECOLS(B,6,9,11)),-1)+1,

K,BYROW(E,LAMBDA(x,SUM(IFERROR(IF(J=x,1,0),0)))),

L,SCAN(0,G-I,LAMBDA(a,b,a+b)),

VSTACK({"Month","Logged","Assigned","Completed","Outstanding"},HSTACK(E,G,I,K,L)))

Actually, if you wan the data to be dynamic... there's a bit more to it than it seems. You need to use Named Ranges to make it properly dynamic, as I did in Post 14. Take a look at what I set up there (CTRL-F3 to view/edit the named ranges in that post). If you need any more help, shout.

For now, though...

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.