Summary of incremental data

M

Mumgirl

New Member
Joined
Jan 7, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi There,

I am getting attendance data on daily basis from various partners ( This is incremental data),

I want to prepare 2 summaries out of it in Excel Power Query based on Operation date and subsequent table.

I have table in "Details" Sheet where we have a list of registered partners, input is coming from MS form in "From Input" sheet, I want power query to compare "Opd id" and "Operation date" between "Details" sheet and "Form Input" Sheet, if particular OPD id has an entry in Form Input then "Details" sheet should show as "Open" in that particular date else "Closed"

Similarly, in "Sum" sheet, I need a count of Open & Closed for perticular day.

Both the summaries should be able to handle incremental data and increase date columns on each day.

Sharing Input file

Daily Tracker.xlsb
ABCDEFGHI
1Service Centre NameCityOPD IdOperation TypeBranchDate of OperationASC Opening TimeASC Closing TimeNumber of Employees Present
2SMART HOME CAREBHUBANRSWAROPD18Only FieldBihar, Odisha & Jharkhand23-04-2110:00:00 AM6:00:00 PM4
3KUTHARI ENTERPRISESBERHAMPUROPD16Only FieldBihar, Odisha & Jharkhand23-04-2110:00:00 AM5:00:00 PM3
4RIMA SERVICE POINTJORHATOPD24BothWest Bengal & NE23-04-2110:00:00 AM6:00:00 PM3
5THE KEYPADGUWAHATIOPD23BothWest Bengal & NE24-04-218:00:00 AM5:00:00 PM5
6RIMA SERVICE POINTJORHATOPD24BothWest Bengal & NE24-04-2110:00:00 AM6:00:00 PM3
7STAR SERVICESJEYPOREOPD20BothBihar, Odisha & Jharkhand22-04-2110:00:00 AM6:00:00 PM4
8STAR SERVICESJEYPOREOPD20BothBihar, Odisha & Jharkhand23-04-2110:00:00 AM6:00:00 PM4
9STAR SERVICESJEYPOREOPD20Only WorkshopBihar, Odisha & Jharkhand24-04-2110:00:00 AM6:00:00 PM4
Form Input


Expected summary 1 ( Details Sheet)

Daily Tracker.xlsb
ABCDEFGHIJ
1RegionBranchStateCityOPD IDWorkshop Name23-Apr24-Apr25-Apr26-Apr
2EastBihar, Odisha & JharkhandBRBegusaraiOPD1Manish EnterprisesClosedClosedClosedClosed
3EastBihar, Odisha & JharkhandBRBhagalpurOPD2Service PlazaClosedClosedClosedClosed
4EastBihar, Odisha & JharkhandBRGayaOPD3Sanehi EnterprisesClosedClosedClosedClosed
5EastBihar, Odisha & JharkhandBRMuzaffarpurOPD4Shakshi TradersClosedClosedClosedClosed
6EastBihar, Odisha & JharkhandBRPatnaOPD5Ayush Enterprises BiharClosedClosedClosedClosed
7EastBihar, Odisha & JharkhandBRPURNEAOPD6A S ENTERPRISESClosedClosedClosedClosed
8EastBihar, Odisha & JharkhandBRSaharsaOPD7Hoda EnterprisesClosedClosedClosedClosed
9EastBihar, Odisha & JharkhandJHDeogharOPD8Shiv Sales And ServiceOpenOpenOpenClosed
10EastBihar, Odisha & JharkhandJHDhanbadOPD9Digital CareClosedClosedClosedClosed
11EastBihar, Odisha & JharkhandJHHazaribaghOPD10Sigma ElectronicsClosedClosedClosedClosed
12EastBihar, Odisha & JharkhandJHJamshedpurOPD11B.P ElectricalOpenOpenOpenOpen
13EastBihar, Odisha & JharkhandJHRanchiOPD12Digital ServiceClosedClosedClosedClosed
14EastBihar, Odisha & JharkhandORAngulOPD13Modern TechnologyOpenOpenClosedOpen
15EastBihar, Odisha & JharkhandORBalangirOPD14Jay Matadi ServicesOpenClosedClosedOpen
16EastBihar, Odisha & JharkhandORBalesoreOPD15P C ElectronicsOpenClosedClosedOpen
17EastBihar, Odisha & JharkhandORBerhampur-1OPD16Kuthari EnterprisersOpenClosedClosedOpen
18EastBihar, Odisha & JharkhandORBhadrakOPD17Ohmega EnterprisesClosedClosedClosedOpen
19EastBihar, Odisha & JharkhandORBhubnaswarOPD18Smart Home CareOpenOpenClosedOpen
20EastBihar, Odisha & JharkhandORCuttackOPD19S R CareClosedClosedClosedClosed
21EastBihar, Odisha & JharkhandORJeyporeOPD20Star ServicesOpenOpenClosedOpen
22EastBihar, Odisha & JharkhandORRourkelaOPD21Vision & Vision ServiceOpenClosedClosedOpen
23EastBihar, Odisha & JharkhandORSambalpurOPD22Popular ServiceOpenOpenClosedOpen
24EastWest Bengal & NEASGuwahatiOPD23The KeypadClosedOpenClosedOpen
25EastWest Bengal & NEASJorhatOPD24Rima Service PointOpenOpenClosedClosed
Details


Summary 2 ( Sum Sheet)

Daily Tracker.xlsb
ABCDEFGHIJ
2RegionBranch23-Apr24-Apr25-Apr26-Apr
3OpenClosedOpenClosedOpenClosedOpenClosed
4EastBihar, Odisha & Jharkhand10126160221012
5EastWest Bengal & NE112003011
6India111381602251113
Sum



Please help me in this.

Sakshi
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
M
  • Question Question
Formatting of data on a Summary sheet
Replies
2
Views
380
Muzza77
M
B
  • Question Question
Data sorting by time interval
Replies
3
Views
444
Bebbspoke
B
D
  • Question Question
Transfering data over multiple Workbooks; Excel 2010
Replies
0
Views
946
Da3d4lu5
D
C
  • Question Question
How can I import data from .asp web pages
Replies
1
Views
3K
Colo
Colo

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

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