prob with vlookup

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
Hello,

I've got a problem. I've illustrated it with an example below:

Sheet 1
Employee Name Designation
John Manager
Smith Executive
Allan Sales
Smith Sr. Manager
Steven Sales
Ann Receptionist

Sheet 2
Ann Receptionist
Smith Executive
Harris #N/A
Chris #N/A
John Manager
Adams #N/A
Smith Executive
Allan Sales

Let's tale for example there are 2 people in the company by the name of Smith. (executive and Sr. Manager). While doing a vlookup in sheet 2, in both places you will only get the result for designation as 'executive' for Smith. This is because vlookup only picks up the 1st value and matches it against corresponding database.
Is there any way to eliminate this problem or better still, is there any other formula that can be used to match "smith" against "executive" and "smith" against "sr. manager" while comparing data bases? Pls revert back ASAP.

Thanks
Straus
 
I want to list the multiple phone numbers against the account code...


On 2002-10-07 08:17, lasw10 wrote:
So do you want the account number repeated twice on Sheet2 (in which case I really don't understand what you're trying to do) or do you want to list the multiple phone numbers against the account code...

i.e

Col A Account Number
Col B First Tel No
Col C Second Tel No
Col D Third Tel No

etc...
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Assuming you want multiple numbers adjacent to the account number...ie.

1280049857 - 226971611 - 226971612

then here's something you can amend to fit your needs...first it copies and pastes sheet1 into sheet2 and then sorts it all by account number and then phone number. it then works out whether or not there is more than one number assigned to that number and if so puts it into the next column. Once it has finished that it runs through the accounts again and clears any accounts that appear twice (as those numbers have now been added to the first appearance of the account code). The data is then resorted (with blanks now replacing the original replicated account data) thereby giving you a nice clean final result.

The data will be pasted into A2:B2 sheet2 and it has been set up to work up to 5 numbers (F).


Sub MATCHACCOUNTS()

Sheets("SHEET2").Range("A2:F100").ClearContents
Sheets("sheet1").Range("a1:b100").Copy
Sheets("sheet2").Range("a1").PasteSpecial xlPasteValues
Sheets("sheet2").Range("a2:b100").Select
Selection.Sort KEY1:=Sheets("sheet2").Range("a2"), ORDER1:=xlAscending, KEY2:=Sheets("sheet2").Range("b2") _
, ORDER2:=xlAscending, HEADER:=xlNo, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

DATA = Sheets("SHEET2").Range("A2:A100")
CROW = 2

For Each AC In DATA

If AC = "" Then Exit For

If Sheets("SHEET2").Range("A" & CROW + 1) = AC Then Sheets("SHEET2").Range("C" & CROW) = Sheets("SHEET2").Range("B" & CROW + 1)
If Sheets("SHEET2").Range("A" & CROW + 2) = AC Then Sheets("SHEET2").Range("D" & CROW) = Sheets("SHEET2").Range("B" & CROW + 2)
If Sheets("SHEET2").Range("A" & CROW + 3) = AC Then Sheets("SHEET2").Range("E" & CROW) = Sheets("SHEET2").Range("B" & CROW + 3)
If Sheets("SHEET2").Range("A" & CROW + 4) = AC Then Sheets("SHEET2").Range("F" & CROW) = Sheets("SHEET2").Range("B" & CROW + 4)

CROW = CROW + 1

Next AC



Data2 = Sheets("SHEET2").Range("A2:a100")
CROW = 2

For Each AC2 In DATA

If AC2 = "" Then Exit For

If Sheets("SHEET2").Range("A" & CROW + 1) = AC2 Then Range("A" & CROW + 1 & ":" & "F" & CROW + 1).ClearContents

CROW = CROW + 1

Next AC2


Sheets("SHEET2").Range("A2:F100").Select
Selection.Sort KEY1:=Sheets("sheet2").Range("a2"), ORDER1:=xlAscending, HEADER:=xlNo, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub
 
Upvote 0
Hi,

I ran your macro and it did work up to some extent, However I did not get the desired result. Your macro is picking the different tel nos pertaining to the same A/c no. and pasting them adjacent. (ie column C, D etc ) and deleting the repititive A/c nos.

thatls not what i precisely want. All I want is a simple vlookup ( yes! a vlookup...because there can be additional A/c nos in sheet 2 , the reason being that sheet 2 contains new A/cs of new customers that were not there in sheet 1 )

