generate weekly report

N

new_to_excelvba

New Member
Joined
Apr 15, 2019
Messages
1
can someone help to generate weekly summary per this file?

raw data entries:
[TABLE="width: 417"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Status1[/TD]
[TD]Status2[/TD]
[TD]Status3[/TD]
[TD]Status4[/TD]
[TD]Week[/TD]
[/TR]
[TR]
[TD="align: right"]02 April 2019[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]03 April 2019[/TD]
[TD]Reject[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]08 April 2019[/TD]
[TD]Approved[/TD]
[TD]Reject[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]08 April 2019[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Reject[/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]09 April 2019[/TD]
[TD="colspan: 2"]Review pending[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]09 April 2019[/TD]
[TD]Approved[/TD]
[TD="colspan: 2"]Review pending[/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]09 April 2019[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Reject[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]09 April 2019[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD="colspan: 2"]Review Pending[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]09 April 2019[/TD]
[TD]Approved[/TD]
[TD="colspan: 2"]Review pending[/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]20 April 2019[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]20 April 2019[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Approved[/TD]
[TD]Review Pending[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]20 April 2019[/TD]
[TD]Approved[/TD]
[TD]Reject[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]

required summary format
[TABLE="width: 423"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Summary[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: left"]
clip_image001.png

<tbody>
</tbody>
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Week[/TD]
[TD]Status[/TD]
[TD]Status1[/TD]
[TD]Status2[/TD]
[TD]Status3[/TD]
[TD]Status4[/TD]
[/TR]
[TR]
[TD]Week14[/TD]
[TD]Approved[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Week14[/TD]
[TD]Reject[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Week14[/TD]
[TD]Review Pending[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Week15[/TD]
[TD]Approved[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Week15[/TD]
[TD]Reject[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Week15[/TD]
[TD]Review Pending[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Week16[/TD]
[TD]Approved[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Week16[/TD]
[TD]Reject[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Week16[/TD]
[TD]Review Pending[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
try this


Book1
ABCDEFGHIJKLM
1DateStatus1Status2Status3Status4WeekWeekStatusStatus1Status2Status3Status4
202-Apr-19ApprovedApprovedApprovedApproved14Week14Approved1111
303-Apr-19Reject14Week14Reject1000
408-Apr-19ApprovedReject15Week14Review Pending0000
508-Apr-19ApprovedApprovedReject15Week15Approved6310
609-Apr-19Review pending15Week15Reject0111
709-Apr-19ApprovedReview pending15Week15Review Pending1210
809-Apr-19ApprovedApprovedApprovedReject15Week16Approved3221
909-Apr-19ApprovedApprovedReview Pending15Week16Reject0100
1009-Apr-19ApprovedReview pending15Week16Review Pending0001
1120-Apr-19ApprovedApprovedApprovedApproved16
1220-Apr-19ApprovedApprovedApprovedReview Pending16
1320-Apr-19ApprovedReject16
14
Sheet5
Cell Formulas
RangeFormula
J2=COUNTIFS(INDEX($B$2:$E$13,,MATCH(J$1,$J$1:$M$1,0)),$I2,$F$2:$F$13,RIGHT($H2,2))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How to populate daily sales dynamically based on the week-ending dates and daily % breakdown
Replies
0
Views
267
Adit
A
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
429
amphead
A
P
  • Question Question
IF and Xlookup not showing expected result
Replies
2
Views
865
PaulyK
P
Rob_010101
  • Question Question
Weekly meeting tracker
Replies
6
Views
373
JerrExcel
JerrExcel
A
  • Solved
Calculate Week commencing summary based on the daily data
Replies
2
Views
461
Adit
A

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top