SharePoint List using Choice field with checkboxes/multiple selections – to Excel for Reporting

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?

Finding and comparing data from two excel files

I need to compare time shifts in two excel files after identifying the corresponding user. The files look something like this

enter image description here

and

enter image description here

The User ID for Name1 Surname1 in the first file is 35.

In the second, User ID is 56 and is always preceded by #.

After verifying that Name1 Surname1 (1st) is the same with Surname1,Name1 (2nd), I should be able to build a correspondence table between User ID-s in both tables, ie 35 <- 56, which will not change.

User ID in the 2nd is in G76, and the IN and OUT times for that user are in AR80 and BT80, always 4 rows down, always AR and BT columns.

At this point I need to compare ShiftStartTime and ShiftEndTime in the 1st with IN and OUT in the 2nd, to set out differences.

The way I was thinking about this was to look for Surname1,Name1 #56 in col G of the 2nd file to find the row, and then get IN OUT to compare the times.

Question. Is a Macro in Excel VBA capable of doing all this, and can someone give an example of what the code might look like.

Choice of SP version for solution with Excel Web Services and MDS

I must start to provision a dev environment to create a solution on SharePoint OnPrem. I try to define first what version I could use. Then What service I should use in my SharePoint farm, but I would like to have a feedback because I’m not used to this exercise.

First, I have some functionals and no-functionals objectives for my end user: Users need to have SharePoint site whom developer could easily customize from design view

  • Users need to have many separate sections with different document library
  • Some functionals admins must be able to manage the permission to the different sections for other user.
  • Users need to view or edit Excel document
  • Users need to view Power BI dashboard
  • Users need to manage some data in SQL database from the SharePoint site.

Currently I have around me lot of SharePoint 2013 farm.

My first hesitation is to start with SharePoint 2013 to stay close to the prod environment (and users used Office 2013) or if this new development could be help me to show the great value of SharePoint 2016 compare to 2013. If I stay on SharePoint 2013:

  • The main component I think implement is Excel Web Access Web Part to view and edit Excel document.
  • I didn’t had idea to manage data from SQL database from SharePoint site. I heard about Master Data Services in SQL Server, but I didn’t really get what integration can be done with SharePoint. Anyone have any doc about that?

If I choose SharePoint 2016, I understood Excel Web Access Web part didn’t exist and is replaced by Excel Online and so I need now to install Office Online Server. But if I’m correct this service can run on the same server as SharePoint, so I need at least 2 different servers for my SharePoint 2016 dev farm vs 1 with my SharePoint 2013 dev farm. That correct? For my Power BI requirement, I should configure something specifically or I could just use a webpart and embed that in HTML?

Thank for your advice, I hope have a great discussion to help me to made good choice for my development.

Weird text pasted into Excel out of nowhere – hacked?

So while I was working in Excel, the following text appeared: 108.162.221.65 162.158.75.124 172.68.78.28 162.158.75.124

It’s Cloudflare IP addresses that i hadn’t copied.

My first thought was, what if someone had hacked my Mac and i pasted from their clipboard.

Scanned my Mac with Intego, didn’t find anything. Same with Malwarebytes Breach Remediation tool. Anyone have any ideas?

Excel – INDEX MATCH MATCH with a twist

Greetings Super Users!

I’m trying to assist some local firefighters make a crewing display page on their excel roster. Their roster is setup with dates from 1st January -> 31st December vertically in column A with the names of each firefighter across the top in Row 1. They then fill out which role any given person is working within the matrix.

On the display page they will have a section for each fire truck and want to display who is the driver for Truck 1, who is the pump operator for Truck 2 etc etc.

Now I’m familiar with using INDEX MATCH MATCH for finding a row and column and then return the intersecting value. However in this case I need to find the date (vertically) then find the role on the same row as the found date (e.g. “Truck 1 Driver”) and then return the column header (Name of firefighter perforing that role on that day)

I’m very capable with VBA but I would like to avoid using it if I can achieve the same result with a forumla. Unfortunately my formulas skills are lacking as I usually just use VBA.

Using Openpyxl to compare data values in two excel files when one isn’t ordered

I am comparing the income values in two different excel files to evaluate if their is a discrepancy the income for each employee between the files. My issue is that the names in the second file are alphabetized but in the first file they are ordered randomly.

I am very new to python, what search function can I use that creates a list telling me what the corresponding row values in the unordered list are to the alphabetized list.

Here is my code so far.

import openpyxl wb = openpyxl.load_workbook('Gehaltabrechnung_v02.xlsx') sheet= wb['Tabelle1'] wb2 = openpyxl.load_workbook('BonusCalculation.xlsx') sheet2= wb2['Tabelle1'] //imports files and names the workbooks  column_income1 = sheet['G'] income1_length = len(column_income1) column_name1 = sheet['B'] name1_length = len(column_name1) column_income2 = sheet2['I'] income2_length = len(column_income2) column_name2 = sheet2['B'] name2_length = len(column_name2) //specifies collumn and their lengths for the two sheets 

Line Endings and New Lines issue in an Excel CSV between 2 Macs

My colleague and I both use Macbook Pros on the latest Mac OS.

He exports CSV files from MS Excel for Mac and sends them to me, but when I receive them I open them in a text editor and there are blank lines on every other line.

Weirdly though, when he opens the same file on his machine in the same text editor, he does not see blank lines. Which makes me wonder if this is a setting on his machine.

I know Windows and Mac treat line endings differently, but we are both on a Mac. And Sublime should be the same on both machines.

When I upload them to our linux server, linux agrees with me (our scripts see blank lines in the CSVs and have to be ignored).

He is creating CSVs using the UTF-8 format.