Help me to generate a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Saz - 21 Apr 2008 17:10 GMT

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

Reply to this Message

Max - 22 Apr 2008 07:07 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I need some help on producing this report -
>
[quoted text clipped - 49 lines]
> Thank you,
> Saz

Reply to this Message

Saz - 22 Apr 2008 15:26 GMT

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

> Here's one formulas crack at this ..
>
[quoted text clipped - 98 lines]
>
> - Show quoted text -

Reply to this Message

Max - 22 Apr 2008 17:35 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Rate this thread: