Find corresponding negative values

[Dezzi]

Hi,

I have a worksheet with charges; each negative charge should have a corresponding positive offset. For now both sets of numbers occupy the same column.

The account has a balance, ie, not all the negative entries have a corresponding offset (pr vice-versa). I have tried separating the negative from the positive and then use the "lookup" formula but if there are three positive entries of the same value and only one negative offset, the formula doesn't account for that. Unfortunately the corresponding numbers don't share the same codes such as purchase order etc.

Basically I want a code that will alert me of negative entries that are without the corresponding offset and will also factor in the duplications.

Any help will be greatly appreciated!

[rylo]

Hi

Can you submit an example file showing your data structure and some representative data.

rylo

[nimrod]

For this example let's assume your dollar amount , both debits and credits , are in column A:A ...starting in A1 .... how about using these formulas in nearby columns.


=COUNTIF(A:A,A1) + COUNTIF(A:A,-A1)

=SUMIF(A:A,A1,A:A) + SUMIF(A:A,-A1,A:A)

These formula will tell you how rows have these specific amount and what the balance , of the specific amount , is. Now just sort you columns to group information.

[Dezzi]

Hi

Can you submit an example file showing your data structure and some representative data.

rylo

Thanks for the replies so far guys. I have attached a very small section of the sheet, you can see where the total number of negative and positive values don't add up

[Dezzi]

For this example let's assume your dollar amount , both debits and credits , are in column A:A ...starting in A1 .... how about using these formulas in nearby columns.


=COUNTIF(A:A,A1) + COUNTIF(A:A,-A1)

=SUMIF(A:A,A1,A:A) + SUMIF(A:A,-A1,A:A)

These formula will tell you how rows have these specific amount and what the balance , of the specific amount , is. Now just sort you columns to group information.

Can you give me another example of what you mean using the data I've attached for clarity please?

Thanks

[Dezzi]

Can anyone else try to help, pleaaaaaaaaaaaaaaaaaaaaasssssse?

[Dezzi]

For this example let's assume your dollar amount , both debits and credits , are in column A:A ...starting in A1 .... how about using these formulas in nearby columns.


=COUNTIF(A:A,A1) + COUNTIF(A:A,-A1)

=SUMIF(A:A,A1,A:A) + SUMIF(A:A,-A1,A:A)

These formula will tell you how rows have these specific amount and what the balance , of the specific amount , is. Now just sort you columns to group information.

It worked!!!!!!!!!!!!
Thank you so much, May God bless you and preserve you!