Excel lists - compare?

[jpryan]

Hi all,

 

I am downloading lists of pupils in school on a weekly basis and updating a separate spreadsheet. The lists never match as there is so much movement in and out of school. Short of actually checking 845 rows side by side. is there a clever way of checking the two lists of names and then resolving mismatches?! Thanks in anticipation.

[mitchell1981]

I use this plugin for Excel: https://www.ablebits.com/excel-suite/index.php

 

It can match up tables based on a key field, plus lots of other stuff. i think there is a free trial, so you can see it if works for you.

[howartp]

I saw a Facebook Short on this at the weekend, so i've searched the basic concept they used. I didn't carry on watching it but I clocked it in my memory as a thing to look back at later.

 

It's something like:

[Koldov]

A slightly more 'manual' way (if it's Excel)....

 

A long (really long) time ago when I used spreadsheets a lot, the way I used to compare lists was with a VLOOKUP, but it doesn't 'automatically' resolve any errors, just errors on them, although there might be a better way now as I think there's new 'lookup' functions.

 

I've tried a while ago as well, comparing the children in SIMS with those in Google and then in Adobe (just to make sure all pupils on roll had those other accounts created), but it was such a pain with legal first/last name vs. preferred first/last name I abandoned it.

 

This calculation 'looks up' the value of the cell E6 in the list B4 to B6 and if it matches exactly returns the value else #N/A:

 

=VLOOKUP(E6,B$4:B$6,1,FALSE)

 

Edited July 3, 2023 by Koldov

[howartp]

To be honest I use the vlookup() a lot for this.

 

But I spend time making sure all spreadsheets i'm working on can key off the UPN (or sometimes App-Ref for admissions) for repeatability when (re-)downloading the same data.

 

When parents fill in their application form to year 7, there's a blank "Office use only" box that my admin team know they have to populate as soon as they can when forms are submitted, even if they're not processing the form data for another week/month.

 

That way, I can key off that field when eg emailing parents about which form group they've been put in (which I did on Friday afternoon).

[CTIDTech]

What are you comparing the lists for? We have tools to manage this, mapping any correlation changes and exporting to CSV if required, otherwise this is a big headache and possibly using up your teams time on repetitive non-productive tasks.

 

We used to do this manually, now we can run automatically every 5 mins if we want - (we don't - we do it every day after the MIS is updated). It even manages and creates rules, runs scripts or sends emails etc. based on whatever the action you want to happen.