comparison > 1

[ksbrace]

Hello,
I have a spreadsheet with several columns (fname, lname, address, city, etc....) My boss gave me a spreadsheet with just last names and wants a spreadsheet with all the data but only of the names on his sheet. is there a built-in function that can assist or can someone help me out. Thanks in advance!
Kelly

 

[SkipVought]

I'd use INDEX & MATCH.

But first, you must add a helper column to the source data that concatenats the first & last names for lookup. Assuming that your source table has one row of headings in row 1, as all good tables do,
[tt]
=A2&B2
[/tt]
Enter NameKey in row 1 of this column.

Then Name your Ranges, using Insert > Names > Create -- Create names in TOP row.

Copy the headings, of the table to your sheet from you boss. Replace any SPACES in the headings with UNDERSCORE chracter

Your formula, assuming that your list headings begins in A1...
[tt]
c2: =INDEX(INDIRECT(C$1),MATCH($A2&$B2,NameKey,0),1)
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

Thanks for your reply but excuse my ignorance.....

I have one column (last name) from my boss's spread sheet.

The sheet that has all the data has a - p columns with relevant data. last name, first name, address, phone, etc...

my boss's sheet is just a subset of the people list on the sheet that has all of the data.

He wants to see all of the data of the names on his sheet.

Does your answer remain the same?

 

[SkipVought]

You NEVER have more than ONE Smith or Jonse, etc?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

There are duplicate last names, yes. He nor I thougth about that....Ok, so now I have one with both first and last names.
Do I add this sheet to the source workbook?

 

[SkipVought]

How many names in your bosses list?

You really need first & last.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

I have first and last now in his list.....he has about 3200 in his list and the main list has 8635

 

[SkipVought]

So now you can do what I outlined above.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

But first, you must add a helper column to the source data that concatenats the first & last names for lookup. Assuming that your source table has one row of headings in row 1, as all good tables do,
this is to concatenate the first and last names, correct? Also, do I want to drag this all the way down 8635 rows?
=A2&B2

Enter NameKey in row 1 of this column.

using office 2007, not seeing this option. Also, does this go in the source spreadsheet or my bosses?
Then Name your Ranges, using Insert > Names > Create -- Create names in TOP row.

Copy the headings, of the table to your sheet from you boss. Replace any SPACES in the headings with UNDERSCORE chracter

does this formula go into the source or bosses spreadsheet?
Your formula, assuming that your list headings begins in A1...

c2: =INDEX(INDIRECT(C$1),MATCH($A2&$B2,NameKey,0),1)


I'm sorry for the confusion, I'm just not grasping how it will pull from one spreadsheet and populate another. I can't thank you enough for your help!

 

[ksbrace]

Also, should I combine these sheets into one workbook?

 

[SkipVought]

ALL the way down, YES.

using office 2007, not seeing this option. Also, does this go in the source spreadsheet or my bosses?

On your Source Data sheet...
1. Select ALL headings and data (all 8000+ rows)
2. Formulas TAB > Create form selection command...

does this formula go into the source or bosses spreadsheet?

bosses sheet.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

ok, now do I want to have both sheets in the same workbook?

secondly, where exactly do I paste this formula?

 

[SkipVought]

Your sheets could be in the same workbook. That would be handier for you.

on the bosses sheet, it should look something like this, starting in A1...
[tt]
fname lname address city
Ann Jones
Sam Jones
Ruth Duff
....
[/tt]
So the formula will be entered in the address column in row 2
[tt]
c2: =INDEX(INDIRECT(C$1),MATCH($A2&$B2,NameKey,0),1)
[/tt]
Copy the formula.

paste across & down thru all cells BELOW the headings and the to RIGHT of the names.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

ok, why are some giving me #REF! HOw do I troubleshoot?

 

[ksbrace]

actually, it happens when they hit AA....I thought it stopped at P, but it actaully goes to AD. Please advise.

 

[SkipVought]

What is the column heading in that column where you have the #ref! errors?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

Skip,
thanks for hangin' with me on this! Ok, AA is actually coming in, but AB, AC and AD are not. The headings are EmpPhone, Store and CheckNo. No spaces in any headings. Please advise if you have time.
Kelly

 

[SkipVought]

In any formula in the column of interest, lets assume AA in row 2...
[tt]
=INDEX([highlight]INDIRECT(AA$1)[/highlight],MATCH($A2&$B2,NameKey,0),1)
[/tt]
Highlight the expression as noted and hit F9, and you should see EmpPhone. if that is, indeed, the heading value. Once noted, hit the ESC key to return the expression!!! If you get anything else, report back.
[tt]
=INDEX(INDIRECT(AA$1),[highlight]MATCH($A2&$B2,NameKey,0)[/highlight],1)
[/tt]
Highlight the expression as noted and hit F9, and you should see a NUMBER. Once noted, hit the ESC key to return the expression!!! If you get anything else, report back.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ksbrace]

I get #REF! and it's just the phone number, no formulas or anything. I actually even removed a few columns to put to just 26 columns to see if it was the double letters(aa,ab,ac) but I got the same result.

 

[SkipVought]

I get #REF! ...

from WHAT expression?

Do you know what that means?

Knowing that, whould lead you to check to see if THAT reference, the heading name, is a valid Named Range. Look in the Name Box. It's probably NOT THERE! So how do you fix that? ADD the missing reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}