Right ok

Say I have a worksheet with A1 - Contract No's, B1 - Serial No, C1 -
Rental, D1 - Rebate.

A Contract # B Serial C Rental D Rebate

1 40190158 1212121212 1100 10
2 40190583 4545454541 1000 10
3 40190583 4545454542 3000 30
4 40190583 4545454543 1000 10
5 40156124 1574516544 10206 102
6 75621564 6542156464 40240 402
7 75621564 4541145444 60240 602
8 45444451 4645125442 2053 21

I have a sheet with over 1000 orders on. At the moment where there are
duplicate contract no's the same rebate is entered into all rebate
fields for that same contract no (in other words there can be other
'machines' with the same contract no).

So the rebate is being shown per machine where the value present is
actually per contract.

If there are no duplicates, then that simply means that there is only 1
machine on the contract. But where there are duplicate contract no's
(individual serial no's), the rebate value needs to be divided up by
ratio using the rental value.

How can I easily do this? Preferably in 1 step. (so identify the
duplicates, take the rebate for them all (which is repeated on each
record for each machine on the dup contract, so its only nes to take it
from 1 rebate field of a machine), then divide this value up using the
rental value as the ratio (each one has a different rental value) and
then dispaly the rebate for each.

Please help!


--
daleuk
------------------------------------------------------------------------
daleuk's Profile: http://www.excelforum.com/member.php...o&userid=26454
View this thread: http://www.excelforum.com/showthread...hreadid=397198

I originally tried this in another column

=ROUND(D2/COUNTIF(A:A,A2),2)

but I notice that 401905783 has different rebate amounts.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"daleuk" wrote in
message ...

Right ok

Say I have a worksheet with A1 - Contract No's, B1 - Serial No, C1 -
Rental, D1 - Rebate.

A Contract # B Serial C Rental D Rebate

1 40190158 1212121212 1100 10
2 40190583 4545454541 1000 10
3 40190583 4545454542 3000 30
4 40190583 4545454543 1000 10
5 40156124 1574516544 10206 102
6 75621564 6542156464 40240 402
7 75621564 4541145444 60240 602
8 45444451 4645125442 2053 21

I have a sheet with over 1000 orders on. At the moment where there are
duplicate contract no's the same rebate is entered into all rebate
fields for that same contract no (in other words there can be other
'machines' with the same contract no).

So the rebate is being shown per machine where the value present is
actually per contract.

If there are no duplicates, then that simply means that there is only 1
machine on the contract. But where there are duplicate contract no's
(individual serial no's), the rebate value needs to be divided up by
ratio using the rental value.

How can I easily do this? Preferably in 1 step. (so identify the
duplicates, take the rebate for them all (which is repeated on each
record for each machine on the dup contract, so its only nes to take it
from 1 rebate field of a machine), then divide this value up using the
rental value as the ratio (each one has a different rental value) and
then dispaly the rebate for each.

Please help!


--
daleuk
------------------------------------------------------------------------
daleuk's Profile:
http://www.excelforum.com/member.php...o&userid=26454
View this thread: http://www.excelforum.com/showthread...hreadid=397198