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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sanjay Gandhi

ASKER

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

Thanks,

-San.

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