Unanswered

Cross reference an excel sheet with list

(0) Share

Report

Posted on by CU08101058-0

Hi all, apologies as I am quite new to power automate, but any help would be appreciated!

I have a sharepoint list which contains staff details needed for a project, this includes their name, employee number and current place of work, etc. My data colleague has provided me with an Excel sheet with details of the recent joiners and leavers, and it includes columns for name, employee number, left/joined, date of leaving/joining.

There are thousands of staff on both the sheet/list. Is there a way I can use connectors to create an instant flow which will cross reference the employee number in the excel sheet to the microsoft list with the, and where staff have left - delete the list row. And another flow, where if there is no matching employee number - add to the list with their name, employee number and location.

Hope that makes sense, thanks in advance

Chrissie

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

CU08101058-0 35 on at

Like (0)

Report

False alarm, I've got it all working now, slowly figuring it out as I go 🤣🤠

Was this reply helpful? Yes No
MB35 91 on at

Like (0)

Report

Hi, I'm trying to do a very similar task currently. Would you mind sharing how you created your flow please?
Thanks

Was this reply helpful? Yes No
CU08101058-0 35 on at

Like (0)

Report

I'm away from my computer now but I'll upload some pics of the flow later for you 👍

Was this reply helpful? Yes No
CU08101058-0 35 on at

Like (0)

Report

I used a simple flow. I had a new excel sheet with a column for 'Joined' or 'Leaver'

'List rows present in a table' is connected to the new excel database with an ODATA filter query which filters for the status: JOIN_LEAVE_CAV_FLAG eq 'Joined'
'Get items' is connected to the existing sharepoint list with a ODATA filter query filtering only those with a matching employee number: field_2 eq '@{items('Apply_to_each')?['STAFFNUMBER']}'
'Update items' updates the existing sharepoint list with the new information changing their status to joined
'Update a row' writes back to the new excel database that the person has been added to sharepoint - I decided not to immediately delete the entry before being sure that the flow did works correctly.

I then just edited the flow to do the same for those tagged as 'Leavers'

I'm rubbish at explaining things, but heres some screenshots, hope that helps!

Was this reply helpful? Yes No