I had the same problem but I wanted to check a whole bunch of things so I created a spreadsheet to validate company data and contact data. I export Contacts with Company data to c:\temp\contact.xls and I export companies without contact data to c:\temp\company.xls.
In my validation spreadsheet it does a lookup on these two spreadsheet names. Here’s an example, looks complicated but it’s not so bad. This one is a basic check that I have a company address - if the address field is less than 10 then I probably don’t have an address for the company in Solve. I then use Conditional Formatting - if the value = FIX then make the cell red, if it’s OK then make it green.
=IF(LEN(‘C:\Temp\[company.xls]Worksheet’!M2)<10,“Fix”,“OK”
You just copy this forumula in your new validation spreadsheet from A2 down to zz a million - depending on how long your exported Solve spreadsheet is.
If you’re interested - this is a bit more complicated - but I also look to see if tags are set. This is a bit more difficult because the tags don’t export in alphabetical order. In this one I’m looking to see if any of my Industry tags are set for each company but I’ve had to break it up because my Industry tags aren’t contiguous.
=IF(OR(COUNTIF(‘C:\Temp\[company.xls]Worksheet’!P2:U2,“*True*“),COUNTIF(‘C:\Temp\[company.xls]Worksheet’!W2:AJ2,“*True*“),COUNTIF(‘C:\Temp\[company.xls]Worksheet’!AL2:AM2,“*True*“)),“OK”,“Fix”)
This basically checks P2 to U2 and then from W2 to AJ2 and then from AL2 to AM2 to see if any of them are set to True (the tags I’m looking for). If they are True then it means I have selected an industry for this company, if none of them are true then I need to go to Solve and add the tags. Same as before it sets OK if there’s an industry tag and Fix if not and then conditional formatting to turn it red or green.
Took a bit of stuffing around but I can get a good visual validation of all my companies and contacts now and it makes fixing them much easier.
