Multiple data in one cell and export to power point

Hello everybody,


I have an Excel sheet, which lists services. This services can be provided for different customers, this is why there can be more than one entry for customer in a customer cell. In the finished Excel there will be about 30 different customers. First I want something like a Pivot table, where I can filter for customers. So for example I want a table where I can see how many services are offered in the different service areas for the different customers. I can’t use a normal Pivot table for this because of the multiple values in a cell.

Secondly and this is more important, I want to create single PowerPoint slides out of the Excel for a single service, which should update automatically. In the end there will be about 150 services and I want a PowerPoint slide for every service. On this slide there should be the name of the service and the rest of the properties. What I don’t want is to copy the whole Table as one in a Power Point. The system should work like this: There is a change in the properties of one service. Therefore the responsible person changes the property in the Excel cell and the system automatically updates the PowerPoint slide for the service.

If multiple data in one cell is not possible, I thought about creating a Database in Access and then exporting it to Excel. And subsequently creating the PDF’s out of the Excel. In this case the responsible person would change the property in the access database. Multiple data in one cell is no problem for access, but a reporting with Pivot tables would not be possible.

The most important part is to create single PowerPoint slides out of the Excel. I am a complete newbie and the task is rather complex so I would be very grateful for your help.

I posted this problem in another forum but received no response and this is why I post here. ( http://forum.chandoo.org/threads/mul...r-point.28148/ )

Thanks a lot

Matze

Can you confirm that this is what you expected (before we move on to PowerPoint)
(Unsophiticated) Pivot Table seems to give you what you want based on the manipulated values in Sheet1, which have been arrived at as follows

-Data copied to Sheet1 for manipulation
- “multi-value” cell (columE) text Split and values placed into array
- required rows inserted
- values assigned from row above
- single customer name inserted in each row from array

The code:

Please Login or Register  to view this content.

Are you looking for Excel to create a single presentation of 150 slides (one for each service)?
OR
a pick-a-mix where user can select any combination of services and the presentation built from that?
OR
something else entirely?


If post#02 above is what you are looking for:
amend file services.xlsm to include extra sheet "for a single service" (with the data based on the values in sheet "Data") laid out exactly as you want it to appear prior to creating the slide for PowerPoint and attach the file to your reply

Hi Kevin,

thanks for the effort. The Pivot is great and I understand the macro. If I choose a single customer in the Pivot, the numbers are correct, but if I select multiple customers, then the numbers are not correct, because it counts the numbers of observations in sheet 1 rather than the actual services. But I think I have to live with this limitation.

The decision about the power point issue is not made yet. Either it will be Number 1 or 150 single presentations one for each service. The important point is, that if the presentations are already created and then a service changes. That at this point the responsible person should just open the Excel file, change the property of the service in the Excel and then the data should be updated in the presentation automatically without the creation of a new set of presentations needed. The idea behind this is, that the presentations will be connected to another system and by updating the existing presentations via the Excel we don't need to create new links with a potentially created new presentation. So maintaining the Excel sheet should be enough. This would be the perfect solution.

Thanks a lot for your help.

Matze

So do you want anything doing about the slides at this time?

Hi Kevin,

I would like to talk with me colleagues on monday. So please don't invest any more time into this before I checked with them.

But it would be helpful if you could tell me, if a solution, as I wish, is possible.

Thank you
Matze

I think I may have solved the "multiple customer" count issue for you.
In Excel2013 onwards there is a new feature in pivot tables called "Distinct Count"

When you create a Pivot Table you need to click on box in bottom left "Add data to this data model"
This adds another option to the Value Field Settings /"Summarize Field By.." Distinct Count appears last on the list after VARP.

Have a look at the "GREEN" pivot table in the attached file to see if this now gives the correct results

Hi Kevin,

your solution is great. Thanks a lot! I will adapt the macro to the real data and see if it works.

The export to power point is up for discussion at the moment. I will maybe come back to this issue later on.

So thanks again
Matze