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

H

HArce

New Member
Joined
Apr 5, 2013
Messages
15
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.
Code: 
==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.

[TABLE="width: 853"]
<TBODY>[TR]
[TD]0000000001</SPAN>
[/TD]
[TD]AAA QUALITAE
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]U, MARY</SPAN>
[/TD]
[TD]TRUE
[/TD]
[TD]JULIE M</SPAN>
[/TD]
[TD]AG03</SPAN>
[/TD]
[/TR]
[TR]
[TD]0000000002</SPAN>
[/TD]
[TD]AAA QUALITAE
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]U, MARY</SPAN>
[/TD]
[TD]TRUE
[/TD]
[TD]JULIE M</SPAN>
[/TD]
[TD]AG03</SPAN>
[/TD]
[/TR]
[TR]
[TD]0000000003</SPAN>
[/TD]
[TD]AAA QUALITAE
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]U, MARY</SPAN>
[/TD]
[TD]TRUE
[/TD]
[TD]JULIE M</SPAN>
[/TD]
[TD]AG03</SPAN>
[/TD]
[/TR]
[TR]
[TD]AAA QUALITAE COUNT</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0000000006</SPAN>
[/TD]
[TD]ROBEE JR
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]BRISK, D</SPAN>
[/TD]
[TD]FALSE
[/TD]
[TD]ANN P</SPAN>
[/TD]
[TD]1084</SPAN>
[/TD]
[/TR]
[TR]
[TD]0000000007</SPAN>
[/TD]
[TD]ROBEE JR
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]BRISK, D</SPAN>
[/TD]
[TD]FALSE
[/TD]
[TD]ARK </SPAN>
[/TD]
[TD]1082</SPAN>
[/TD]
[/TR]
[TR]
[TD]ROBEE JR COUNT</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0000000012</SPAN>
[/TD]
[TD]VAN'DESEL
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]BRISK, D</SPAN>
[/TD]
[TD]TRUE
[/TD]
[TD]BWDQ</SPAN>
[/TD]
[TD]1094</SPAN>
[/TD]
[/TR]
[TR]
[TD]0000000013</SPAN>
[/TD]
[TD]VAN'DESEL
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]BRISK, D</SPAN>
[/TD]
[TD]TRUE
[/TD]
[TD]BWDQ</SPAN>
[/TD]
[TD]1094</SPAN>
[/TD]
[/TR]
[TR]
[TD]0000000014</SPAN>
[/TD]
[TD]VAN'DESEL
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]BRISK, D</SPAN>
[/TD]
[TD]FALSE
[/TD]
[TD]BWDQ</SPAN>
[/TD]
[TD]1094</SPAN>
[/TD]
[/TR]
[TR]
[TD]0000000015</SPAN>
[/TD]
[TD]VAN'DESEL
[/TD]
[TD]BLR</SPAN>
[/TD]
[TD]BRISK, D</SPAN>
[/TD]
[TD]FALSE
[/TD]
[TD]RPWQ</SPAN>
[/TD]
[TD]1095</SPAN>
[/TD]
[/TR]
[TR]
[TD]VAN'DESEL COUNT</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Wanted to provide a bit of clarification on the sample above. Forgot to include the column headers.

[TABLE="width: 640"]
<TBODY>[TR]
[TD="class: xl69, width: 226"]C
[/TD]
[TD="class: xl69, width: 182"]D
[/TD]
[TD="class: xl69, width: 50"]E
[/TD]
[TD="class: xl69, width: 93"]F
[/TD]
[TD="class: xl69, width: 51"]G
[/TD]
[TD="class: xl69, width: 202"]H
[/TD]
[TD="class: xl69, width: 46"]I
[/TD]
[/TR]
[TR]
[TD="class: xl65"]0000000001
[/TD]
[TD="class: xl65"]AAA QUALITAE
[/TD]
[TD="class: xl65"]BLR
[/TD]
[TD="class: xl65"]U, MARY
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]JULIE M
[/TD]
[TD="class: xl66"]AG03
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]0000000002
[/TD]
[TD="class: xl65"]AAA QUALITAE
[/TD]
[TD="class: xl65, bgcolor: transparent"]BLR
[/TD]
[TD="class: xl65, bgcolor: transparent"]U, MARY
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65, bgcolor: transparent"]JULIE M
[/TD]
[TD="class: xl66, bgcolor: transparent"]AG03
[/TD]
[/TR]
[TR]
[TD="class: xl65"]0000000003
[/TD]
[TD="class: xl65"]AAA QUALITAE
[/TD]
[TD="class: xl65"]BLR
[/TD]
[TD="class: xl65"]U, MARY
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]JULIE M
[/TD]
[TD="class: xl66"]AG03
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AAA QUALITAE COUNT
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]0000000006
[/TD]
[TD="class: xl65"]ROBEE JR
[/TD]
[TD="class: xl65"]BLR
[/TD]
[TD="class: xl65"]BRISK, D
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65"]ANN P
[/TD]
[TD="class: xl66"]1084
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]0000000007
[/TD]
[TD="class: xl65"]ROBEE JR
[/TD]
[TD="class: xl65, bgcolor: transparent"]BLR
[/TD]
[TD="class: xl65, bgcolor: transparent"]BRISK, D
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65, bgcolor: transparent"]ARK
[/TD]
[TD="class: xl66, bgcolor: transparent"]1082
[/TD]
[/TR]
[TR]
[TD="class: xl67"]ROBEE JR COUNT
[/TD]
[TD="class: xl65, align: right"]2
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]0000000012
[/TD]
[TD="class: xl65"]VAN'DESEL
[/TD]
[TD="class: xl65, bgcolor: transparent"]BLR
[/TD]
[TD="class: xl65, bgcolor: transparent"]BRISK, D
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65, bgcolor: transparent"]BWDQ
[/TD]
[TD="class: xl66, bgcolor: transparent"]1094
[/TD]
[/TR]
[TR]
[TD="class: xl65"]0000000013
[/TD]
[TD="class: xl65"]VAN'DESEL
[/TD]
[TD="class: xl65"]BLR
[/TD]
[TD="class: xl65"]BRISK, D
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]BWDQ
[/TD]
[TD="class: xl66"]1094
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]0000000014
[/TD]
[TD="class: xl65"]VAN'DESEL
[/TD]
[TD="class: xl65, bgcolor: transparent"]BLR
[/TD]
[TD="class: xl65, bgcolor: transparent"]BRISK, D
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65, bgcolor: transparent"]BWDQ
[/TD]
[TD="class: xl66, bgcolor: transparent"]1094
[/TD]
[/TR]
[TR]
[TD="class: xl65"]0000000015
[/TD]
[TD="class: xl65"]VAN'DESEL
[/TD]
[TD="class: xl65"]BLR
[/TD]
[TD="class: xl65"]BRISK, D
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65"]RPWQ
[/TD]
[TD="class: xl66"]1095
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]VAN'DESEL COUNT
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
Aladin Akyurek said:
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.
Click to expand...

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.
 
Upvote 0
HArce said:
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.
Click to expand...

G2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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))
 
Upvote 0
Aladin Akyurek said:
G2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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))
Click to expand...

Worked amazingly! Thanks again, Aladin!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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
Back
Top