Forum Discussion

lucykennedy66's avatar
lucykennedy66
Copper Contributor
Jul 18, 2023

Excel Data Integrity Task between Databases

Hi All,

 

I have a task at present that requires a download of supplier data from one data base and a download of supplier data from a separate database. The two databases have completely different naming conventions, but are both downloaded in csv File. Both files totalling +1000 rows and 25 columns. 

 

My Task is to find the missing information in various columns between database A and B, Highlight those fields and then manually update database B. 

 

So for example database A has supplier Microsoft contract value of £100 but in database B the contract value field is blank for supplier Microsoft, so I need to highlight that as a field that is currently missing information and needs updating. VLOOKUP (conditional formatting) wrapped with ISNA doesn't appear to be working and throws up an argument error each time. 

 

Any advise on the least time consuming way to do this would be appreciated 

 

thanks,

Lucy

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 23, 2023
    first off you say "completely different naming conventions" so is the supplier Name the same in each or not? if not that is much more difficult. If so then yes a conditional formatting similar to what you suggest can work. If you have excel 365 then maybe use XLOOKUP as that has the if_not_found built in and more efficient. Either way I suggest using additional columns on the database B to show the values from database A. So IMHO I would probably to the right of database B do something like =XLOOKUP(databaseB[supplier], databaseA[supplier], databaseA[ccc], "na") where ccc would be the column to return and you either copy that formula for each column or use REDUCE and LAMBDA to create it all at once. If you send/attach/give link to a sample workbook without personal / private info I would be happy to give an example

Resources