Merging Data with Similar Entries

[mmeigooni]

Hello all,

Hopefully someone can help me with this...

I have two spreadsheets. The first one has all the schools in Los Angeles county in one column some accompanying data (class size, teacher:student ratio, etc.).

The second spreadsheet is also a list of schools. Some of the schools are in common with the first spreadsheet (meaning they are in LA county) and some of the schools are not on the first spreadsheet (they are not in LA county). This second spreadsheet also does not have any accompanying data.

What I would like to do is take the data from the first spreadsheet and put it with the relevant school in the second spreadsheet.

There is one problem. The school names do not match identically. For example, in the first spreadsheet the school's name may be "Alhambra Senior High School" while in the second it is just "Alhambra".

I've tried using macros with the find find utility (ctrl-f) and copy and pasting, but I can't seem to get it to do what I need. Any ideas on how to attack this?

Thanks in advance!

 

[mmeigooni]

ttt

 

[rallcorn]

Assume you can't just match the first word of both because you might have Alhambra High School, Alhambra Middle School, etc. ??

 

[mmeigooni]

The first word works sometimes but not always. For example, spreadsheet 2 has "Taft" and spreadsheet 1 has "William Howard Taft High School"

 

[rallcorn]

is sheet 2 always the most abbreviated?

 

[mmeigooni]

Yes it is.

 

[javalover]

is there something other than the school name that sheet 1 & sheet 2 might have in common (i.e. address)?

my first thought would be to highlight sheet 1 in a color, copy and paste into sheet 2 and then sort alphabetically.

you could go through manually to see which row to delete, or you could remove duplicates (that is if you have a column with say like addresses).

certainly, there's Excel gurus out there that might have a better idea.

good luck!

 

[mmeigooni]

That is a good idea. Unfortunately sheet 2 has only school names.

Thanks though!

rallcorn, going back to your question about matching the first word. Like I said, this isn't always the case, but it usually is. As in probably 80-90% of the time. So if you have something in mind for matching the first word, it would still help quite a bit.

 

[rallcorn]

I do have an idea

on sheet 1 (more detailed), insert column to create a name that is just the first word. here's that formula: =LEFT(A2,(SEARCH(" ",A2,1)-1))

Then use this formula in sheet 2 to match the one-word school names & index in the info from sheet 1:
=IF(ISERROR(INDEX(E$7:E$10,MATCH($D12,$D$7:$D$10,FALSE),1)),INDEX(E$7:E$10,MATCH($D12,$C$7:$C$10,FALSE),1),INDEX(E$7:E$10,MATCH($D12,$D$7:$D$10,FALSE),1))

of course the cell references will be different, but this should give you the gist of it.