Help me to generate a report

[Saz]

I need some help on producing this report -

The data:

Users Clients Run ID Solutions Update Solutions
Brenda Walmart 1234 aaaa System Generated
Brenda Walmart 1234 bbbb System Generated
Brenda Walmart 1234 cccc Reject
Brenda Walmart 4567 aaaa Accept
Brenda Walmart 4567 eeee Deferred
Brenda Target 7890 ffff System Generated
Brenda Target 7890 gggg System Generated
John ShopKo 8901 cccc System Generated
John ShopKo 8901 ffff System Generated
John ShopKo 9012 dddd System Generated
Eric Giant 1012 aaaa Reject
Eric Giant 1023 mmmm Accept
Eric Subway 1458 nnnn System Generated
Eric Cisco 6548 eeee System Generated

The report:

Users Clients Clients Update Total Runs Runs Update
Solutions Solutions Update
Brenda 2 1 3 2
7 3
John 1 0 2 0 3 0
Eric 3 1 4 2 4 2

Brenda:
There are two clients: Walmart and Target
There are three runs: 2-Walmart (1234 and 4567) and 1-Target (7890)
There are seven runs: 3-Walmart (1234), 2-Walmart(4567) and 2-Target
(7890)

Updates: Any updates beside System Generated status consider as update
been done.

Brenda:
Out of Seven solutions: There are 3 updates done
3 Update solutions are from 2 runs out of 3 total runs
2 runs which solutions are updates are both from 1 clients out of 2.

So over all result is Brenda has two clients in which only one is
updated. She requests three runs in those two clients in which only
two are updated. Those three runs have seven solutions in which only
three are updated.

Please help me how to generate this report. I am running out of
ideas.

Thank you,
Saz

 

[Max]

Here's one formulas crack at this ..

Illustrated in this sample:
http://www.savefile.com/files/1516502
Complex_Criteria_Report_Generation.xls

Source data as posted assumed in cols A to E, from row2 down

In F2:
=IF(COUNTA($A2:$B2)<2,"",IF(SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2))>1,"",SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2))))

In G2:
=IF(COUNTA($A2:$B2)<2,"",IF(SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($E$2:$E2<>"System
Generated"))>1,"",SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($E$2:$E2<>"System
Generated"))))

In H2:
=IF(COUNTA($A2:$C2)<3,"",IF(SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($C$2:$C2=$C2))>1,"",SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($C$2:$C2=$C2))))

In I2:
=IF(COUNTA($A2,$C2)<2,"",IF(SUMPRODUCT(($A$2:$A2=$A2)*($C$2:$C2=$C2)*($E$2:$E2<>"System
Generated"))>1,"",SUMPRODUCT(($A$2:$A2=$A2)*($C$2:$C2=$C2)*($E$2:$E2<>"System
Generated"))))

In J2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))
Leave J1 empty

In K2:
=IF(ROWS($1:1)>COUNT(J:J),"",INDEX(A:A,SMALL(J:J,ROWS($1:1))))

In L2: =IF($K2="","",SUMIF($A:$A,$K2,F:F))
Copy L2 to O2

In P2: =IF(K2="","",COUNTIF(A:A,K2))

In Q2:
=IF(K2="","",SUMPRODUCT((A$2:A$200=K2)*(E$2:E$200<>"System Generated")))
Adapt the ranges to suit the max expected extent of source data

Select F2:Q2, copy down to cover the max expected extent of source data, eg
down to Q200. Minimize/hide away cols F to J. The desired results (as per
post) will be returned in cols K to Q, all neatly bunched at the top.

---

 

[Saz]

Hi Max, Thank you for the help. Using your formula did help me where
to start. The result is sort of off from what I am looking for but I
think I can twist little bit and get what I want. Thank you again, Saz

 

[Max]

Welcome. Your requirements were both numerous & complex.

I'm quizzical about this comment though:

.. The result is sort of off from what I am looking for ..

as the results yielded does look like a 100% exact match
with what you originally posted.

Could there be a problem with your actual data, I wonder?

Take down the sample file, see for yourself