Sorting a list of people by reference to another list

[Darkiness]

Hi
I'm a first time user on the forum. Nice to meet one and all.

I love using excel but only learn what I use.
My problem is this. I have a list of about 25000 people in rows with data name address tel number etc and also final column which is the local club they are in (or at least assigned to).
Problem is that we have about 350 clubs and we have just reorganised into districts across the country (7 of them)

I want to use excel to quickly sort people into districts based upon their club (rather than their home address) without doing it manually by comparing the membership list with the districts list of clubs.

Is there an easy way to do this?

Any help would be much appreciated.



D

[whatsup]

Hi

Use a further column to establish the sort order by formula: Let's say starting in A2
A2 is the name of member
B2 the district
C2 the club
Then
D2 = B2&C2&A2

Copy the cells formula until the end of the list, and afterwards select the cell D2. Click Sorting ascending or - descending.
Before sorting make sure that no empty column/row seperates your list. As always give it a try on a copy of the origin.

[Darkiness]

many thanks for this. I will give it a whirl

[Darkiness]

Tried to do that but once it came to trying i didnt really know what it meant.

It might be the way I have described it. In column G of the 25000 rows, there are the names of 356 clubs.

What I want to be able to do is to type in a formula which in non excel terms would translate as

"For each row where the cell in column G shows the words (Halifax, Leeds, Doncaster....York) then insert the H cell for that row the words District 1"

and then

"For each row where the cell in column G shows the words (Redruth, Torquay, Bristol, Taunton.... Bath) then insert the H cell for that row the words District 4"

and so on.

better still would be if I could make the formula "read" the list of clubs that belong to each district, rather than have to type them into the formula

[whatsup]

Well, if that's what you want ...

What does your list of clubs look like?

Assuming the clubs are listed in one column (AA) and the districts in the following (AB) use VLOOKUP()
H2 =VLOOKUP(G2,$AA$2:$AB$1000,2,0)

Adjust the red part of the formula to the area were actually the clubs and districts are listed and copy the formula down the column. If you don't get it working describe were data is located, or load up a short example of the file (with personal data deleted)

[Darkiness]

I've got to thank you for your patience on this.
I have been using a dummy sheet to try and work out the method and I have tidied that up and attached it.
the sheet probaby explains it better than my narrative

[ScottA]

Darkiness

You are really close. What you need to do (easiest way) is put all of your club names in a single column with your districts in a second column right next to it. See Columns N & O.

Now you can use the formula whatsup provided:

Originally Posted by whatsup

Assuming the clubs are listed in one column (AA) and the districts in the following (AB) use VLOOKUP()
H2 =VLOOKUP(G2,$AA$2:$AB$1000,2,0)

Adjust the red part of the formula to the area were actually the clubs and districts are listed and copy the formula down the column.

[ScottA]

D -

If you already have a worksheet with the data for the clubs and districts that data doesn't have to be in with the same worksheet as the data for your members.

Note this example. The district info is on sheet 2.
And now the formula changes to

=VLOOKUP(G2,Sheet2!$A$2:$B$11,2,0)

[Darkiness]

ScottA and whatsup - I am very grateful to you both for pushing me to end point and then for getting me there.

I can't tell you how much you have helped me.
Thank you very much, indeed

[ScottA]

D -

Here are a couple of things to think about.

If this is a "one off" thing you might want to consider using a helper column then do a copy and "past values only" to the District column.

On the other hand if the district data could change over time it might be convenient to have the formulas in place.
Try this in the example - in the district data > change the value for b1 from "District 1" to something like "Gold District". You should see that any of the member data with Members Club = b1 is automatically updated to Gold District.