Test case results

[Rafa_B]

Hi,
I need to create a report for test case result, like a pivot table, from several excel files.
Until now I've able to join all the excel files and sheets, and I have a table like this:

Area	Process	Case	Step#	Test Date	Company 1	Company 2	Company 3
Spare	Master Data	MMR-DM01	1	2017-10-23	OK	OK	OK
Spare	Master Data	MMR-DM02	1	2017-10-23	NOK	NOK	NOK
Spare	Master Data	MMR-DM03	1
Spare	Master Data	MMR-DM04	1	2017-10-23	N/A	N/A	OK
Spare	Master Data	MMR-DM05	1	2017-10-23	N/A	N/A	OK
Spare	Master Data	MMR-DM06	1	2017-10-23	NOK	NOK	NOK
Spare	Master Data	MMR-DM07	1	2017-10-23	N/A	N/A	OK
Spare	Master Data	MMR-DM08	1	2017-10-23	N/A	N/A	OK
Spare	Master Data	MMR-DM09	1	2017-10-23	N/A	N/A	OK
Spare	Master Data	MMR-DM10	1
Spare	Master Data	MMR-DM11	1	2017-10-23	OK	N/A	N/A
Spare	Master Data	MMR-DM11	2
Spare	Master Data	MMR-DM12	1	2017-10-23	OK	N/A	N/A
Spare	Master Data	MMR-DM12	1
Spare	Master Data	MMR-DM13	1	2017-10-23	OK	N/A	N/A
Spare	Master Data	MMR-DM14	1	2017-10-23	OK	OK	OK
Spare	Master Data	MMR-DM15	1	2017-10-23	OK	OK	OK
Spare	Master Data	MMR-DM16	1	2017-10-23	OK	N/A	N/A
Spare	Master Data	MMR-DM17	1	2017-10-23			NOK
Spare	Master Data	MMR-DM18	1	2017-10-23	N/A	OK	N/A
FP	Master Data	FP-DM01	1	2017-10-23	OK	N/A	NOK
FP	Master Data	FP-DM01	1	2017-10-23	N/A	OK	OK

I need to count all cases result for all companies except for N/A values, empty values meaning test pending.
The desired output would be:

Area	Process	Cases	Tested	Pending	OK	NOK	%OK	%NOK
Spare	Master Data	40	26	14	19	7	47,5%	17,5%
FP	Master Data	4	4	0	3	1	75,0%	25,0%

How could I achieve this scenario?
Any idea is wellcome.

[JoshKushner]

You can create the desired output by transposing the data and joining a series of filters and summarizations.

I've attached a sample workflow.

Flow:

Result:

Transpose:

Formulas:

[jrgo · Accepted solution]

Hi @Rafa_B,

Here's a possible solution based off your example tables. Workflow attached.

Hope this help!

Best,

Jimmy

[danrh]

Give this a try:

Hope it helps!

[Rafa_B]

Jimmy, thanks for your solution!

Also thanks to Josh and Danrh, I've tested all your ideas.