Count of changes between column different column values

[rohitashsharma]

Hi team,

I have been trying to find out the way so I may have count in a separate column among the multiple columns same as I have in on columns BV and BW in the attached data.

Example:

Thanks,

Rohitash

[SPetrie · Accepted solution]

Hopefully the file you shared is dummy data and not actual data. May want to remove that if it is live.

If im understanding the ask, the process would be this.

Add in a record ID for all the data.

Transpose everything except the main columns and then create a new column to identify the groups you are wanting the totals for.

Summarize the data by grouping on RecordID and the new group you created and a countDistinctNonNull for the values.

Cross tab it back out grouping by ID with Group as the column names and value as the valuse.

Rejoin that back to the original data using the RecordIDs again and you will have the new columns.

[RolandSchubert]

Hi @rohitashsharma ,

I'm not sure, if I got you right, but I tried to rebuild the Excel logic, even though the SUMPRODUCT parts was a bit confusing ...

In general, I added a RecordId, transposed the fields V-BW and splitted between the two groups. 

Then, you can count the number of occurences for a specific value (e.g. date) , calculate the "inner part" of the formula and aggregate the results for each ID.

Last step is to join the result back to your original data.

Let me know if it works for you.

Best,

Roland