Count Distinct Function

[HM]

Hi,

I was wondering whether there was a Count Distinct Function? I know the summarise tool has it but I need to filter my data each time and then join it back to the main dataset which is making it very messy. Details below:

Data Input:

Project	Red	Yellow	Blue	Green	Orange
1	Y	Y	N	Y	Y
2	N	N	N	N	Y
3	Y	N	Y	Y	Y
4	Y	Y	Y	N	Y
5	Y	N	N	Y	Y

Desired Output:

Project	Red	Yellow	Blue	Green	Orange	Count No. projects where Red = Y	Yellow = Y	Blue = Y	Green = Y	Orange = Y
1	Y	Y	N	Y	Y	4	2	2	3	5
2	N	N	N	N	Y	4	2	2	3	5
3	Y	N	Y	Y	Y	4	2	2	3	5
4	Y	Y	Y	N	Y	4	2	2	3	5
5	Y	N	N	Y	Y	4	2	2	3	5

Thanks!

[jdunkerley79]

You can use Transpose and CrossTab to make this a simpler operation.

Transpose to convert the Input to Project, Colour, Value.

You can then do the summarise to produce totals for each colour = Y

Then crosstab back to produce a single row 

Finally append fields to original set.

Have attached a demo workflow

[s_pichaipillai · Accepted solution]

@HM

here is another version of Solution using Multirow Tool then Summarize

[HM]

Thank you all for your help! 

[PPNU]

Thankyou so much for this workflow

Since there is hyphen in raw data so when I am trying to count , its considering hyphen as well.

I have cleaned it up using regreplace function as well but its not working.

Please find workflow below.