Showing results for 
Search instead for 
Did you mean: 
Level 10

compare sharepoint lists

I have two SP lists.  I want get a result of all the items in list one that do not appear in List two.  I  have looked at some of the responses here but fail to get the result I want.  List one has 200 records and list two has 500 records but there will be instances in where items in list one are not in list two and these I want to report on.


Any ideas?

Community Support Team
Community Support Team

Re: compare sharepoint lists

Hi @leyburn19,


Does your SP List one has same data structure with your SP List two?

Further, do you want to get a result of all the items in List one that don't appear in List two?


I assume that your SP List one has same data structure with your SP List two and there is a unique column in both SP lists. I have created two SP lists on my side and the data structure of them as below:

List 1:9.JPG



Note: The TaskName column in my two SP lists is a unique column which could identify one item uniquely.


I have made a test on my side and please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to ResultsArray, Type set to Array and Value set to empty.
  • Add a "Get items" action, specify Site Address and List Name set to List 1. 
  • Add a "Apply to each" action, input parameter set to output of the "Get items" action.
  • Within "Apply to each" action, add a "Get items 2" action, specify Site Address and List Name set to List 2. Add a "Filter array" action, From set to output of the "Get items 2" action, click "Edit in advanced mode", type the following formula:
@equals(item()?['TaskName'], items('Apply_to_each')?['TaskName'])

       Add a "Condition" action, click "Edit in advanced mode", type the following formula:


Within "If/yes" branch of Condition, add a "Append to array variable" action, Name set to ResultsArray and Value set to following formula:

  • Under "Apply to each" action, add a "Compose" action, Inputs field set to ResultsArray variable.

Image reference:11.JPG



The flow works successfully as below:






Best regards,







Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Level 10

Re: compare sharepoint lists

I am doing exactly as you say but I think I have an issue with this as when I do a full run it was going for over an hour before I simply cancelled.


List1 has 199 records

List2 has 454 records at present


My obejcetive is to find and list the details of all the records in List1 that have no matches in List2


Both lists are similar but not the same.  List one is a veh details list.  List 2 is a list of vehicle bookings.  List one has a unique column for a veh registration.  List2 has same column but it is not unique as a veh can be booked on multiple occasions.  I want to do a daily summary to my sites of all veh regos that have no bookings at all in List2.


So in principal if I was looping an array in something like VBA,  as soon as a if find a match it would go to the next rego.  If it looped all teh way through it would put that rego into a new array for my report


I did everything you said.  To see a result,  I reduced the rows to be 10 in List1 and 100 in List 2.  It does work with a result array that had nothing but empty strings so I am assuming there were matches against every row.  This took 2 minutes.  When I expanded the get items to manage the whole list of 199 vs 454 I gave up after an hour.  It was still working but that sort of time makes this a little tough.


Do you have any other ideas.  The one you gave really opened my mind to variables and arrays so thank you for that as well.

Level: Powered On

Re: compare sharepoint lists

@v-xida-msft.  I want to thank for such clear instructions. I did exactly as per the described steps but I can't open the OUTPUTS in the very last Compose step.  The only option is to save and when I open a saved file it says Unknown File Type.  The flow compiles fine, but don't can't see the results.

Also,  I have theMS_Flow_092718.jpg same object (unique key) in both my SP lists and I want to compare just base on that one column.  How could I define the filter array to just filter on that one object?

Any help is appreciated.

Helpful resources


New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!


Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!


Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!


Microsoft Learn

Learn how to build the business apps that you need.


Power Platform World Tour

Find out where you can attend!


Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,832)