Syncing lists with one another

[MikeExcel10]

Hello everyone, hope you're well!

I'm new to ME but thanks for such valuable content.

After of hours of trial-and-error, I'm stumped... *really* hope someone can help ?

I have a list of schools in alphabetical order. I also have the corresponding annual tuition fee per student on separate sheets.
Each year, the number of schools varies; ie in year 1 = 82 schools, year 2 = 105 schools, year 3 = 122 schools, year 4 = 113 schools, year 5 = 133 schools, year 6 = 128 schools etc.

Since the number of schools varies year-on-year, I am struggling with the ability to compare like-for-like. Ideally, I would like to compare the trends of the *tuition fee per student over time* (for example) so that I can see the change on an annual basis.

If the number of schools each year remained fixed, this would be simple. But, because the number of schools changes each year (and the list is in alphabetical order), I can't:
a) immediately identify which school(s) are added/deducted from the previous year
b) align the corresponding data for like-for-like comparison

I've got to this point with a lot of manual work - which defeats the point of Excel's magic. Essentially, is there a way to align the varying yearly list of schools so that I can compare their corresponding data like-for-like? If so, how?

Thanks so so much, honestly, I really need some help! #n00b

 

[RoryA]

Welcome to the forum.

Are you saying that you have one master list of all the schools already, or do you just mean that the list on each sheet is in alphabetical order?

 

[RoryA]

Also please note that we have the same cross-posting rules as pretty much everywhere else.

 

[MikeExcel10]

Thanks!

The list on each sheet is in alphabetical order, but the list on each sheet differs. Each sheet represents the tuition fees for all the schools in that particular year. The problem I am having is that the number (and order) of schools differs year-on-year because some years there are new schools entering the market, and other years the schools may have closed down.

Without aligning the LHS list of the school names (on each sheet), I cannot compare like-for-like on the tuition fees.

Separately, I have taken all the schools from 5 sheets (representing 5 years), compiled them into one list, then deleted duplicates; but this doesn't help because the tuition fee info is not connected.

Does that make sense?

What do you suggest? Cheers Mike

 

[RoryA]

Since you have 365, I would suggest using the Get and Transform tool (a.k.a. Power Query) to extract the data from every sheet and append it all to one master table that you can then pivot. There's an article here that will demonstrate one way to do that. You will need to add a year column to each query so that you have some way of identifying them to be able to pivot.
If you get stuck, if you can upload a file somewhere (eg OneDrive or Dropbox) and post a link here, I'll have a look when I have a minute.

 

[MikeExcel10]

Hey Rory, really appreciate your guidance. I've reviewed the article and am also watching youtube videos to help get to grips with it.

In the meantime, here is a snippet of the problem I am working with - perhaps you'd be able to take a look? Example-SchoolsTuition.xlsx

Many thanks, Mike

 

[RoryA]

I've added some queries and a pivot linked to the allyears query to this version. You'll note that there is a little bit of tidying up of school names to do as they are not always consistent.

 

[MikeExcel10]

Hi Rory, many thanks for this! I will take a look today in the office. Really really appreciate your help. Cheers