Thread: how to sort and separate outstanding data and reconciled data

Every morning i receive data of more than a 1500 entries in Excel. I am to sort these data such that if the total amount
(ie sum of the amount in column E) of all the data with the same transaction code (column C) is zero (0), then these group of
data with the same transaction code is filtered out (or moved to a different sheet) and the transaction codes whose amount
when added together and does not sum up to zero are also moved to a seperate sheet.

Example:
if there are y numbers of rows which have the same transaction code (column C) and the sum of their amount in column E is equal to zero then all
these y rows should be be copied to a new sheet, say sheet3 and listed among rows with the same characteristics. Similarly,
if the we have X number of rows which have the same transaction code
(column c) but the sum of all their amount does not add up to zero (be it negative or positive) all these x number of rows should be
copied to another sheet say sheet 4. please find attached a sample of the data I use.

A quick pivot table shows there are no transaction codes on this sample that zero out
Your are looking for a macro that will split this data into two separate sheets based on the sum of the amount per transaction code? With one sheet zeroing and the other not?

Hi raystafarian,
You understand exactly what i want. is it possible to use a formulas such that they don't exhaust all my memory as it is running on my 2G, 1.6gh, PC.

thanks in advance

Perhaps a macro like this could be of use to you?

To test run macro "separate_rows".

The macro "separate_rows" first clears sheet2, sheet3 and sheet4. It then makes a copy of your indata sheet (sheet1) called "Analyze" and sorts and trims the values in column C as there are a number of trailing spaces in those values.

The trimmed values are then sorted for unique values and using a "SUMIF" function the value of the rows are checked. If the sum is zero those rows will be copied to sheet4 and their values will be deleted from "Analyze".

After checking all the "SUMIF" values, coping and deleting if values are zero the remaining data is copied to sheet3 and sheet "Analyze" is deleted.

Your original data on Sheet1 is left undisturbed by this macro.

Alf