Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help me to generate a report
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help me to generate a report
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,R OWS($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saz" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help me to generate a report
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 On Apr 22, 1:07*am, "Max" wrote: 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<"Syst*em Generated")))) In H2: =IF(COUNTA($A2:$C2)<3,"",IF(SUMPRODUCT(($A$2:$A2=$ A2)*($B$2:$B2=$B2)*($C$2:*$C2=$C2))1,"",SUMPRODUC T(($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<"Syst*em 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,R OWS($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. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik ---"Saz" wrote in message ... 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help me to generate a report
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saz" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I generate a report for my classroom? (I APPRECIATE YOUR HELP | Excel Worksheet Functions | |||
Can I generate a report for my classroom? (I APPRECIATE YOUR HELP | Excel Worksheet Functions | |||
Problem trying to generate a report with a formula | Excel Discussion (Misc queries) | |||
Issue with a formula to help generate a report. | Excel Worksheet Functions | |||
Generate Report | Excel Worksheet Functions |