Results 1 to 15 of 15
Thread: Help with Excel Task (2002)

20040318, 17:20 #1
 Join Date
 Mar 2004
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Help with Excel Task (2002)
OK, I hope I'm on the proper message board. I have a very specific task I want to perform in Excel 2002. Here's the details. I have 2 different spreadsheets, spreadsheet one with a list of phone numbers(Column A) and their corresponding names (Column C), and spreadsheet 2 with phone usage history that contains phone numbers but no corresponding names.
I want to compare the second spreadsheet phone numbers to the known phone numbers on the first spreadsheet and write the corresponding names in a designated name column on the second spreadsheet. If the phone number on the second speadsheet has no match on the first spreadsheet I want to write a ? in the designated name column. In short, how do I get the NAMES on the SpreadSheet that doesn't have names? This sheet is very large and has multiple instances of the same phone number.
HOW DO I DO THIS? Any help on where to start or what to do?
Thanks for your assistance!!!

20040318, 18:24 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
You didn't say how far down the names and numbers go on sheet1so let's say they are in A1:C2000. You also did not say which column on Sheet2 contains the phone numbers, so let's say they are in column A. You will need to modify the formula below if those are not correct. In row 1 of the column where you want the name on Sheet2 enter the formula below:
<pre>=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$C$2000,3, FALSE)),"?",VLOOKUP(A1,Sheet1!$A$1:$C$2000,3,FALSE ))
</pre>
Then copy this formula as far down Sheet2 as the numbers go.Legare Coleman

20040319, 11:44 #3
 Join Date
 Mar 2004
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
Legare  I tried but the formula would only returned ? in the column for each phone number in sheet2. OK, sheet1 has the names (column A) and corresponding numbers (column C) has 56 entries ($C$56). In sheet2 the phone numbers are in Column K (approximately 600) and I want to place the matching name from sheet1 (column A) in sheet2 (column M) for each of the 600 numbers in Column K of sheet2.
Plus, how do I mass copy of the formula?
Hope this additional info helps. I think your on the right track, but I know so little about Excel.

20040319, 21:56 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
OK, that is quite different. Put the formula below in cell M1 on Sheet2 and copy it down as far as it needs to go:
<pre>=IF(ISERROR(MATCH(K1,Sheet1!$C$1:$C$56,0)),"? ",OFFSET(Sheet1!A1,MATCH(K1,Sheet1!$C$1:$C$56, 0)1,0,1,1))
</pre>
Legare Coleman

20040320, 11:44 #5
 Join Date
 Mar 2004
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
Legare  Still not working. It's got to be something simple. Can I send the Excel file to you? It might make more sense if I do.
Thanks

20040320, 11:47 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Help with Excel Task (2002)
You can attach a copy of the workbook to a post. It must be below 100 KB; you can zip it if necessary. Remove sensitive information or replace it with dummy data.

20040320, 16:59 #7
 Join Date
 Mar 2004
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
OK, here's a stripped down verion of the file. If a formula will work with this it'll work for the real thing. I've removed all impotant info, I think. The Numbers sheet has stripped down phone numbers and the names have been change to uniqe letters. Several columns were removed from the Info sheet and the phone numbers again stripped down, but all columns should be the same as my original, just alot less numbers to sift thru on the Info sheet.
Thanks for your assistance!!

20040320, 18:17 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help with Excel Task (2002)
Try this:
=IF(ISERROR(MATCH(K1,Numbers!$A$31:$A$60,0)),"??", VLOOKUP(K1,Numbers!$A$34:$C$60,3,FALSE))
Steve

20040320, 20:46 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
You said that your sheets were Sheet1 and Sheet2, not Numbers and Info. Steve's formula should work.
Legare Coleman

20040321, 13:25 #10
 Join Date
 Mar 2004
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
GREAT JOB GUYS!! It's working, BUT how do I get the K cell "lookup_value" to change automatically for the corresponding row? I have to change it manually to make it work, and the actual spreadsheets of data will have thousands of phone numbers to compare. Sorry I know so little about Excel. Any suggestions?
Thanks

20040321, 14:23 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
Enter that formula into the first row. Then select the cell with the formula. There will be a small square in the lower right corner of the cell border. Put the cursor over that square and it should turn from a large cross to as smaller thin cross. left click and drag down to copy the formula to the other cells. The lookup value cell reference should automatically update to the corresponding row.
Legare Coleman

20040321, 18:22 #12
 Join Date
 Mar 2004
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
Thanks for all your help Legare. That works great! And I've started to learn some essential information about Excel that I can use in the future.
You're the GREATEST!!

20040321, 18:28 #13
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Help with Excel Task (2002)
Steve,
Since I am doing a class where I need to show VLOOKUP, I took interest in your solution to the example. While I know how to use VLOOKUP, having a meaningful example that I don't have to develop <img src=/S/smile.gif border=0 alt=smile width=15 height=15> is always useful.
However, I am very confused. Why would the MATCH start on $A$31 and the VLOOKUP on the false result start on $A$34?
I downloaded the sample spreadsheet. First time I went to use it, Excel complained about a broken spreadsheet (in addition to saying there were macros) and repaired it. Being suspicious, I downloaded it again and it was ok the 2nd time (but still with macros).
I inserted your formula in col M of the Info sheet and filled down. With your formula the way it was, I got "??" as the name for 2813735284, which isn't even close. When I changed the formula to start in row 1 of the Numbers sheet for both the MATCH and the VLOOKUP, I got C for 2813735284 which looks correct.
Fred

20040321, 20:54 #14
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Excel Task (2002)
I just noticed an error in Steve's formula. Please change it to:
<pre>=IF(ISERROR(MATCH(K1,Numbers!$A$1:$A$60,0))," ??",VLOOKUP(K1,Numbers!$A$1:$C$60,3,FALSE))
</pre>
Legare Coleman

20040322, 01:45 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help with Excel Task (2002)
<img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
I apologize for any confusion.
Legare, thanks for correcting my error.
Steve