Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag for India asked on

Pivot or Macro on a Data Sheet

Hi,

I am attaching a workbook. It contains two sheets - one with the Data, and the other with Report.

Is there any method, where by using a Pivot table, I can create the Report. Or, do I have to create a Macro. In case Macro is the solution, then I do not want that solution. In case there can be an answer in the form of Pivot or, some link with VLookup, I would like to know that.

Thanks,

San.
Pivot-Or-Macro.xlsx
SpreadsheetsOffice Productivity

Avatar of undefined
Last Comment
Glenn Ray

8/22/2022 - Mon
Glenn Ray

This example file doesn't appear to contain all the necessary information on the "Data" sheet to produce the results you want on the "Report" sheet.

I presume that "S. No." on the Report sheet is the station number.  If so, what is "Posting Location"?  Where is the "number of times..." value stored in the Data sheet?  Is it indicated by the occurence of a person's name?

A PivotTable on the existing data won't work; I think it'll need to be transposed.  I'll wait for your clarification before continuing.

Regards,
-Glenn
Sanjay Gandhi

ASKER
I guess what you are saying makes greater sense than the columns in the sheet, I have shared. S.No. is just a column added for Serial number. Yes, number of times the names appear, is number of times the Guard is found sleeping.

If I capture the data in this manner, I think it should make sense:

Location | Guard | Date

If the data is captured in above columns, I believe then I can get a fairly good pivot close to my report. Will the above format be correct?

Regards,

-San
ASKER CERTIFIED SOLUTION
Glenn Ray

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sanjay Gandhi

ASKER
Formats also make the difference. So we have to change the basic format sometimes to get the right result.

Thanks,

-San.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Glenn Ray

Here is a preliminary workbook with a PivotTable based on your new dataset.  I also tried bringing it into your original Report format using GETPIVOTDATA, but that's still a somewhat manual process.  It should give you some idea of how that works.

Regards,
Glenn
EE-Pivot-Or-Macro.xlsx