The only difference being that if a particular A/c no. is repeated in sheet 2 ( simply because there is more than 1 tel no. in sheet 1 ), the macro should pick up the different tel nos. I hope you've got what i'm trying to say. I know i'm being quite a pain in the neck, but pls try to help me out in this one

Thanks a ton
straus

On 2002-10-07 08:37, lasw10 wrote:
Assuming you want multiple numbers adjacent to the account number...ie.

1280049857 - 226971611 - 226971612

then here's something you can amend to fit your needs...first it copies and pastes sheet1 into sheet2 and then sorts it all by account number and then phone number. it then works out whether or not there is more than one number assigned to that number and if so puts it into the next column. Once it has finished that it runs through the accounts again and clears any accounts that appear twice (as those numbers have now been added to the first appearance of the account code). The data is then resorted (with blanks now replacing the original replicated account data) thereby giving you a nice clean final result.

The data will be pasted into A2:B2 sheet2 and it has been set up to work up to 5 numbers (F).


Sub MATCHACCOUNTS()

Sheets("SHEET2").Range("A2:F100").ClearContents
Sheets("sheet1").Range("a1:b100").Copy
Sheets("sheet2").Range("a1").PasteSpecial xlPasteValues
Sheets("sheet2").Range("a2:b100").Select
Selection.Sort KEY1:=Sheets("sheet2").Range("a2"), ORDER1:=xlAscending, KEY2:=Sheets("sheet2").Range("b2") _
, ORDER2:=xlAscending, HEADER:=xlNo, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

DATA = Sheets("SHEET2").Range("A2:A100")
CROW = 2

For Each AC In DATA

If AC = "" Then Exit For

If Sheets("SHEET2").Range("A" & CROW + 1) = AC Then Sheets("SHEET2").Range("C" & CROW) = Sheets("SHEET2").Range("B" & CROW + 1)
If Sheets("SHEET2").Range("A" & CROW + 2) = AC Then Sheets("SHEET2").Range("D" & CROW) = Sheets("SHEET2").Range("B" & CROW + 2)
If Sheets("SHEET2").Range("A" & CROW + 3) = AC Then Sheets("SHEET2").Range("E" & CROW) = Sheets("SHEET2").Range("B" & CROW + 3)
If Sheets("SHEET2").Range("A" & CROW + 4) = AC Then Sheets("SHEET2").Range("F" & CROW) = Sheets("SHEET2").Range("B" & CROW + 4)

CROW = CROW + 1

Next AC



Data2 = Sheets("SHEET2").Range("A2:a100")
CROW = 2

For Each AC2 In DATA

If AC2 = "" Then Exit For

If Sheets("SHEET2").Range("A" & CROW + 1) = AC2 Then Range("A" & CROW + 1 & ":" & "F" & CROW + 1).ClearContents

CROW = CROW + 1

Next AC2


Sheets("SHEET2").Range("A2:F100").Select
Selection.Sort KEY1:=Sheets("sheet2").Range("a2"), ORDER1:=xlAscending, HEADER:=xlNo, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub
 
Upvote 0
I am really struggling to understand what you want to do...

If you have a set of account numbers on sheet 2 that appear more than once because they have more than one number associated with them then surely you already have all the numbers so why the look up...

I guess that you're simply trying to find if an account & number combo exists on sheet1 too - if not it must be a new account or old account but new number

is this correct?
 
Upvote 0
Right...

Let's say Sheet1 Cells A2 to B33 contain your data (where A = A/C and B = Tel No).

On Sheet2 you then have your list of account numbers but no numbers - some might be new some old etc...

FIRST STEP (and you can adapt the earlier code)

Sort BOTH sheet 1 and sheet 2 by account number. Then highlight Col A&B in Sheet1 and name the range DATA

SECOND STEP

In column B of Sheet2 enter this formula:

=MATCH(A2,Sheet1!$A$1:$A$33,0)

In column C of Sheet2 enter this formula:

=IF(A2=A1,C1+1,B2)

In column D of Sheet2 enter this formula:

=IF(AND(COUNTIF(Sheet1!$A$2:$A$33,A2)<=1,ISERROR(VLOOKUP(A2,DATA,2,FALSE))),"NEW A/C",IF(COUNTIF(Sheet1!$A$2:$A$33,A2)<=1,VLOOKUP(A2,DATA,2,FALSE),IF(A2=A1,INDIRECT("SHEET1!B"&C1+1),INDIRECT("SHEET1!B"&C2))))

