Matching data.

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
 
R

Roger Govier

Hi Barry

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

Bondi

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
 
G

Guest

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
 
B

Bondi

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
 
B

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
 
G

Guest

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
 
R

Roger Govier

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.
 
B

Bondi

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Consistent Sheet column header rows 2
Excel Columns moving up and down on their own in a worksheet. What Goes? 2
Excel lookup across entire workbook 1
Compile Error: Method or data member not found error 0
Matching records based on a common field 1
Paste a value into "Find What" 1
Link variable data from one worksheet to another worksheet? 3
VBA - retrieve data on last create named range from previous inserted named range 0

Top