Comparing cells of text in two different sheets to find and highlight differences/updates

lorikgator

New Member
Joined
Aug 26, 2014
Messages
25
This is my first post - I've been lurking for a long time and it seems I always need an answer right away, so I've never actually tried this, so here goes! I'm sorry if it's lengthy... I see how frustrating posts are without enough detail so I may overdo it a bit.

My Specs:
Excel 2007, Windows 7

Background/Scenario:
I have a sheet, formatted as a table, with data that is partially pulled from an online location (SharePoint spreadsheet) and partially entered/updated by a small group of people.
The data on the SharePoint site changes fairly regularly and we need to pull the current info (open the SharePoint spreadsheet as an Excel file - basically importing it into my Workbook) and see what's an entirely new entry (have that covered thanks to this site :)) and what's changed in existing entries (the source of my question here.)

I'm not averse to using VBA (I'm using a fair amount in this file already), but I'm still in the fairly early learning stages.

Details:
  1. I need the comparison to highlight the cells in my data entry table/sheet that are different from the cells in the sheet imported from the SharePoint site (that changes regularly)
  2. I need the user to see both the current cell and the "updated" info from the import
  3. I have 7 columns that I need to compare individually (to see WHICH of the columns have changed data)
  4. There are 80+ total columns, so a concatenation compare or copying data from the imported sheet to the user imput sheet seems impractical
  5. I already have the instance of a new row in the import covered with code
  6. I have a unique index field to compare in both documents (called "Capability ID")
  7. It needs to involve minimal intervention by the person pulling the data

Example Data:

UserDoc Field1
INDEX Field
UserDoc Field2
...
ImportDoc Field1
ImportDoc Field2
R10M-A
RTB-08
Right
...
R11M
R10M-A
MNS-01
Managed
...
Measured
R10M-A
GPS-02
Global
...
R11M
VoIP-22
Voice
...

<tbody>
</tbody>





Existing Formula/Solution and Problem With It:

  • Currently I'm using INDEX/MATCH to do the comparison and display the value if there's a difference:
    • "Table_owssvr" is the IMPORTED table (Excel automatically formats it as a table and names it - handy here!)
    • "Current IT Release Target" is the field I'm investigating with this particular formula
    • "Capability ID" is my indexed column that's the same in both tables
    • "Capabiliy Tracker" is the sheet name where my user input table is located (and $B3 is the location of the indexed field equal to Capability ID)
{=IFERROR(IF(INDEX(Table_owssvr[Current IT Release Target],MATCH($B3,Table_owssvr[Capability ID],0))<>'Capability Tracker'!A3,INDEX(Table_owssvr[Current IT Release Target],MATCH($B3,Table_owssvr[Capability ID],0)),""),"NOT IN DASHBOARD")}

  • This formula displays the changed data if something changed, nothing if it's the same, and NOT IN DASHBOARD if the index value isn't found
  • I have conditional formatting to change the cell shading for any of the fields in the user input sheet that are different from the imported sheet as a result of this formula
  • The user can then copy and paste special > values to update the user input sheet value to the imported sheet value
  • The user deletes the old import table each time they pull a new one (to keep the automatically created table name the same) and that screws up the references in the formula, so I've created a macro to add the formula BACK each time you import a new version of the SharePoint site data
  • This works JUST fine as long as I never sort the user input table (but that's something I need to do)

So there's my dilemma. My solution works until I have to sort, then I have to run the "put the correct formulas back in the cells" macro again to fix the reference to the changes post-sort.

  • How can I do this better/more efficiently and allow my users to sort with no problems?
  • What extra info is needed to answer this question that I didn't think to include?
  • How can I make future posts thorough but not OVERLY detailed?

Thanks to anyone who is even willing to read this one! :)

lorikgator
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

lorikgator

New Member
Joined
Aug 26, 2014
Messages
25
I'm sorry I was unable to download the MrExcel HTML maker - I get an IE error when it tries to go to the page. I downloaded the alternative suggestion but it's all in German and I can only get so far with pictures!

I'm happy to posts screen shots if someone can help me with a place (that isn't cloud storage - it'll be blocked by my company) to store the images on the web to reference. I suspect that's a simple thing that I SHOULD know but I just haven't encountered the need before!
 

Forum statistics

Threads
1,144,574
Messages
5,725,076
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top