Help with matching data in two columns with a control number

[jmujjf]

Hello,

I am hoping someone can assist me with figuring out a formula that can add a flag in a "NoMatch" type column as displayed below based on a similar set of data. The purpose is to identify where a particular ID has a debit without a matching credit. I assume the fact that the ID can be used more than twice makes this even more complicated but I am hoping someone has encountered a problem like this before. Thank you in advance for any assistance you can provide.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]NoMatch[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]65[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[jmujjf]

I've been brainstorming trying to determine a way to do this. Maybe it isn't even possible using a formula? Is there any other way I might not have considered that would enable me to easily manipulate the data? The end goal would be to determine the ID number and associated total credit balance if the Debit side equals 0. Also, upon a second look at the data my example should probably look more like:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]NoMatch[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD][/TD]
[TD]75[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD][/TD]
[TD]65[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

You should take a look at following thread :

https://www.mrexcel.com/forum/excel-questions/962187-formula-match-debit-credit-values-2.html#post4621021

HTH

 

[jtakw]

Hi,

Your requirements are unclear to me...just a guess here:

Book1
	A	B	C	D	E	F
1	ID	Debit	Credit	NoMatch
2	1234	25
3	1234		25
4	1235	0		X		X
5	1235		75	X	X
6	1237	35
7	1237	30
8	1237		65
9	1238	0		X		X
10	1238	1		X
11	1238		50	X	X
Sheet270

Cell Formulas
Range	Formula
D2	=IF(SUMIF(A$2:A$11,A2,B$2:B$11)=SUMIF(A$2:A$11,A2,C$2:C$11),"","X")
E2	=IF(COUNTIF(A$2:A2,A2)=COUNTIF(A$2:A$11,A2),IF(SUMIF(A$2:A$11,A2,B$2:B$11)=SUMIF(A$2:A$11,A2,C$2:C$11),"","X"),"")
F2	=IF(COUNTIF(A$2:A2,A2)=1,IF(SUMIF(A$2:A$11,A2,B$2:B$11)=SUMIF(A$2:A$11,A2,C$2:C$11),"","X"),"")

D2 formula will Flag All rows for IDs with No Match
E2 formula will Flag Last instance of IDs with No Match
F2 formula will Flag First instance of IDs with No Match

Formulas copied down.

Again, I'm Not even sure I understand your requirements.