[SOLVED] Matching data.

[Barry Walker]

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

[Roger Govier]

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

[Bondi]

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

[Barry Walker]

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

[Barry Walker]

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

[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

[Barry Walker]

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

[Bondi]

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

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

--
Regards

Roger Govier


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

[Barry Walker]

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

[Bondi]

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

[Barry Walker]

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 Special>Values over the top of column A and
> see if it works then.
>
> --
> Regards
>
> Roger Govier
>
>
> "Barry Walker" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >> >
> >> >
>
>
>

[Roger Govier]

Hi Barry

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

--
Regards

Roger Govier


"Barry Walker" <[email protected]> wrote in message
news:[email protected]...
> 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 Special>Values over the top of column A
>> and
>> see if it works then.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Barry Walker" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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
>> >> >
>> >> >
>>
>>
>>