Need a life savor

SueC

New Member
Joined
Jan 12, 2005
Messages
10
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

New Member
Joined
Jan 12, 2005
Messages
10
This first example is what I was referring to +100 and -100. Thanks
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

Mark W. said:
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

texasalynn said:
Mark W. said:
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

=MATCH(-H2&"@"&J2,$H$2:$H$13&"@"&$J$2:$J$13,0)+1
needs to be confirmed with Ctrl + shift + enter.
Book1
FGHIJ
1Matching rowDebit CreditRef no
2551
311102
413163
52-51
6#N/A112
7#N/A-103
8#N/A31
9#N/A272
10#N/A-33
113-102
12#N/A121
134-163
Sheet2
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top