vlookup function

[Blobbies]

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!!

[Toppers]

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,VLData,2))

and this in C2

=IF(ISERROR(VLOOKUP(A2,VLData,3)),"",VLOOKUP(A2,VLData,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!!
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!!
>
>

[Blobbies]

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!!
>
>

[Sunil]

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

[mangesh_yadav]

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")


Mangesh