IF, Then Deduct Macro

[ktoon]

Hi All,

I have an extremely difficult issue that I am trying to solve by using a Macro, but I'm not sure if this is beyond Macro capabilities!

Currently, I have a macro for the below data, if Column B = 1 or 4 then replace data in Column C with Column D - then delete all of column D. This works fine.

A	B	C	D
709	1	24	42
709	4	24	42
709	20	1	42
709	9	12	42
711	20	1	42
711	9	36	42
712	1	36	42
712	4	36	42
712	20	1	42
712	9	12	42

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

However, now what I am trying to do brings in an IF statement also based on Column A, in which is a unique ID tied to a person.

So, on a per person basis (ID in column A), after the macro I stated above has run, would deduct the value from column C, where the ID = 9 in Column B FROM the values in Column C where the ID = 1 or 4 in Column B

To use Column A ID 709 as an example, this is how the output should like once complete:

A	B	C
709	1	30
709	4	30
709	20	1
709	9	12

<tbody>
</tbody>

And in the Example of person 711 above, who has no 1 or 4 ID in Column B associated, no deductions would take place for them (since the deducted ID's are not present for that person) and it would look exactly as it does above so no changes necessary.

Hopefully some genius out there can help me figure this one out I've been at it hours but it's far too confusing!

 

[hiker95]

Hi Hiker,

No, I must admit I did not try your solution - I couldn't identify in the code where I could manually change the affected ID's. I originally asked for the deduction to only occur for ID's with 1 and 4, but I actually need an alternative that performs the same function except against 1, 4 and 5 (as in the Macro I posted).

Although it appears the Macro I posted works fine (provided by Steve), do you believe that there is a benefit to your code instead of this? (not trying to be insulting/rude if I sound that way at all)

ktoon,

If you look at your raw data screenshot, and, your results screenshot, they are the same as my screenshots?????

It would only be fair to come to a final conclusion if you were to try all posted macros.