# Syncing lists with one another

#### MikeExcel10

##### New Member
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### RoryA

##### MrExcel MVP, Moderator
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
Also please note that we have the same cross-posting rules as pretty much everywhere else.

#### MikeExcel10

##### New Member
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
52.4 KB · Views: 4
• 2020-21 school fees.png
65.3 KB · Views: 4

#### RoryA

##### MrExcel MVP, Moderator

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
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
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
Hi Rory, many thanks for this! I will take a look today in the office. Really really appreciate your help. Cheers

Replies
5
Views
245
Replies
12
Views
270
Replies
8
Views
189
Replies
1
Views
142
Replies
2
Views
1K

1,148,277
Messages
5,745,807
Members
423,978
Latest member
leodo21

### 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.

### Which adblocker are you using?

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

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