theraindew
New Member
- Joined
- Dec 7, 2018
- Messages
- 11
- Office Version
- 365
- 2016
- Platform
- Windows
I have a large data set that i have to work on every day and find out items that match to a zero.
Below is a sample data. Column A has the reference numbers. I have removed the suffixes and kept only the numbers in column B. Column C has amounts. Now, some of the amounts in column C will total to zero for the same reference numbers in column B. I want to identify these matching items and mark them in column D. I have given Yes and No as sample, but this could be anything. Is there a formula or even VBA that can achieve this? I tried sum/if/sumproduct but couldn't get it to work!
Appreciate any help on this.
Below is a sample data. Column A has the reference numbers. I have removed the suffixes and kept only the numbers in column B. Column C has amounts. Now, some of the amounts in column C will total to zero for the same reference numbers in column B. I want to identify these matching items and mark them in column D. I have given Yes and No as sample, but this could be anything. Is there a formula or even VBA that can achieve this? I tried sum/if/sumproduct but couldn't get it to work!
Appreciate any help on this.
Reference | Ref | Amount | Match |
44004345450A | 44004345450 | -54,999 | Yes |
44004345450B | 44004345450 | -99,999 | No |
44004345450-A | 44004345450 | -79,999 | No |
44004345450C | 44004345450 | -20,799 | No |
44004345450-B | 44004345450 | -216,750 | No |
44004345450D | 44004345450 | 30,500 | Yes |
44004345450E | 44004345450 | 24,499 | Yes |
44004541765A | 44004541765 | -11,041 | Yes |
44004541765-A | 44004541765 | -35,998 | No |
44004541765-B | 44004541765 | -36,449 | No |
44004541765B | 44004541765 | 11,041 | Yes |
44004740328A | 44004740328 | -38,998 | Yes |
44004740328-A | 44004740328 | -19,499 | No |
44004740328B | 44004740328 | 38,998 | Yes |