• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a way to select the number of rows in a range based on a condition in another range

E

ELanc

New Member
Hi,
I have several large data files consiting of contract/account information containing each person named on the account and their role. For each contract/account I need to test that any person in a "beneficiary" role is the same person named in either one of two other roles on the account. I have created the below formula which correctly tests what I need but the processing time is significant (20 to 40 minutes) when I'm going through a large file (e.g. 50,000 rows). I'm looking for ways to do this more efficiently.

=IF(K2="Beneficiary",SUMPRODUCT(--(Contract_Num=A2),--(Name=S2),--((Role="Annuitant")+(Role="Designated Life"))),"")

Contract_num - Column A: Contains the contract/Account number - there are multiple rows per account (anywhere from 4 to 10 rows)
Name - Column S: Name of the person acting in that role. One person can be more than one role.
Role - Column K: Contains the role. Each role/name combo is a separate row.
All the other columns are irrelevant for this task.​

Is there any way to structure this, or any other formula, so that the Names and Relation range is limited to only the rows where the Contract_Num=A2 condition is true? Hoping that would limit processing time - Matching across a few rows at a time vs the entire data set as the formula is copied down the rows.

Thanks for any advice.
 
Yes. Multiple ways. If you can sort your data alphabetically, you can employ something called Range Slicing, where you make clever use of Index and Match to work out where the block of data of interest starts and ends.

Or you can use Excel's Database functions: DSUM etc

Or you can use Excel's CHOOSE function.

I have a great sample workbook, but it's too big to post here.

Tell you what: I'll write up a blog post at either Daily Dose of Excel or at Chandoo.org (if I'm still allowed there) outlining some of these approaches.

Feel free to email me on weir.jeff@gmail.com if you want me to send you my sample file.
 
Thanks. Here's the best I can do while I'm at work... you at least get a visual. I can post a sample file later.

Account Role Name Formula Result
xxxx Owner John Doe
xxxx Annuitant John Doe
xxxx Designated Life Jane Doe
xxxx Beneficiary Jane Doe 1
nnnn Owner David Smith
nnnn Joint Owner Kathy Smith
nnnn Annuitant David Smith
nnnn Designated Life Kathy Smith
nnnn Beneficiary David Smith 1
nnnn Beneficiary Kathy Smith 1
A1234 Owner Steve M Jones
A1234 Annuitant Steve M Jones
A1234 Designated Life Janet M Jones
A1234 Beneficiary Peyton A Jones 0
A5678 Owner Leona Black
A5678 Joint Owner Mark Black
A5678 Annuitant Leona Black
A5678 Designated Life Mark Black
A5678 Beneficiary Mark Black 1
A5678 Beneficiary Deborah Black 0
 
You must log in or register to reply here.
Back
Top