Forum Discussion

DonnaStaMaria's avatar
DonnaStaMaria
Copper Contributor
Dec 22, 2021
Solved

Excel data comparison to find duplicates from unique text

I have a large amount of student data each term (Between 800 to 1000+ students per term). I need to check for changes in their classes mid term to track them. Also, see who is new. So I will usually combine the Mid term data details within the first report ran at the beginning of the term and sort Column A (Unique Student ID) and Column B (Class the person is registered to) and set conditional formatting both columns A & B so I can see roughly who is new and who changed classes mid term but I have been wasting time looking line by line. I would like to know what formulas I can use to find duplicates In column A, and from the duplicates, highlight if the class in Column B has changed.
 
I guess the new ones will present themselves as unique across both columns unless you have additional suggestions. Ive created a sample table for you to see clearly, as I'm afraid I might not be explaining it correctly. 
 
Thanks for the help!
 
Row ARow BRow C
Customer IDClass NameData Explanation
123456Class A Saturday 11:00 Junior B1 Term 2-2021Student A First term registration
123456Class B Sunday 12:00 Junior B1 Term 2-2021Student A Mid term change of class
654321Class C Monday 16:30 Senior B2 Term 2-2021Student B First term registration
654321Class C Monday 16:30 Senior B2 Term 2-2021Student B who did not make any changes
125256Class A Sunday 13:45 Junior B1 Term 2-2021Student C who registered Mid term, new
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 22, 2021

    DonnaStaMaria Perhaps the attached workbook is something that could work for you. I've used formulae that work in all Excel versions. If you are on the latest, consider exchanging VLOOKUP for XLOOKUP. If you want to make something more dynamic and robust, consider using structured tables, and/or Power Query. The latter would require some learning, if you have never used it before.

     

    Rather than relying on conditional formatting and visual checks, I added some columns to each data set (First and Mid) to do som cross-check between them. If you put a filter on the extra columns you can fairly easily create listings with all changes between terms.

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 22, 2021

    DonnaStaMaria Perhaps the attached workbook is something that could work for you. I've used formulae that work in all Excel versions. If you are on the latest, consider exchanging VLOOKUP for XLOOKUP. If you want to make something more dynamic and robust, consider using structured tables, and/or Power Query. The latter would require some learning, if you have never used it before.

     

    Rather than relying on conditional formatting and visual checks, I added some columns to each data set (First and Mid) to do som cross-check between them. If you put a filter on the extra columns you can fairly easily create listings with all changes between terms.

    • DonnaStaMaria's avatar
      DonnaStaMaria
      Copper Contributor
      Dec 23, 2021

      Riny_van_Eekelen wow, this was perfect for what I needed. Thank you so much! You have saved three of us countless hours, trying to sort through hundreds of records. Thank you, from all of us in my team. 

Resources