We help IT Professionals succeed at work.

Pivot or Macro on a Data Sheet

251 Views
Last Modified: 2014-05-20
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
Comment
Watch Question

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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 GandhiFounder, Kenhal

Author

Commented:
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
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sanjay GandhiFounder, Kenhal

Author

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

Thanks,

-San.
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.