Adding to a database - checking for matches and identifying differences in records

[samstar85]

Hi all,

I am in the process of creating a database which contains 20 columns of data and I will need to regularly add to, and am in the process of creating a workflow to the database to check for matching records before I add the new lines.

These are the steps taken so far:

1. Identify brand new item codes, as these lines will definitely need to be added to the database

2. Identify existing item codes, but the variant doesn't match (i.e. same product, different colour) as these will need to be added but will need to be reviewed first (somehow highlight the non matched data)

This is the step I'd like to make next, however I am unsure on the best method to do this:

3. Identify existing item codes, with matching variant, but where any of the remaining columns don't match - ideally by showing both the new record and the existing one below, and somehow highlighting the column where the data doesn't match so manual checking can be done for these lines.

If anyone has any suggestions they would be able to give to as to how to achieve this, parts of this, or improve this process, I'd be incredibly grateful.

Many thanks

[mceleavey]

Load the table in and join to the new records. The ones that don't match are new, the ones that do match are already in the system, and you can then run a comparison to see if there are differences. If not, drop the replications, if so, those are up for review.

Alternatively, load in the existing table, and the new records, then union them together and apply a unique tool to drop replications. Apply a sum tool and group by and count the key field(s). Filter those where the answer is great than one. This means these records are the same records, as in same unique ID, but with variations in the data.

M.

[samstar85]

Thank you @mceleavey, I've used the join tool to find the initial matches and drop out those that don't match however, its the comparison part that I am struggling with. As part of your first suggestion, was there a tool in particular that you would suggest for the comparison to give me the desired result? I have been trying to Transpose the data, and apply the headers as 'New Lines' and 'Old Lines', with the rows being the original column header so to enable comparison (Pass/Fail) with a formula in a third column, but I am struggling to get the column headers I am after.

I will try a new workflow with your second suggestion now and see how that works out as well.

Many thanks for your help

[mceleavey · Accepted solution]

Hi @samstar85 ,

this is a simple method which should be workable, but without seeing the data you may need to employ a macro here.

Anyway, I've attached an example which should make it clearer.

Here's is the workflow:

[samstar85]

Thank you @mceleavey, this has worked well to separate the lines for review.

I may be pushing my luck here but I don't suppose you have any suggestions how to mark the cells that don't match? Do you know if there is a way to use an IF statement to make the text red, or bold for example? I have a large number of sheets to compare to add to the database and am trying to make this process as quick and simple as possible. 

Thanks again

[mceleavey]

Hi @samstar85 ,

Yes, the new records are those that don't match, so you don't need to mark anything any colour and then manually check. Import all records from all spreadsheets and follow the same logic.

I would need an actual example of what you mean to be more clear.

If you load in all spreadsheets (use the Directory and Dynamic Input method) and run comparisons to ensure there are no duplications, then input into the table you won't have to go through any manual checking.

[samstar85]

Hi @mceleavey 

Thank you for this, I was unaware of the Directory and Dynamic Input method so that will be a great help. I am fine with the new lines, its the existing ones that we have to review as some may need to be added as a replacement for the existing database entry but this has got me most of the way there. 

Hope you have a great day

[mceleavey]

Thanks @samstar85 , glad I could help.

Ping me a direct message if you need any further help.

M.