Need a life savor

[SueC]

Hi ... I have a file with 10 columns. One column (H) have numeric values and one with a reference number(J). I need to identify all the rows that have a common debit(plus) and credit(minus) with the same reference number. U have looked all over the site and can't seem to find what I am looking for. I do this manually every month, but the file just continues to grow with each period, so it is becoming cumbersome.

Hope there is a savior out there for me!

Thanks

 

[just_jon]

When you say common number, do you mean +100 and -100 is a match or +100 and any negative is a match to be flagged?

 

[Mark W.]

Create 2 additional columns (e.g., K & L) with...

K2: =J2&","&ABS(H2)
L2: =COUNTIF($range,K2)

Where $range is an absolute reference from K2 to last row in K. Fill these formulas down to the last row. Apply and AutoFilter on column L for cell values >1.

 

[SueC]

This first example is what I was referring to +100 and -100. Thanks

 

[texasalynn]

Create 2 additional columns (e.g., K & L) with...

K2: =(J2&","&H2)
L2: =COUNTIF(range,K2)

Where range is K2 to last row in K. Fill these formulas down to the last row. Apply and AutoFilter on column L for cell values >1.

I would change this suggestion slightly
K2: =(J2&","&abs(H2))

HTH
texasalynn

 

[Mark W.]

deleted

 

[Mark W.]

Create 2 additional columns (e.g., K & L) with...

K2: =(J2&","&H2)
L2: =COUNTIF(range,K2)

Where range is K2 to last row in K. Fill these formulas down to the last row. Apply and AutoFilter on column L for cell values >1.

I would change this suggestion slightly
K2: =(J2&","&abs(H2))

HTH
texasalynn

Already, did.

 

[fairwinds]

Hi,

=MATCH(-H2&"@"&J2,$H$2:$H$13&"@"&$J$2:$J$13,0)+1
needs to be confirmed with Ctrl + shift + enter.

Book1
	F	G	H	I	J
1	Matching row		Debit Credit		Ref no
2	5		5		1
3	11		10		2
4	13		16		3
5	2		-5		1
6	#N/A		11		2
7	#N/A		-10		3
8	#N/A		3		1
9	#N/A		27		2
10	#N/A		-3		3
11	3		-10		2
12	#N/A		12		1
13	4		-16		3
Sheet2

 

[texasalynn]

ah-ha that's what happens with post-edits. Good job Mark W.

 

[just_jon]

Mark -- if you do ABS in the concatenated column, you'll find a match between +100 and +100 wouldn't you?

Was thinking something like -

=--ISNUMBER(MATCH(B2&":"&A2,B$2:B$10&":"&(A$2:A$10*-1),0))

BUT, use *-1 in the concatenated column, and substitute to -

=--ISNUMBER(MATCH(B2&":"&A2,X$2:X$10),0))

Where X2 is

==B2&":"&A2*-1