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

[lorikgator]

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

 

[lorikgator]

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!