CJennings1234
New Member
- Joined
- Sep 22, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
So I've recently started work on a project in my job that uses Microsoft Forms(QR Codes), Microsoft Power Automate and then Microsoft Excel.
The workers fill out a questionnaire on Microsoft Forms, the Power Automate then automatically adds a new row into the table on Excel with their answers. Those are then put onto different sheets/pivot tables to get the conformity of the answers and a few other bits.
On the Microsoft Forms all of the questions are either a Yes or No, if they answer Yes they go onto the next question, if the answer is No then they have to put a reason as to why they said No in some detail which also appears onto the Excel spreadsheet.
The 'Master Data' table I've got setup so far is as below:
NAME | DATE | WEEK | SHIFT | Q1 | Q1 Explain Why | Q2 | Q2 Explain Why |......
What I'd like is a way to make a table on the Excel worksheet with the Date, Area and the problem for that area(if any) however the only way I can think of to do this will include duplicates, which I'd like to avoid if possible.
I've tried to use the Pivot Table, ConcatenateX function which does work however when there is alot of entries for one day then there just ends up being alot of whatever De-limiters I set it as being, so say if there was 2x Entries in the questionnaire for 21/09/22 but 0 of them had a 'No' answer, the row would just show 2x commas.
Any help is appreciated, let me know if you need anymore information, also attached some examples(Example 1 is the table, Example 2 is the pivot table idea I tried with the '+' sign being the de-limiter)
The workers fill out a questionnaire on Microsoft Forms, the Power Automate then automatically adds a new row into the table on Excel with their answers. Those are then put onto different sheets/pivot tables to get the conformity of the answers and a few other bits.
On the Microsoft Forms all of the questions are either a Yes or No, if they answer Yes they go onto the next question, if the answer is No then they have to put a reason as to why they said No in some detail which also appears onto the Excel spreadsheet.
The 'Master Data' table I've got setup so far is as below:
NAME | DATE | WEEK | SHIFT | Q1 | Q1 Explain Why | Q2 | Q2 Explain Why |......
What I'd like is a way to make a table on the Excel worksheet with the Date, Area and the problem for that area(if any) however the only way I can think of to do this will include duplicates, which I'd like to avoid if possible.
I've tried to use the Pivot Table, ConcatenateX function which does work however when there is alot of entries for one day then there just ends up being alot of whatever De-limiters I set it as being, so say if there was 2x Entries in the questionnaire for 21/09/22 but 0 of them had a 'No' answer, the row would just show 2x commas.
Any help is appreciated, let me know if you need anymore information, also attached some examples(Example 1 is the table, Example 2 is the pivot table idea I tried with the '+' sign being the de-limiter)
