Excel experts please help!

[JustinP1]

Hi Guys,

In a bit of a jam. A couple of years back we bought two lists. Basically a list of 20,000 Primary Schools and 4000 Secondary Schools.

The data was pretty inaccurate so from this time we have been editing it with peoples real names and stuff like that. I have just found out now I am doing a mailing to *just* the Primary Schools that soon after this time both lists were amalgamated and mixed up and sorted by alphabetical order in Excel.

So, I either have the choice of using the inaccurate data which was supplied two years ago or working out how to split the lists back up.

What may make life more easy is that each name and address has its own individual code in the first column like:

34567 Mrs Jones St Edwards School 1 New Drive etc.

These have not been changed. Is there a way of using the original lists individual code numbers to selectively delete these rows from the new lists to form a list of the updated data with just the Primary Schools?


A huge amount of thanks in advance for anyone who has any ideas how to do this!

[FunkyGibbon]

I'd use Access to help here.

Import your spreadsheet that has the clean but combined data.

Import your spreadsheet that has the primary only data (but is not clean)

You can then use the query tool to join the 2 tables on the unique ID you have kept, that way you can just get all the record ID from the primary data but with the clean person data from the combined sheet.

You can then export back into excel clean primary school data.

This might not make any sense if you are unfamiliar with Access.

FG

[whygee02]

Hilight the column with the school name in it.

Then click Data, Filter, Autofilter.

Then on the drop down grey filter box, choose custom. Then show rows where the name contains 'Primary'

HTH

[tigger1]

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

[wiggy001]

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

Yep - vlookup the unique code in the clean list again the codes in the primary school list. The one's that aren't here will appear as #N/A. Sort on this column and delete the #N/A ones

[tigger1]

wiggy001 said:

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

Yep - vlookup the unique code in the clean list again the codes in the primary school list. The one's that aren't here will appear as #N/A. Sort on this column and delete the #N/A ones

- although I'd left it as an exercise for the reader as it's "easier" to learn how it works that way

As an aside, if you're going to re-order a column, it might be prudent to introduce an "order" column numbered 1 to "x" in case you ever want it in it's original order (assuming it's not A-Z) - although in reality order doesn't usually matter.

[FunkyGibbon]

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

DOH - forgot about that - indeed it is your friend and much easier than pi55ing about with Access.

[tigger1]

FunkyGibbon said:

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

DOH - forgot about that - indeed it is your friend and much easier than pi55ing about with Access.

Yes and no - this "problem" sounds like it should always have been based in Access anyway - excel is great for calculation, spreadsheets(!) - not so great for manipulating lists (+databases!). Access on the other hand...pretty crap for sums...but surprisingly ok for database work

[FunkyGibbon]

tigger1 said:

FunkyGibbon said:

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

DOH - forgot about that - indeed it is your friend and much easier than pi55ing about with Access.

Yes and no - this "problem" sounds like it should always have been based in Access anyway - excel is great for calculation, spreadsheets(!) - not so great for manipulating lists (+databases!). Access on the other hand...pretty crap for sums...but surprisingly ok for database work

I totally agree, I should have said "and much easier than pi55ing about with Access if you are only familiar with excel"

[JustinP1]

tigger1 said:

wiggy001 said:

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

Yep - vlookup the unique code in the clean list again the codes in the primary school list. The one's that aren't here will appear as #N/A. Sort on this column and delete the #N/A ones

- although I'd left it as an exercise for the reader as it's "easier" to learn how it works that way

As an aside, if you're going to re-order a column, it might be prudent to introduce an "order" column numbered 1 to "x" in case you ever want it in it's original order (assuming it's not A-Z) - although in reality order doesn't usually matter.

When *I* amalgamated the two lists onto the same file they were left in order so that all the primaries at the top and then below were the secondaries. However, someone then thought to 'sort' the list by alphabetical order and thus mixing them up. I did think to look for 'Primary' in the names and search for that, but that would only catch about 70% some schools dont have that in the title.

Thanks for your help so far! I am so glad that I dont have to spend the next few days editing them out by hand!

I am a bit stuck though - and forgive me for being crap at this. How *exactly* would I use Vlookup? In the help section it is telling me that needs to be used in a formula?

Cheers again!

[FunkyGibbon]

JustinP1 said:

tigger1 said:

wiggy001 said:

tigger1 said:

vlookup is your friend.

Go have a look in the help file for it, then come back if you need help - but it should be a piece of....

Yep - vlookup the unique code in the clean list again the codes in the primary school list. The one's that aren't here will appear as #N/A. Sort on this column and delete the #N/A ones

- although I'd left it as an exercise for the reader as it's "easier" to learn how it works that way

As an aside, if you're going to re-order a column, it might be prudent to introduce an "order" column numbered 1 to "x" in case you ever want it in it's original order (assuming it's not A-Z) - although in reality order doesn't usually matter.

When *I* amalgamated the two lists onto the same file they were left in order so that all the primaries at the top and then below were the secondaries. However, someone then thought to 'sort' the list by alphabetical order and thus mixing them up. I did think to look for 'Primary' in the names and search for that, but that would only catch about 70% some schools dont have that in the title.

Thanks for your help so far! I am so glad that I dont have to spend the next few days editing them out by hand!

I am a bit stuck though - and forgive me for being crap at this. How *exactly* would I use Vlookup? In the help section it is telling me that needs to be used in a formula?

Cheers again!

PM me and I'll send you an example

[tigger1]

I'll try to explain in a really simple table here.
2 columns : -

A B
ID Letter
1 A
2 B
3 C
4 D
.
.
.
26 Z


If you have another column (C, first entry in row 2) that lists the position in the alphabet, then you can get the letter by doing: -

=vlookup(C2, A$2:B$27, 2, false)

• C2 is the value you're looking for in the lookup table
• A$2:B$27 is the table ($ symbol lets you copy this without changing the range)
• 2 means to return column 2 in the table(the letters)
• false just means that it only returns when you get an exact match

HTH

[2something]

AFAIK the table referenced by vlookup needs to be in alphabetical order. You probably read the help file, but if not this catches a lot of people out.

ETA: It seem like the data you will be referencing will be in alphabetical order.

Edited by 2something on Wednesday 9th January 16:23

[tigger1]

2something said:

AFAIK the table referenced by vlookup needs to be in alphabetical order. You probably read the help file, but if not this catches a lot of people out.

ETA: It seem like the data you will be referencing will be in alphabetical order.

Edited by 2something on Wednesday 9th January 16:23

Not if you make the 4th argument "false" - as I mentioned above. We're only looking for exact matches then, so there's no problems with ordering

[Olf]

YHM!

[JustinP1]

Tigger, you have mail!

[JustinP1]

Olf said:

YHM!

You have got some too about the 'other thing', not exactly excel related!

[tigger1]

JustinP1 said:

Tigger, you have mail!

You have mail in about 2 minutes ;0) (off to read mine)

Justin - you should have mail from me, but yes, although it won't be until tonight that I get to look at it (honestly need to do a little bit of work here in between PH browsing!)

Edited by tigger1 on Thursday 10th January 16:04

[tigger1]

Justin - just in case the email didn't get there, you *should* have mail.

[Olf]

tigger1 said:

Justin - just in case the email didn't get there, you *should* have mail.

x2