Syncing lists with one another

MikeExcel10

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Also please note that we have the same cross-posting rules as pretty much everywhere else. ;)
 

MikeExcel10

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • 2019-20 school fees.png
    2019-20 school fees.png
    52.4 KB · Views: 3
  • 2020-21 school fees.png
    2020-21 school fees.png
    65.3 KB · Views: 3

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Rory, many thanks for this! I will take a look today in the office. Really really appreciate your help. Cheers
 

Forum statistics

Threads
1,143,620
Messages
5,719,788
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top