|
|
Hi All
I have a little puzzle.
I have a workbook with two worksheets.
In Sheet 1 , column D contains a number to 2 decimal places , and column
E contains a reference number.
In Sheet 2 , column A contains equally a number to 2 decimal places ,
and column B contains a Reference Number.
I need a formula or a macro which will check the Reference Numbers in
sheet 1 with sheet 2 and , where there is a a match , bring the number
from column A to overwrite the number in column D. Effectively ,
updating the numbers to 2 decimal places in Sheet 1 with the ones from
Sheet 2. The Reference Numbers would be unchanged.
It should look for a match anywhere between the 2 columns , and continue
down until it has got to the final row , each time bringing the number
from ws2 to ws1
Can anyone help with this?
Grateful for any help.
Best Wishes
|
|
Put this formula in a helper column (e.g. in F1) of Sheet1:
=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A:A,MATCH(E1,Sheet2!B:B,0)))
Then you can copy this formula down to cover all the data you have in
Sheet1.
When you are happy that it does what you want, you can fix the values in
column F and then copy/paste them over the values in column D, and then
delete column F.
Hope this helps.
Pete
"Colin Hayes" <Colin[ at ]chayes.demon.co.uk> wrote in message
news:W6$u$SAM0pWJFw+u[ at ]chayes.demon.co.uk...
[Quoted Text] >
>
> Hi All
>
> I have a little puzzle.
>
> I have a workbook with two worksheets.
>
> In Sheet 1 , column D contains a number to 2 decimal places , and column E
> contains a reference number.
>
> In Sheet 2 , column A contains equally a number to 2 decimal places , and
> column B contains a Reference Number.
>
> I need a formula or a macro which will check the Reference Numbers in
> sheet 1 with sheet 2 and , where there is a a match , bring the number
> from column A to overwrite the number in column D. Effectively , updating
> the numbers to 2 decimal places in Sheet 1 with the ones from Sheet 2. The
> Reference Numbers would be unchanged.
>
> It should look for a match anywhere between the 2 columns , and continue
> down until it has got to the final row , each time bringing the number
> from ws2 to ws1
>
> Can anyone help with this?
>
> Grateful for any help.
>
>
>
> Best Wishes
|
|
In article <eetAWjuaJHA.4424[ at ]TK2MSFTNGP05.phx.gbl>, Pete_UK
<pashurst[ at ]auditel.net> writes
[Quoted Text] >Put this formula in a helper column (e.g. in F1) of Sheet1:
>
>=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A:A,MATCH(E1,Sheet2!B:
>B,0)))
>
>Then you can copy this formula down to cover all the data you have in
>Sheet1.
>
>When you are happy that it does what you want, you can fix the values in
>column F and then copy/paste them over the values in column D, and then
>delete column F.
>
>Hope this helps.
>
>Pete
Hi Pete
OK thanks for getting back.
I tried it out , but found no joy I'm afraid. It's very close though , I
feel.
Curiously , when I drag down the formula in F , it opens an entirely
separate popup requesting update values. I think it's not recognising
Sheet2 as a worksheet and is looking for the values in an imported file.
It just needs to bring over the values in Sheet2 to Sheet1 where the
Reference Numbers match , and replace the values in the appropriate
cells. I feel a little insecure in the clarity of my description , so
I've put some sample figures below.
So Sheet1 , before change:
D E
2.65 75945bx185
5.75 12116bx86
6.75 12183bx86
3.75 51087bx126
4.75 12835bx88
7.75 12117bx86
6.75 12185bx86
7.75 2302bx29
3.75 69918bxV12
7.75 12115bx86
Sheet2
A B
6.15 75945bx185
2.36 12116bx86
9.75 12183bx86
3.75 51087bx126
4.75 12835bx88
8.25 12117bx86
6.45 12185bx86
7.75 2302bx29
4.75 23278bx30
3.75 14395bx97
3.75 43751bx177
Sheet1 , after update becomes :
D E
6.15 75945bx185
2.36 12116bx86
9.75 12183bx86
3.75 51087bx126
4.75 12835bx88
8.25 12117bx86
6.45 12185bx86
7.75 2302bx29
3.75 69918bxV12
7.75 12115bx86
I did try tweaking , but I'm not expert enough to do this really , so if
you can see how to get it going , I'd be grateful for your advice.
Best Wishes
Colin
>
>
>"Colin Hayes" <Colin[ at ]chayes.demon.co.uk> wrote in message
>news:W6$u$SAM0pWJFw+u[ at ]chayes.demon.co.uk...
>>
>>
>> Hi All
>>
>> I have a little puzzle.
>>
>> I have a workbook with two worksheets.
>>
>> In Sheet 1 , column D contains a number to 2 decimal places , and column E
>> contains a reference number.
>>
>> In Sheet 2 , column A contains equally a number to 2 decimal places , and
>> column B contains a Reference Number.
>>
>> I need a formula or a macro which will check the Reference Numbers in
>> sheet 1 with sheet 2 and , where there is a a match , bring the number
>> from column A to overwrite the number in column D. Effectively , updating
>> the numbers to 2 decimal places in Sheet 1 with the ones from Sheet 2. The
>> Reference Numbers would be unchanged.
>>
>> It should look for a match anywhere between the 2 columns , and continue
>> down until it has got to the final row , each time bringing the number
>> from ws2 to ws1
>>
>> Can anyone help with this?
>>
>> Grateful for any help.
>>
>>
>>
>> Best Wishes
>
>
|
|
Hi Colin,
I set this up with the test data you provided, put my formula in F1 of
Sheet1 and copied it down, and got exactly the results you said you
were hoping for.
You must have typed the formula with errors in it, particularly as you
say that Excel is not recognising the sheet names - make sure you use
the names that you have in your workbook, which are not necessarily
Sheet1 and Sheet2. If you have spaces in these names, you will have to
put apostrophes around the complete name, like 'Sheet 2'.
Hope this helps.
Pete
On Dec 31, 3:01 am, Colin Hayes <Co...[ at ]chayes.demon.co.uk> wrote:
[Quoted Text] > In article <eetAWjuaJHA.4...[ at ]TK2MSFTNGP05.phx.gbl>, Pete_UK > <pashu...[ at ]auditel.net> writes > > >Put this formula in a helper column (e.g. in F1) of Sheet1: > > >=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A:A,MATCH(E1,Sheet2!B: > >B,0))) > > >Then you can copy this formula down to cover all the data you have in > >Sheet1. > > >When you are happy that it does what you want, you can fix the values in > >column F and then copy/paste them over the values in column D, and then > >delete column F. > > >Hope this helps. > > >Pete > > Hi Pete > > OK thanks for getting back. > > I tried it out , but found no joy I'm afraid. It's very close though , I > feel. > > Curiously , when I drag down the formula in F , it opens an entirely > separate popup requesting update values. I think it's not recognising > Sheet2 as a worksheet and is looking for the values in an imported file. > > It just needs to bring over the values in Sheet2 to Sheet1 where the > Reference Numbers match , and replace the values in the appropriate > cells. I feel a little insecure in the clarity of my description , so > I've put some sample figures below. > > So Sheet1 , before change: > > D E > > 2.65 75945bx185 > 5.75 12116bx86 > 6.75 12183bx86 > 3.75 51087bx126 > 4.75 12835bx88 > 7.75 12117bx86 > 6.75 12185bx86 > 7.75 2302bx29 > 3.75 69918bxV12 > 7.75 12115bx86 > > Sheet2 > > A B > > 6.15 75945bx185 > 2.36 12116bx86 > 9.75 12183bx86 > 3.75 51087bx126 > 4.75 12835bx88 > 8.25 12117bx86 > 6.45 12185bx86 > 7.75 2302bx29 > 4.75 23278bx30 > 3.75 14395bx97 > 3.75 43751bx177 > > Sheet1 , after update becomes : > > D E > > 6.15 75945bx185 > 2.36 12116bx86 > 9.75 12183bx86 > 3.75 51087bx126 > 4.75 12835bx88 > 8.25 12117bx86 > 6.45 12185bx86 > 7.75 2302bx29 > 3.75 69918bxV12 > 7.75 12115bx86 > > I did try tweaking , but I'm not expert enough to do this really , so if > you can see how to get it going , I'd be grateful for your advice. > > Best Wishes > > Colin > > > > > > >"Colin Hayes" <Co...[ at ]chayes.demon.co.uk> wrote in message > >news:W6$u$SAM0pWJFw+u[ at ]chayes.demon.co.uk... > > >> Hi All > > >> I have a little puzzle. > > >> I have a workbook with two worksheets. > > >> In Sheet 1 , column D contains a number to 2 decimal places , and column E > >> contains a reference number. > > >> In Sheet 2 , column A contains equally a number to 2 decimal places , and > >> column B contains a Reference Number. > > >> I need a formula or a macro which will check the Reference Numbers in > >> sheet 1 with sheet 2 and , where there is a a match , bring the number > >> from column A to overwrite the number in column D. Effectively , updating > >> the numbers to 2 decimal places in Sheet 1 with the ones from Sheet 2. The > >> Reference Numbers would be unchanged. > > >> It should look for a match anywhere between the 2 columns , and continue > >> down until it has got to the final row , each time bringing the number > >> from ws2 to ws1 > > >> Can anyone help with this? > > >> Grateful for any help. > > >> Best Wishes- Hide quoted text - > > - Show quoted text -
|
|
Hi Pete
OK I had another go , and you're quite right. It's working fine now - I
must have input an error to cause it to go wrong.
Thanks again - I'm grateful for your kind help and expertise.
BTW - as an extension to this , could the formula be modified to add 1
to the number being imported to Sheet1?
Best Wishes
Colin
In article
<4566ca0d-474c-4b12-843e-a3c85acfd012[ at ]w1g2000prm.googlegroups.com>,
Pete_UK <pashurst[ at ]auditel.net> writes
[Quoted Text] >Hi Colin, > >I set this up with the test data you provided, put my formula in F1 of >Sheet1 and copied it down, and got exactly the results you said you >were hoping for. > >You must have typed the formula with errors in it, particularly as you >say that Excel is not recognising the sheet names - make sure you use >the names that you have in your workbook, which are not necessarily >Sheet1 and Sheet2. If you have spaces in these names, you will have to >put apostrophes around the complete name, like 'Sheet 2'. > >Hope this helps. > >Pete > >On Dec 31, 3:01 am, Colin Hayes <Co...[ at ]chayes.demon.co.uk> wrote: >> In article <eetAWjuaJHA.4...[ at ]TK2MSFTNGP05.phx.gbl>, Pete_UK >> <pashu...[ at ]auditel.net> writes >> >> >Put this formula in a helper column (e.g. in F1) of Sheet1: >> >> >=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A:A,MATCH(E1,Sheet2! >B: >> >B,0))) >> >> >Then you can copy this formula down to cover all the data you have in >> >Sheet1. >> >> >When you are happy that it does what you want, you can fix the values in >> >column F and then copy/paste them over the values in column D, and then >> >delete column F. >> >> >Hope this helps. >> >> >Pete >> >> Hi Pete >> >> OK thanks for getting back. >> >> I tried it out , but found no joy I'm afraid. It's very close though , I >> feel. >> >> Curiously , when I drag down the formula in F , it opens an entirely >> separate popup requesting update values. I think it's not recognising >> Sheet2 as a worksheet and is looking for the values in an imported file. >> >> It just needs to bring over the values in Sheet2 to Sheet1 where the >> Reference Numbers match , and replace the values in the appropriate >> cells. I feel a little insecure in the clarity of my description , so >> I've put some sample figures below. >> >> So Sheet1 , before change: >> >> D E >> >> 2.65 75945bx185 >> 5.75 12116bx86 >> 6.75 12183bx86 >> 3.75 51087bx126 >> 4.75 12835bx88 >> 7.75 12117bx86 >> 6.75 12185bx86 >> 7.75 2302bx29 >> 3.75 69918bxV12 >> 7.75 12115bx86 >> >> Sheet2 >> >> A B >> >> 6.15 75945bx185 >> 2.36 12116bx86 >> 9.75 12183bx86 >> 3.75 51087bx126 >> 4.75 12835bx88 >> 8.25 12117bx86 >> 6.45 12185bx86 >> 7.75 2302bx29 >> 4.75 23278bx30 >> 3.75 14395bx97 >> 3.75 43751bx177 >> >> Sheet1 , after update becomes : >> >> D E >> >> 6.15 75945bx185 >> 2.36 12116bx86 >> 9.75 12183bx86 >> 3.75 51087bx126 >> 4.75 12835bx88 >> 8.25 12117bx86 >> 6.45 12185bx86 >> 7.75 2302bx29 >> 3.75 69918bxV12 >> 7.75 12115bx86 >> >> I did try tweaking , but I'm not expert enough to do this really , so if >> you can see how to get it going , I'd be grateful for your advice. >> >> Best Wishes >> >> Colin >> >> >> >> >> >> >"Colin Hayes" <Co...[ at ]chayes.demon.co.uk> wrote in message >> >news:W6$u$SAM0pWJFw+u[ at ]chayes.demon.co.uk... >> >> >> Hi All >> >> >> I have a little puzzle. >> >> >> I have a workbook with two worksheets. >> >> >> In Sheet 1 , column D contains a number to 2 decimal places , and column E >> >> contains a reference number. >> >> >> In Sheet 2 , column A contains equally a number to 2 decimal places , and >> >> column B contains a Reference Number. >> >> >> I need a formula or a macro which will check the Reference Numbers in >> >> sheet 1 with sheet 2 and , where there is a a match , bring the number >> >> from column A to overwrite the number in column D. Effectively , updating >> >> the numbers to 2 decimal places in Sheet 1 with the ones from Sheet 2. The >> >> Reference Numbers would be unchanged. >> >> >> It should look for a match anywhere between the 2 columns , and continue >> >> down until it has got to the final row , each time bringing the number >> >> from ws2 to ws1 >> >> >> Can anyone help with this? >> >> >> Grateful for any help. >> >> >> Best Wishes- Hide quoted text - >> >> - Show quoted text - >
|
|
Glad to hear that, Colin.
This amendment will add 1 to the value being brought from Sheet2, but
will leave the value from Sheet1 unchanged:
=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A:A,MATCH(E1,Sheet2!
B:B,0))+1)
Happy New Year to you.
Pete
On Dec 31 2008, 8:07 pm, Colin Hayes <Co...[ at ]chayes.demon.co.uk> wrote:
[Quoted Text] > Hi Pete > > OK I had another go , and you're quite right. It's working fine now - I > must have input an error to cause it to go wrong. > > Thanks again - I'm grateful for your kind help and expertise. > > BTW - as an extension to this , could the formula be modified to add 1 > to the number being imported to Sheet1? > > Best Wishes > > Colin > > In article > <4566ca0d-474c-4b12-843e-a3c85acfd...[ at ]w1g2000prm.googlegroups.com>, > Pete_UK <pashu...[ at ]auditel.net> writes > > > > >Hi Colin, > > >I set this up with the test data you provided, put my formula in F1 of > >Sheet1 and copied it down, and got exactly the results you said you > >were hoping for. > > >You must have typed the formula with errors in it, particularly as you > >say that Excel is not recognising the sheet names - make sure you use > >the names that you have in your workbook, which are not necessarily > >Sheet1 and Sheet2. If you have spaces in these names, you will have to > >put apostrophes around the complete name, like 'Sheet 2'. > > >Hope this helps. > > >Pete > > >On Dec 31, 3:01 am, Colin Hayes <Co...[ at ]chayes.demon.co.uk> wrote: > >> In article <eetAWjuaJHA.4...[ at ]TK2MSFTNGP05.phx.gbl>, Pete_UK > >> <pashu...[ at ]auditel.net> writes > > >> >Put this formula in a helper column (e.g. in F1) of Sheet1: > > >> >=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A:A,MATCH(E1,Sheet2! > >B: > >> >B,0))) > > >> >Then you can copy this formula down to cover all the data you have in > >> >Sheet1. > > >> >When you are happy that it does what you want, you can fix the values in > >> >column F and then copy/paste them over the values in column D, and then > >> >delete column F. > > >> >Hope this helps. > > >> >Pete > > >> Hi Pete > > >> OK thanks for getting back. > > >> I tried it out , but found no joy I'm afraid. It's very close though , I > >> feel. > > >> Curiously , when I drag down the formula in F , it opens an entirely > >> separate popup requesting update values. I think it's not recognising > >> Sheet2 as a worksheet and is looking for the values in an imported file. > > >> It just needs to bring over the values in Sheet2 to Sheet1 where the > >> Reference Numbers match , and replace the values in the appropriate > >> cells. I feel a little insecure in the clarity of my description , so > >> I've put some sample figures below. > > >> So Sheet1 , before change: > > >> D E > > >> 2.65 75945bx185 > >> 5.75 12116bx86 > >> 6.75 12183bx86 > >> 3.75 51087bx126 > >> 4.75 12835bx88 > >> 7.75 12117bx86 > >> 6.75 12185bx86 > >> 7.75 2302bx29 > >> 3.75 69918bxV12 > >> 7.75 12115bx86 > > >> Sheet2 > > >> A B > > >> 6.15 75945bx185 > >> 2.36 12116bx86 > >> 9.75 12183bx86 > >> 3.75 51087bx126 > >> 4.75 12835bx88 > >> 8.25 12117bx86 > >> 6.45 12185bx86 > >> 7.75 2302bx29 > >> 4.75 23278bx30 > >> 3.75 14395bx97 > >> 3.75 43751bx177 > > >> Sheet1 , after update becomes : > > >> D E > > >> 6.15 75945bx185 > >> 2.36 12116bx86 > >> 9.75 12183bx86 > >> 3.75 51087bx126 > >> 4.75 12835bx88 > >> 8.25 12117bx86 > >> 6.45 12185bx86 > >> 7.75 2302bx29 > >> 3.75 69918bxV12 > >> 7.75 12115bx86 > > >> I did try tweaking , but I'm not expert enough to do this really , so if > >> you can see how to get it going , I'd be grateful for your advice. > > >> Best Wishes > > >> Colin > > >> >"Colin Hayes" <Co...[ at ]chayes.demon.co.uk> wrote in message > >> >news:W6$u$SAM0pWJFw+u[ at ]chayes.demon.co.uk... > > >> >> Hi All > > >> >> I have a little puzzle. > > >> >> I have a workbook with two worksheets. > > >> >> In Sheet 1 , column D contains a number to 2 decimal places , and column E > >> >> contains a reference number. > > >> >> In Sheet 2 , column A contains equally a number to 2 decimal places , and > >> >> column B contains a Reference Number. > > >> >> I need a formula or a macro which will check the Reference Numbers in > >> >> sheet 1 with sheet 2 and , where there is a a match , bring the number > >> >> from column A to overwrite the number in column D. Effectively , updating > >> >> the numbers to 2 decimal places in Sheet 1 with the ones from Sheet 2. The > >> >> Reference Numbers would be unchanged. > > >> >> It should look for a match anywhere between the 2 columns , and continue > >> >> down until it has got to the final row , each time bringing the number > >> >> from ws2 to ws1 > > >> >> Can anyone help with this? > > >> >> Grateful for any help. > > >> >> Best Wishes- Hide quoted text - > > >> - Show quoted text -- Hide quoted text - > > - Show quoted text -
|
|
|