L
Len
Hi,
I have at a table below with duplicates of numeric text in column E,
there is no problem using excel formula COUNTIF to remove duplicates
However, if I were to remove each row of duplicates with conditions
that the corresponding value of duplicate in column F is Positive and
also the next value of the same duplicate in the same column F is
negative and both values of that duplicate in column F sum up equal to
zero
In this case, duplicate of 123456 found at E1, E2 and E4 but it has
positive value of 70 at F1 and F2 and also negative value of 70 at F4
Row 1 and 4 will be deleted as the duplicate of 123456 has both value
at F1 ( ie 1st positive value ) & F4 sum up equal to zero
The removal of duplicates will go on until there is no duplicate
found or there is no zero value found after sum up of two duplicate's
value , ie only unique numeric text remain in the table
E.g.
Column E F
Doc No Amt
1 123456 70
2 123456 70
3 654321 -1560
4 123456 -70
5 654321 1560
6 654321 -1560
Result
Doc No Amt
2 123456 70
6 654321 -1560
How to use excel formula or excel vba codes to achieve the result by
removing duplicates with the above conditions
I would be much appreciated if someone could offer a solution to the
above scenario and thanks in advance
Regards
Len
I have at a table below with duplicates of numeric text in column E,
there is no problem using excel formula COUNTIF to remove duplicates
However, if I were to remove each row of duplicates with conditions
that the corresponding value of duplicate in column F is Positive and
also the next value of the same duplicate in the same column F is
negative and both values of that duplicate in column F sum up equal to
zero
In this case, duplicate of 123456 found at E1, E2 and E4 but it has
positive value of 70 at F1 and F2 and also negative value of 70 at F4
Row 1 and 4 will be deleted as the duplicate of 123456 has both value
at F1 ( ie 1st positive value ) & F4 sum up equal to zero
The removal of duplicates will go on until there is no duplicate
found or there is no zero value found after sum up of two duplicate's
value , ie only unique numeric text remain in the table
E.g.
Column E F
Doc No Amt
1 123456 70
2 123456 70
3 654321 -1560
4 123456 -70
5 654321 1560
6 654321 -1560
Result
Doc No Amt
2 123456 70
6 654321 -1560
How to use excel formula or excel vba codes to achieve the result by
removing duplicates with the above conditions
I would be much appreciated if someone could offer a solution to the
above scenario and thanks in advance
Regards
Len