And there you go. You should find that in Column D you will have the list of tel nos.

Let me know if this is now ok for you?
 
Upvote 0
On 2002-10-07 07:08, unknown wrote:
Hi,
I'm so sorry to trouble you again. I'll try my best to explain my problem again. Let me illustrate it with another example below ( pertaining to a telephone company ): In sheet 1, I have Various A/c nos with their corresponding telephone nos. ( Pls note that in a particular A/c, there can be more than 1 telephone no. just as in a company there can be more than 1 telephone connection ). In sheet 2, I have a list of some A/c nos. I want to find out which are the telephone nos pertaining to those A/c nos. Now if I do a VLOOKUP in the second sheet, in cases where the A/c ( for eg. 1180040600 ) no. is repeated twice ( as a result of 2 diff tel nos. ), the result will only pick the 1st telephone number ie 0226979383 in both places and leave out 0226990289. How do i get over this problem so that i can get both 0226979383 & 0226990289.

sheet 1
A/c Number Tel Number
1080019977 0226916145
1080032707 0226934762
1080043019 0226992301
1080043670 0226912959
1180039669 0226963518
1180040600 0226979383
1180040600 0226990289
1180044784 0225980569
1180047183 0226970514
1180051623 0226998399
1280049857 0226971612
1280049857 0226971611
1380037984 0225976059
1380040079 0225980070
1380047736 0226970571
1380047736 0226970572
1380049757 0225986515
1480043536 0226964726
1480047040 0226995436
1480049467 0226996871
1580018719 0226916877
1580031019 0226944097
1580031811 0226959703
1580034732 0225977890
1580039715 0226960851
1580039715 0226960850
1580040911 0225950912
1580047213 0225950911
1580047213 0226929151
1580047213 0226970181
1580047213 0226970191
7380055017 0226916890

Sheet 2
A/c Number
1080019977
1080032707
1080043119
1080043670
1180039669
1180040600
1180040600
1180044784
1180047183
1180081623
1280049857
1280049857
1380037984
1380047736
1380047736
1380049757
1380070079
1480043536
1480047040
1480059467
1580018719
1580031019
1580031811
1580039715
1580039715
1580040911
1580040911
1580047213
1580047213
1580047213
1580047213
7380055017

thanks
straus

Let A1:B33 house 2-column data you provided above.

(1.) Select A2:B33 (excluding labels).

(2.) Go to the Name Box on the Formula Bar.

(3.) Type TList and hit enter.

Let A2:A34 in Sheet2 house the A/c numbers, including the label (your second sample above).

Sheet2

In A1 enter...

=COUNT(INDEX(TList,0,1))

if you don't have any blanks in the first column of TList.

In B3 enter...

=IF(LEN(A3),VLOOKUP(A3,TList,2,0),"")

In C3 enter & copy across to, say, I3...

=IF(COUNTIF(INDEX(TList,0,1),$A3)>COUNTA($B3:B3),INDEX(OFFSET(Sheet1!$B$2,MATCH(B3,OFFSET(Sheet1!$B$2,0,0,$A$1,1),0),0,$A$1,1),MATCH($A3,OFFSET(Sheet1!$A$2,MATCH(B3,OFFSET(Sheet1!$B$2,0,0,$A$1,1),0),0,$A$1,1),0)),"")

Select B3:I3 & copy down as far as needed.

See the results (shown in part)...
aaRetrieveAllAssociatedVals Straus.xls
ABCD
132
2A/cNumber
310800199770226916145  
410800327070226934762  
51080043119#N/A  
610800436700226912959  
711800396690226963518  
8118004060002269793830226990289 
9118004060002269793830226990289 
1011800447840225980569  
1111800471830226970514  
121180081623#N/A  
13128004985702269716120226971611 
14128004985702269716120226971611 
1513800379840225976059  
16138004773602269705710226970572 
17138004773602269705710226970572 
Sheet2
 
Upvote 0
Can't really see how this differs from the earlier solution (with perhaps the exception of sorting not being required)...let's hope it doesn't lead to any further confusion on Straus' part.
 
Upvote 0
On 2002-10-09 15:07, lasw10 wrote:
Can't really see how this differs from the earlier solution (with perhaps the exception of sorting not being required)...let's hope it doesn't lead to any further confusion on Straus' part.

I guess you don't.
 
Upvote 0
fantastic man...u guys are great...that's just what i wanted...thanks a ton both of you guys lasw10 & Aladin Akyurek..hats off to you.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top