Forum Discussion

Andrew365125's avatar
Andrew365125
Copper Contributor
Apr 11, 2023

Data comparison and data transformations.

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.

 

 

 

excel
Formulas and Functions
Macros and VBA
power bi

3 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Apr 11, 2023
    what you mean compare?
    can you upload some excel work book with dump data and expected result worksheets?
    • Andrew365125's avatar
      Andrew365125
      Copper Contributor
      Apr 12, 2023

      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.

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Apr 12, 2023
        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.

Resources