Make column B 0 for all but 1 value where col A has duplicates

[wonka1234]

Hi,

I have the following dataset:

Week	Hours
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_02	32
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40
2023_01_09	40

Expected output:

Week	Hours
2023_01_02	32
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_02	0
2023_01_09	40
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0
2023_01_09	0

Is there anyway to do this logic without hard coding it?

[Luke_C · Accepted solution]

Hi @wonka1234 

You could use a multi-row formula to check for this. This formula is checking to see if the week is different than the preceding row. If it is, it keeps the value, otherwise it sets to 0

[hlee36]

Used unique tool to filter what's unique or not and converted values to 0 for duplicates with formula tool and added these with union tool.