vlookup function
 

Hi there

I have a template that I use for various sets of data that I receive from
time to time.

The data has a unique number, to which I have to enter in the associated
details manually.

The unique number however may feature many times in the data.

The template is set up to cope with 1000 records. I use the vLookup
function to populate the rows of data, according to the unique number.

I am sure that someone would be able to advise me on how I could create a
macro or VBA to do this, instead of just using the function.

i.e.

UNIQUE RECORDS

Number Name Address
0001 Eddie 2 Smith Street
0002 Oliver 34 Jones Street

Would need to populate:

Number Name Address
0002
0002
0001
0002
0001

Many thanks for taking the time to read this and for any suggestions that
may be forthcoming!!

vlookup function
 

Try this:

In the following, VLData is your lookup table (3 Columns as per your Unique
Records).

Assuming the records to be populated have data in coums A, B and C starting
in the second row then:

Put the following in B2

IF(ISERROR(VLOOKUP(A2,VLData,2)),"",VLOOKUP(A2,VLD ata,2))

and this in C2

=IF(ISERROR(VLOOKUP(A2,VLData,3)),"",VLOOKUP(A2,VL Data,3))

Drag both columns down for your 1000 records.

When you enter a Number (in Column A) the name and address will be filled in
or left blank if there is no entry in VLData..


HTH

"Blobbies" wrote:

Hi there

I have a template that I use for various sets of data that I receive from
time to time.

The data has a unique number, to which I have to enter in the associated
details manually.

The unique number however may feature many times in the data.

The template is set up to cope with 1000 records. I use the vLookup
function to populate the rows of data, according to the unique number.

I am sure that someone would be able to advise me on how I could create a
macro or VBA to do this, instead of just using the function.

i.e.

UNIQUE RECORDS

Number Name Address
0001 Eddie 2 Smith Street
0002 Oliver 34 Jones Street

Would need to populate:

Number Name Address
0002
0002
0001
0002
0001

Many thanks for taking the time to read this and for any suggestions that
may be forthcoming!!

vlookup function
 

Sub MyLookup()
Dim ws1 as Worksheet, ws2 as Worksheet
Dim rng1 as Range, rng2 as Range
set ws1 = worksheets("Uniques")
set ws2 = worksheets("Duplicates")
set rng1 = ws1.Range(ws1.Cells(2,1),ws1.Cells(2,1).End(xldown ))
set rng2 = ws2.Range(ws2.Cells(2,1),ws2.Cells(2,1).End(xldown ))
rng2.offset(0,1).Formula = "=Vlookup(A2," & rng1.Resize(,3).Address _
(External:=True) & ",2,False)"
rng2.offset(0,2).Formula = "=Vlookup(A2," & rng1.Resize(,3).Address _
(External:=True) & ",3,False)"
rng2.Offset(0,1).Resize(,2).Formula = _
rng2.Offset(0,1).Resize(,2).Value
End Sub

--
Regards,
Tom Ogilvy

"Blobbies" wrote in message
...
Hi there

I have a template that I use for various sets of data that I receive from
time to time.

The data has a unique number, to which I have to enter in the associated
details manually.

The unique number however may feature many times in the data.

The template is set up to cope with 1000 records. I use the vLookup
function to populate the rows of data, according to the unique number.

I am sure that someone would be able to advise me on how I could create a
macro or VBA to do this, instead of just using the function.

i.e.

UNIQUE RECORDS

Number Name Address
0001 Eddie 2 Smith Street
0002 Oliver 34 Jones Street

Would need to populate:

Number Name Address
0002
0002
0001
0002
0001

Many thanks for taking the time to read this and for any suggestions that
may be forthcoming!!

vlookup function
 

thanks toppers and tom

i've been at work since 3 a.m. this morning - is now 10 p.m. at night here,
so i haven't tried your suggestions yet.

i will though and i really appreciate the time you guys put in to help!!

cheers





"Blobbies" wrote:

Hi there

I have a template that I use for various sets of data that I receive from
time to time.

The data has a unique number, to which I have to enter in the associated
details manually.

The unique number however may feature many times in the data.

The template is set up to cope with 1000 records. I use the vLookup
function to populate the rows of data, according to the unique number.

I am sure that someone would be able to advise me on how I could create a
macro or VBA to do this, instead of just using the function.

i.e.

UNIQUE RECORDS

Number Name Address
0001 Eddie 2 Smith Street
0002 Oliver 34 Jones Street

Would need to populate:

Number Name Address
0002
0002
0001
0002
0001

Many thanks for taking the time to read this and for any suggestions that
may be forthcoming!!

vlookup function
 

hi,

My data look like this ...( in reality there are 10k+ records)
A B

9999469 55-2092-1
CA968 55-2420-1
RWR81S5R23FR 91-0717-1
JANTX1N1202A CO715
TR291 TC338
CA968 91-1664-3
TR2375 9999469

in this see the value 9999469 it exists in the first column A and it exists
in the second column also B. I want to find whether a value in the second
colum exists in the second column or not...

i use the code...


Set myrng = Worksheets("Sheet1").Range("A:B")
i = WorksheetFunction.VLookup(ActiveSheet.Cells(1, 1), myrng, 2)
MsgBox i

if the value not exists i need to diaply i as "NO" otherwise "YES".....

i write the above code for only 1 value ie 9999469.....remaining thing i can
do it in a loop....thanks in advance...

With regds

Sunil.T

vlookup function
 

Hi Sunil

simply enter the following formula in the adjoing row, and copy down
Change the range to suit yourself.

=IF(ISNUMBER(MATCH(A1,$B$1:$B$7,0)),"Yes","No")


Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=36074