Data comparison and data transformations.

[Andrew365125]

I have a list of parts that I have scanned into Excel, I need to compare them to a list that was derived from Access that is now in Excel. I can compare in Power BI if that is best.

The list I scanned is one column and the list I compare to is 2 columns, I was able to get the list separated using this formula, =LEFT(('sp9'!B55),FIND("-",('sp9'!B55))-1)

Here is a sample of data I need to compare. The first is the main list I need to compare to, the second picture is the data I scanned in, and the third photo is what I transformed the second dataset into. 

I transformed it to this

How can I make the transformation to start transforming the data in the next column once no more inputs are left in the column or, is there a simpler way to compare the data? 

Should I be thinking VBA, Power BI, Formulas, or something else, I don't want to take 3 months (literally) to compare the data when it could be automated in some way.

[#2]

what you mean compare?
can you upload some excel work book with dump data and expected result worksheets?

[#3]

@peiyezhu What I mean by compare is, I make a list of all the parts in a drawer, look at a list of what is supposed to be in that drawer according to our database, if the database says a part is in that drawer then I  mark it green, if the database says it is in a different drawer I correct it and mark it green, and finally if the part does not exist in the database I add it in and mark it yellow. I will try to email you a sample workbook that has sample data since I am unsure of how to upload the data.

[#4]

Sample Database
row 48
mark green
2454 27-2-4 9A 5/8/2007

Sample transformed data
row 26 why mark yellow?
2454 27-2-4 9A

I think they are exactly same or this row exist in database.
why this row is marked yellow?


Do you really want to mark with color rather than add extra column to mark 'missing' then it easy to filter or deal with condition format?
re:says it is in a different drawer
If you have different situations or mass data,the vba/UI may render more quickly than fill cells with background color.