Hi all,
I have a problem but it may be too difficult to solve.
I want to create a macro that will be able to sift through the data I have and highlight values that sum to 0. The data is already sorted into reference order - The first 4 digits are used to identify a store so for example transactions with reference 2001XXX relates to one store and ones with a reference 3261XXX will relate to transactions for a different store. Therefore I want a macro that could 'look' at each group individually.
Could someone please help to create a macro that could go through each group (for example the group of references beginning with 2001) and find the combination of values that comes to 0 and highlight them?
Sometimes there is a discrepancy and the debits and credits with the same reference will not sum to 0 (see reference 2001002 in the example), and therefore I assume the macro will have to sort through the values for each store rather than look at the references? There may also be times when, as with reference 2002302, there is a value in the group that could also be a match (in this case with reference 2002300) - is there any way to get the macro to check the reference matches first and then highlight, and if there is no matching reference then in highlights the first matches it comes across?
Please let me know if you can help. I have included an 'Ideally' tab to show what I would like the end result to look like.
Thank you
Bookmarks