Matching data.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

On sheet2 in cell B1 enter
=VLOOKUP(A1,Sheet1!A:B,2,0)
Copy down as far as required
 
Barry said:
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
Click to expand...

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
 
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
 
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
 
Barry said:
The figures 8 8 4 5 8 9 should be under installments. Sorry about that
Click to expand...


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 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
 
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 Special>Values over the top of column A and
see if it works then.
 
Barry said:
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
Click to expand...

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
 
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
 
Back
Top