|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
--
Regards
Roger Govier
"Barry Walker" <Barry Walker[ at ]discussions.microsoft.com> wrote in message
news:54C35507-0167-4CFE-953C-6113F79EF960[ at ]microsoft.com...
[Quoted Text] >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 wrote:
[Quoted Text] > 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
|
|
It comes up with this
#N/A
"Roger Govier" wrote:
[Quoted Text] > 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 Walker[ at ]discussions.microsoft.com> wrote in message
> news:54C35507-0167-4CFE-953C-6113F79EF960[ at ]microsoft.com...
> >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
>
>
>
|
|
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
[Quoted Text] >
> 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,
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
|
|
The figures 8 8 4 5 8 9 should be under installments. Sorry about that
"Barry Walker" wrote:
[Quoted Text] > 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 wrote:
[Quoted Text] > 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 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:
[Quoted Text] >
> 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
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" <BarryWalker[ at ]discussions.microsoft.com> wrote in message
news:B61A3C9C-6524-4894-983F-1BA8BB44A52A[ at ]microsoft.com...
[Quoted Text] > 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 wrote:
[Quoted Text] > 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
|
|
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:
[Quoted Text] > 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" <BarryWalker[ at ]discussions.microsoft.com> wrote in message
> news:B61A3C9C-6524-4894-983F-1BA8BB44A52A[ at ]microsoft.com...
> > 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
Glad you got it worked out in the end. Thanks for the feedback.
--
Regards
Roger Govier
"Barry Walker" <BarryWalker[ at ]discussions.microsoft.com> wrote in message
news:BAB66C58-4D30-4139-B032-C917C14B91A3[ at ]microsoft.com...
[Quoted Text] > 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" <BarryWalker[ at ]discussions.microsoft.com> wrote in
>> message
>> news:B61A3C9C-6524-4894-983F-1BA8BB44A52A[ at ]microsoft.com...
>> > 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
>> >> >
>> >> >
>>
>>
>>
|
|
|