0

I work for an organization that allows us to create SharePoint Sites / lists, etc. I have NO backend access to SharePoint, nor do I have access to SQL, SSRS, etc. I do have Access and Excel.

That said, my list in SharePoint is a list of "unique" projects. Each project has a choice field, with an option for multiple check box selections for "product". So - one to many. One Project may have 1 or MORE Products. I have been asked to generate graphical representation/report - using Excel - of the count of PRODUCTS in our PROJECTS.

When I export data to excel - and attempt to create a pivot table, each project has the PRODUCT lists sorted by a ;#. Which makes it impossible to get an aggregated count by product.

I have multiple fields like this - and hundreds of projects and products - so it's not conducive to manually break them all out - and I'll have to run the data on demand or at a minimum monthly.

IS there a way to process this through Excel with limited manual intervention to group / count the individual PRODUCTS?

0

This is how the lookup data is stored internally. Your best option is to create some client side JavaScript code which reads and formats the data in the required format and dump it into a CSV file. Then open the CSV file in Excel.

You can execute the JavaScript code by opening browser console window.

  1. Querying data in JavaScript
  2. Expand lookup fields
  3. Create CSV file using JavaScript

Another Option

Create a REST query and execute it in a new browser tab. This will give back the JSON/XML format. Now convert this to CSV format using online tools -

  1. https://konklone.io/json/
  2. http://www.convertcsv.com/xml-to-csv.htm
  • 1
    Or create a Macro in Excel that can split and reformat as desired – Eric Alexander Aug 15 '16 at 17:10
  • @EricAlexander in this case will the lookup fields get expanded with actual values? – Amal Hashim Aug 15 '16 at 17:11
  • 1
    Exports with lookup values typically come over in the export to excel as ID;#Value like she mentions – Eric Alexander Aug 15 '16 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.