locked
Comparing Excel sheet and SharePoint List to show differences RRS feed

  • Question

  • Hi,

    I've got the question from my management whether it is possible to compare a SharePoint list that we have against an Excel sheet. Both the SharePoint list and the Excel sheet have the same unique columns date and article number. So the rows could be found that way.

    We need a list of the rows that could not be found with those 2 unique column values.

    Since we aren't really a SharePoint developer firm, we aren't familiar enough with it to know if this is possible. Can anyone tell me whether this is possible or not? And if it is, how it could be done, or at least point me to some documentation that tells me how it could be done?

    Thanks in advance.

    Kind regards,

    Nils

    Monday, March 16, 2015 8:19 AM

Answers

  • Hi,

    According to your description,my understanding is that you want to compare the list item and the excel sheet record to find which item is missing in the SharePoint list.

    As a workaround, I suggest you can try to export list item to a new Excel sheet using the OOTB feature in list ribbon and then you can use excel vlookup function to compare the record between the two sheets.

    Here are some detailed articles for your reference:

    How to Use VLookup With Different Sheets on Excel

    Vlookup function

    If you have some question about using vlookup function in excel, you can create a new post in excel forum, you will get more detailed information from there.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Thanks

    Best Regards


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Proposed as answer by Eric Tao Wednesday, March 25, 2015 5:22 AM
    • Marked as answer by Eric Tao Saturday, March 28, 2015 10:45 AM
    Wednesday, March 18, 2015 11:07 AM

All replies

  • Hi,

    You cannot compare with the excel sheet which is imported data from the SharePoint list as its contains the auto generated columns such as ItemType , Path etc..those are not relevant to your form requirement

    But you can check the required columns are available in the excel by navigating to List Setting Page,check the Columns header and compare with the excel sheet column data.

    You can ignore the default built in columns such as created by,modified,created and modified by columns.


    Murugesa Pandian| MCPD | MCTS |SharePoint 2010



    Monday, March 16, 2015 9:37 AM
  • Hi,

    Thanks for the reply, but I think I've expressed myself poorly. We need a way to compare the data in the SP list to the data in the Excel sheet. We have created a new SP list and a form to add records to it, and this should be the new way to save the data.
    Still not all of our colleagues are using this new form, so we would like a way to compare the old Excel sheet (that is also still used as backup for the moment) with the new SP list, and look at the rows that arent found in the SP list so we can point the colleague to the new form.

    Example of the SP list:
    row nr art nr date colleague name
    1 123 03/17 X
    2 1234 03/16 X
    Excel sheet:
    row nr art nr date colleague name
    1 123 03/17 X
    2 321 03/16 Y
    3 1234 03/16 X
    ==> so we need a way that SP tells us that row 2 (from the Excel sheet) does not exist in its SP list (by looking at the art nr and the date). This way we can tell colleague Y to use the SP form next time.

    Is this somewhat possible?

    Thanks in advance.

    Kind regards,
    Nils
    Tuesday, March 17, 2015 10:05 AM
  • Hi,

    According to your description,my understanding is that you want to compare the list item and the excel sheet record to find which item is missing in the SharePoint list.

    As a workaround, I suggest you can try to export list item to a new Excel sheet using the OOTB feature in list ribbon and then you can use excel vlookup function to compare the record between the two sheets.

    Here are some detailed articles for your reference:

    How to Use VLookup With Different Sheets on Excel

    Vlookup function

    If you have some question about using vlookup function in excel, you can create a new post in excel forum, you will get more detailed information from there.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Thanks

    Best Regards


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Proposed as answer by Eric Tao Wednesday, March 25, 2015 5:22 AM
    • Marked as answer by Eric Tao Saturday, March 28, 2015 10:45 AM
    Wednesday, March 18, 2015 11:07 AM