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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

zoharb
  • Question Question
to calculate status
Replies
4
Views
232
zoharb
zoharb
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
270
CaptainGravyBum
C
D
  • Solved
VBA to generate report
2
Replies
15
Views
287
dsubash
D
A
  • Question Question
How to populate daily sales dynamically based on the week-ending dates and daily % breakdown
Replies
0
Views
284
Adit
A
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
476
amphead
A

Forum statistics

Threads
1,226,735
Messages
6,192,733
Members
453,752
Latest member
Austin2222

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