Matching data.
 

I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Matching data.
 

Hi Barry

On sheet2 in cell B1 enter
=VLOOKUP(A1,Sheet1!A:B,2,0)
Copy down as far as required

--
Regards

Roger Govier


"Barry Walker" <Barry wrote in message
...
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second
column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some
(7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with
the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a
long time.

Does anyone know a quicker way?

Regards Barry

Matching data.
 

Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi

Matching data.
 

It comes up with this

#N/A

"Roger Govier" wrote:

Hi Barry

On sheet2 in cell B1 enter
=VLOOKUP(A1,Sheet1!A:B,2,0)
Copy down as far as required

--
Regards

Roger Govier


"Barry Walker" <Barry wrote in message
...
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second
column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some
(7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with
the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a
long time.

Does anyone know a quicker way?

Regards Barry

Matching data.
 

Ok ill start again because the formula given is very close to what I need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the correct
and corresponding cells to that of column A into column B. The function needs
to be able to find matches I think. But im really not sure. Can any of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi

Matching data.
 

Hi Barry,

There might be several reasons for that. Try to have a look in the help
files on VLOOKUP and see if your formula, values and arrays meets the
critirias there.

Regards,
Bondi

Matching data.
 

The figures 8 8 4 5 8 9 should be under installments. Sorry about that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what I need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the correct
and corresponding cells to that of column A into column B. The function needs
to be able to find matches I think. But im really not sure. Can any of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi

Matching data.
 

Barry Walker wrote:
The figures 8 8 4 5 8 9 should be under installments. Sorry about that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what I need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the correct
and corresponding cells to that of column A into column B. The function needs
to be able to find matches I think. But im really not sure. Can any of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi




Hi Barry,

How about this one in cell B2 on Sheet2:
=VLOOKUP(A2,Sheet1!$A$2:$B$7,2)

And copy down.

Regards,
Bondi

Matching data.
 

Hi Bondi,

It's so close but not quite there. Okay so it is copying the installments
but its not matching them.

It just seems to copy column B exactly as it is rather like copy and paste.

I need it to be able to recognise whatever the number is in A1 worksheet 1
and the corresponding installment figure, and then find the match of A1
worsheet 2 and then pull the figures into B1 of worksheet all the way down
the column. Is it even possible or does this make sense!

Sorry to be a pain you are really helping.

Barry Thanks

"Bondi" wrote:


Barry Walker wrote:
The figures 8 8 4 5 8 9 should be under installments. Sorry about that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what I need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the correct
and corresponding cells to that of column A into column B. The function needs
to be able to find matches I think. But im really not sure. Can any of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi




Hi Barry,

How about this one in cell B2 on Sheet2:
=VLOOKUP(A2,Sheet1!$A$2:$B$7,2)

And copy down.

Regards,
Bondi

Matching data.
 

Hi Barry

It looks like your numeric values in one or other sheet are really text
values and have spaces surrounding them, hence Vlookup is not finding
them.

In cell C2 of sheet 1 enter
=TRIM(A2)
and copy down
If this removes the spaces and converts them to numbers, then copy the
whole of column C and Paste SpecialValues over the top of column A and
see if it works then.

--
Regards

Roger Govier


"Barry Walker" wrote in message
...
The figures 8 8 4 5 8 9 should be under installments. Sorry about that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what I
need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the
correct
and corresponding cells to that of column A into column B. The
function needs
to be able to find matches I think. But im really not sure. Can any
of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a
second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some
(7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet
with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking
a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the
lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi

Matching data.
 

Barry Walker wrote:
Hi Bondi,

It's so close but not quite there. Okay so it is copying the installments
but its not matching them.

It just seems to copy column B exactly as it is rather like copy and paste.

I need it to be able to recognise whatever the number is in A1 worksheet 1
and the corresponding installment figure, and then find the match of A1
worsheet 2 and then pull the figures into B1 of worksheet all the way down
the column. Is it even possible or does this make sense!

Sorry to be a pain you are really helping.

Barry Thanks

"Bondi" wrote:


Barry Walker wrote:
The figures 8 8 4 5 8 9 should be under installments. Sorry about that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what I need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the correct
and corresponding cells to that of column A into column B. The function needs
to be able to find matches I think. But im really not sure. Can any of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some (7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi




Hi Barry,

How about this one in cell B2 on Sheet2:
=VLOOKUP(A2,Sheet1!$A$2:$B$7,2)

And copy down.

Regards,
Bondi



Hi Barry,

The formula i gave you should return in Sheet2 (with the formula in
column B)
A B
1 8
4 5
5 8
6 9

Is that what it is returning to you and is it what you are looking for?

Regards,
Bondi

Matching data.
 

Roger,

Genius! The first formula you gave me does work. The reason it didn't before
was because there was text at the top of each column. Once I deleted this and
then input the formula it seemed to have worked. This will now save two days
of imputting! Thankyou so much and Bondi thankyou too.

Regards

Barry

"Roger Govier" wrote:

Hi Barry

It looks like your numeric values in one or other sheet are really text
values and have spaces surrounding them, hence Vlookup is not finding
them.

In cell C2 of sheet 1 enter
=TRIM(A2)
and copy down
If this removes the spaces and converts them to numbers, then copy the
whole of column C and Paste SpecialValues over the top of column A and
see if it works then.

--
Regards

Roger Govier


"Barry Walker" wrote in message
...
The figures 8 8 4 5 8 9 should be under installments. Sorry about that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what I
need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to the
correct
and corresponding cells to that of column A into column B. The
function needs
to be able to find matches I think. But im really not sure. Can any
of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a
second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are some
(7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first worksheet
with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is taking
a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the
lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi

Matching data.
 

Hi Barry

Glad you got it worked out in the end. Thanks for the feedback.

--
Regards

Roger Govier


"Barry Walker" wrote in message
...
Roger,

Genius! The first formula you gave me does work. The reason it didn't
before
was because there was text at the top of each column. Once I deleted
this and
then input the formula it seemed to have worked. This will now save
two days
of imputting! Thankyou so much and Bondi thankyou too.

Regards

Barry

"Roger Govier" wrote:

Hi Barry

It looks like your numeric values in one or other sheet are really
text
values and have spaces surrounding them, hence Vlookup is not finding
them.

In cell C2 of sheet 1 enter
=TRIM(A2)
and copy down
If this removes the spaces and converts them to numbers, then copy
the
whole of column C and Paste SpecialValues over the top of column A
and
see if it works then.

--
Regards

Roger Govier


"Barry Walker" wrote in
message
...
The figures 8 8 4 5 8 9 should be under installments. Sorry about
that

"Barry Walker" wrote:

Ok ill start again because the formula given is very close to what
I
need.

this is how my data looks

Worksheet 1
A1 B1
Agreements Installments
1 8
2 8
3 4
4 5
5 8
6 9

Worksheet 2

A1 B1
Agreements Installments
1
4
5
6

So i need to put the installments figure from worksheet 1 in to
the
correct
and corresponding cells to that of column A into column B. The
function needs
to be able to find matches I think. But im really not sure. Can
any
of you
help? I hope this is clearer.

Regards Barry


Barry Walker wrote:
I have two sets of data (2 worksheets)

one set contains a column with 14000 different numbers and a
second column
with corresponding values to these numbers.

the second worksheet contains a column of numbers which are
some
(7000) of
the numbers in the first worksheet.

I need to put the corresponding values from the first
worksheet
with the
column of numbers in the second worksheet.

The only way I know how is to copy one at a time which is
taking
a long time.

Does anyone know a quicker way?

Regards Barry

Hi Barry,

Maybe you could use VLOOKUP().. If you put something along the
lines of
this in a cell next to your number in Sheet2 and then copy down:

=VLOOKUP(A1,Sheet1!$A$1:$B$14000,2)

Regards,
Bondi