Logic formula? - Find Number of Instances and match data in a separate column

[HArce]

All, I have a W/S with a portfolio grouped by their respective relationship names. I need a formula (or VBA) that will first look in COLUMN D, and count the number of instances, then I need it compare the contents of its corresponding cell content in Column I, and finally display whether all cell values in Col I are an exact match.

The purpose of doing this is to identify any split relationship; meaning that the relationship is being handled by multiple parties.

It seems to me like a logical formula would work but after several failed attempts, this is the best I could come up with.

==IF(D1=D2,EXACT(I1,I2),IF(D2=D1,EXACT(I2,I1),""))

This works okay; but, as can be seen, it is limited to a logical statement that only references 2 cells. In the above sample, if D2=D3, then, are I2 and I3 exact matches? I nested an IF statement to do a reverse lookup, but I'm certain there's a better, more thorough way to look at all instances simultaneously and have it return a Split/Unplit, True/False, Match/Unmatch, Yes/No, or any variation thereof.

The challenge is that oftentimes, there are more than 2 accounts in a single relationship with multiple relationships having 15 ~ 40 instances.

I've included a sample below in hopes of providing some clarity on what it is I am after.

Many thanks in advance.

0000000001</SPAN>	AAA QUALITAE	BLR</SPAN>	U, MARY</SPAN>	TRUE	JULIE M</SPAN>	AG03</SPAN>
0000000002</SPAN>	AAA QUALITAE	BLR</SPAN>	U, MARY</SPAN>	TRUE	JULIE M</SPAN>	AG03</SPAN>
0000000003</SPAN>	AAA QUALITAE	BLR</SPAN>	U, MARY</SPAN>	TRUE	JULIE M</SPAN>	AG03</SPAN>
AAA QUALITAE COUNT</SPAN>	3</SPAN>
0000000006</SPAN>	ROBEE JR	BLR</SPAN>	BRISK, D</SPAN>	FALSE	ANN P</SPAN>	1084</SPAN>
0000000007</SPAN>	ROBEE JR	BLR</SPAN>	BRISK, D</SPAN>	FALSE	ARK </SPAN>	1082</SPAN>
ROBEE JR COUNT</SPAN>	2</SPAN>
0000000012</SPAN>	VAN'DESEL	BLR</SPAN>	BRISK, D</SPAN>	TRUE	BWDQ</SPAN>	1094</SPAN>
0000000013</SPAN>	VAN'DESEL	BLR</SPAN>	BRISK, D</SPAN>	TRUE	BWDQ</SPAN>	1094</SPAN>
0000000014</SPAN>	VAN'DESEL	BLR</SPAN>	BRISK, D</SPAN>	FALSE	BWDQ</SPAN>	1094</SPAN>
0000000015</SPAN>	VAN'DESEL	BLR</SPAN>	BRISK, D</SPAN>	FALSE	RPWQ</SPAN>	1095</SPAN>
VAN'DESEL COUNT</SPAN>	4</SPAN>

<TBODY>
</TBODY>

 

[HArce]

Wanted to provide a bit of clarification on the sample above. Forgot to include the column headers.

C	D	E	F	G	H	I
0000000001	AAA QUALITAE	BLR	U, MARY	TRUE	JULIE M	AG03
0000000002	AAA QUALITAE	BLR	U, MARY	TRUE	JULIE M	AG03
0000000003	AAA QUALITAE	BLR	U, MARY	TRUE	JULIE M	AG03
AAA QUALITAE COUNT	3
0000000006	ROBEE JR	BLR	BRISK, D	FALSE	ANN P	1084
0000000007	ROBEE JR	BLR	BRISK, D	FALSE	ARK	1082
ROBEE JR COUNT	2
0000000012	VAN'DESEL	BLR	BRISK, D	TRUE	BWDQ	1094
0000000013	VAN'DESEL	BLR	BRISK, D	TRUE	BWDQ	1094
0000000014	VAN'DESEL	BLR	BRISK, D	FALSE	BWDQ	1094
0000000015	VAN'DESEL	BLR	BRISK, D	FALSE	RPWQ	1095
VAN'DESEL COUNT	4

<TBODY>
</TBODY>

 

[Aladin Akyurek]

What is data and what is output (desired result) is not clear. Try to describe the problem you have without any reference to a formula.

 

[HArce]

What is data and what is output (desired result) is not clear. Try to describe the problem you have without any reference to a formula.

Thanks for the response, Aladin.

All columns ,except column G, is data.

What I hope to accomplish is to quickly identify split relationships. In a nutshell, what I would like to see the program do is, look at column D, and for each alike instance in D compare its contents in column I. If all contents in Col I match, then all is well. If content is unmatched in I, then it'll need to be reviewed.

Using my example, D2 through D4 are are all part of the "AAA Qualitae" relationship, hence, should be assigned to the same person, identifier AG03. Hence, I2 through I4 should all equal AG03.

On the same token, D9 through D12 are part of the "Van'Desel" relationship and as you can see I9 through I12 don't all match. Thus, this relationship will need to be reviewed and assigned accordingly.

Hope that makes more sense.

Thanks again for the help.

 

[Aladin Akyurek]

Thanks for the response, Aladin.

All columns ,except column G, is data.

What I hope to accomplish is to quickly identify split relationships. In a nutshell, what I would like to see the program do is, look at column D, and for each alike instance in D compare its contents in column I. If all contents in Col I match, then all is well. If content is unmatched in I, then it'll need to be reviewed.

Using my example, D2 through D4 are are all part of the "AAA Qualitae" relationship, hence, should be assigned to the same person, identifier AG03. Hence, I2 through I4 should all equal AG03.

On the same token, D9 through D12 are part of the "Van'Desel" relationship and as you can see I9 through I12 don't all match. Thus, this relationship will need to be reviewed and assigned accordingly.

Hope that makes more sense.

Thanks again for the help.

G2, control+shift+enter, not just enter, and copy down:

=IF($I2="","",MATCH($D2&" COUNT",$C$2:$C$13,0)-MIN(IF($D$2:$D$13=$D2,
  ROW($D$2:$D$13)-ROW($D$2)+1))=COUNTIFS($D$2:$D$13,$D2,$I$2:$I$13,$I2))

 

[HArce]

G2, control+shift+enter, not just enter, and copy down:

=IF($I2="","",MATCH($D2&" COUNT",$C$2:$C$13,0)-MIN(IF($D$2:$D$13=$D2,
  ROW($D$2:$D$13)-ROW($D$2)+1))=COUNTIFS($D$2:$D$13,$D2,$I$2:$I$13,$I2))

Worked amazingly! Thanks again, Aladin!

 

[Aladin Akyurek]

Worked amazingly! Thanks again, Aladin!

You are welcome.