Forum Discussion
Filter Function or any Logic
so the file is still not attached (at least I can't see it). please share using a cloud option like onedrive or google drive. I think I get it. See the attached file. I have 2 output options, the first is:
=LET(rnti,data[rnti],ack,data[clrCtrl],
same,--DROP(VSTACK(ack,-1)=VSTACK(-1,ack),-1),
counts, SCAN(0,same,LAMBDA(p,q,(p+q)*q)),
countsB, IF(counts>6,6,counts),
PIVOTBY(rnti,"spcu-"&countsB,countsB,COUNT))but this one has spcu-0 column and total columns and row (which I thought might be helpful)
but this will take those things away:
=LET(rnti,data[rnti],ack,data[clrCtrl],
same,--DROP(VSTACK(ack,-1)=VSTACK(-1,ack),-1),
counts,SCAN(0,same,LAMBDA(p,q,(p+q)*q)),
countsB,IF(counts>6,6,counts),
tabl,PIVOTBY(rnti,"spcu-"&countsB,countsB,COUNT,0,0,,0),
HSTACK(TAKE(tabl,,1),DROP(tabl,,2)))
Hi Everyone, I am trying this for the 1st time( to share a file via Google Drive)
https://docs.google.com/spreadsheets/d/1nKNKhXTLyUJC32v6oegsjFUMBiKXzaCN/edit?pli=1&gid=1146938509#gid=1146938509
Let me know if this is accessible.
Br,
Anupam
Very good idea. The HLINK works but we can't access the file.
When sharing it you should grant read rights - at leastI tried again, let me know if you have the access now
https://docs.google.com/spreadsheets/d/1nKNKhXTLyUJC32v6oegsjFUMBiKXzaCN/edit?pli=1&gid=1146938509#gid=1146938509
Br,
Anupam
The link works now. I downloaded and again all I did was use the formulas I provided below and just updated for the range of input data (I also updated it to go up to spdu-7):
The link also didn't work for me. I used the file in OliverScheurich​ post below, copied all the data 2x and assigned the copied data to 2 new rnti numbers. I then applied the formula I posted below but pointed it to that table of data with this result:
As I noted in my message below if you don't want the Total row/column and the spcu-0 column I provided a different variation of this formula in the post below.
