Index/Match not picking up nth instances in list

[Ebo12]

Hey everyone,

I'm currently working on a rather large report to help my executive management team identify the number of "New" vs. "Current" customers who financed with our company during 2015 (sample chart shown below). For the purpose of this sample, let's say I work for ABC Company.

Column "Analysis 1" is a helper column that I added to identify which customers are definitely current customers based on whether a date exists under "Inactive Date" that is +/- 15 days of the date under "Final Approval Date". If column "ABC Company Status" shows "active", then they are also a "current customer". Every other customer = "New Customer".

The formula that I'm currently using (and having trouble with) is under column "Current or New":
=IF([@[ABC Co. Status]]="Active", "Current Customer",IF(INDEX([Analysis 1],MATCH([@Grouping],[Grouping],0))<>"", INDEX([Analysis 1],MATCH([@Grouping],[Grouping],0)),"New Customer")).

The issue I'm running into though is that this formula doesn't pick up on instances where the entry isn't the first instance in the grouping (even if it matches the criteria)... For example, Row 7 "Jake" should be a current customer based on the "Analysis 1" designation, as should Row 19 "Katie". I have instances on my report where "Active" customers are also incorrectly labeled. I think that maybe Small could be used to solve for this.... but I'm not familiar enough with that function to know where to start with it (if it even fits at all for that matter).

Row #	Grouping	ID #	Name	Final Approval Date	File Status	Liability	Liability Co. Name	Liability Acct #	ABC Co. Status	Inactive Date	Analysis 1	Current or New
1	1	123456	John	01-30-15	10	1st Mortgage	ABC Company	123	Active			Current Customer
2	2	987654	Jill	04-01-15	10	1st Mortgage	ABC Co.	456	Inactive	04-05-15	Current Customer	Current Customer
3	2	987654	Jill	04-01-15	10	1st Mortgage	Wells Fargo	789				Current Customer
4	2	987654	Jill	04-04-15	10	1st Mortgage	BMX Industrial	987				Current Customer
5	3	7894561	Jake	06-18-15	10	1st Mortgage	BNY Mellon	654				New Customer
6	3	7894561	Jake	06-18-15	10	1st Mortgage	Bank Of America	321				New Customer
7	3	7894561	Jake	06-18-15	10	1st Mortgage	ABC Company	135	Inactive	06-19-15	Current Customer	New Customer
8	3	7894561	Jake	06-18-15	10	1st Mortgage	Rochester Bank	679				New Customer
9	4	654321	John	09-07-15	10	1st Mortgage	BNY Mellon	976				New Customer
10	4	654321	John	09-07-15	10	1st Mortgage	Discover Card	523				New Customer
11	5	555555	Henry	10-12-15	10	1st Mortgage	Bank Of America	741				New Customer
12	5	555555	Henry	10-12-15	10	1st Mortgage	BMX Industrial	852				New Customer
13	5	555555	Henry	10-12-15	10	1st Mortgage	ABC Co.	963	Inactive	06-01-03	New Customer	New Customer
14	6	222222	Katie	11-02-15	10	1st Mortgage	Quicken Loans	111				New Customer
15	6	222222	Katie	11-02-15	10	1st Mortgage	Bank of New York	222				New Customer
16	6	222222	Katie	11-02-15	10	1st Mortgage	Nationstar	333				New Customer
17	6	222222	Katie	11-02-15	10	1st Mortgage	Lenders Choice	999				New Customer
18	6	222222	Katie	11-02-15	10	1st Mortgage	Rochester Bank	888				New Customer
19	6	222222	Katie	11-02-15	10	1st Mortgage	ABC Co.	777	Inactive	11-03-15	Current Customer	New Customer

Any suggestions on how to solve for this would be much appreciated. Thank you in advance!

 

[jorismoerings]

Hi,

I can't distinguish if you want just the rows changed with the correct customer type of if it's the intention to mark all instances of the same client as New or Current.
I assumed the fist and changed your formula to this:

	L	M
1	Analysis 1	Current or New
2		Current Customer

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula L2 =IF(MEDIAN([@[Final Approval Date]]-15,[@[Final Approval Date]]+15,[@[Inactive Date]])=[@[Inactive Date]],"Current Customer","") M2 =IF([@[ABC Co. Status]]="Active","Current Customer",IF([@[Analysis 1]]="Current Customer","Current Customer","New Customer")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope this helps.

 

[Ebo12]

Hi Jorismoerings,

Thank you for your reply! The intent is to have the formula under Column M display the same (and correct) designation of "Current Customer"/"New Customer". Column L is a helper column that I had in there, and is working correctly for me.

Here is an example of what I need: Grouping 3 for example (which is Row # 5,6,7,8), should display "Current Customer" for each instance of the grouping # (since it is +/- 15 days, or Active). The goal is once this is fixed, I would then be able to use "Remove duplicates" in Excel and not remove the correct designation.

Does that help? Thank you again for assisting!

 

[jorismoerings]

Hi,

try this:

Book1
	M
1	Current or New
2	Current Customer
Sheet1

Cell Formulas
Range	Formula
M2	=IF([@[ABC Co. Status]]="Active","Current Customer",IF(COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name],[Analysis 1],"")=COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name]),"New Customer","Current Customer"))

NB: will only work from Excel 2007 or upwards. If you are below this version let me know; I'll rephrase.

 

[Ebo12]

That worked pretty well! Thanks for that man.

Can I request a small tweak though please? Management just asked me to amend "Analysis 1" (column L) so that if [@Inactive Date]-[Final Approval Date]>=-365, "Current Customer", "New Customer".
I made the change to L to accommodate, however, now it is causing M (in instances of the same customer) to display both "New Customer" and "Current Customer" in the grouping whenever one instance of the customer is <-365, but other instances are Active or >=-365

Example (revised from the original table to illustrate):

Row #	Grouping	ID #	Name	Final Approval Date	File Status	Liability	Liability Co. Name	Liability Acct #	ABC Co. Status	Inactive Date	Analysis 1	Current or New
2	2	987654	Jill	03-01-14	10	1st Mortgage	ABC Co.	456	Inactive	04-05-15	New Customer	New Customer
3	2	987654	Jill	04-01-15	10	1st Mortgage	ABC Co.	789	Active			Current Customer
4	2	987654	Jill	04-04-15	10	1st Mortgage	BMX Industrial	987				New Customer

I would need it to say "If one instance of Current Customer" is present in the matching group, then always Current Customer. I apologize for asking assistance again after that good fix you proposed, but do you have a suggestion for this scenario as well?

Thank you again!

 

[Ebo12]

Sorry I messed up the table data. Revised below in quote:

That worked pretty well! Thanks for that man.

Can I request a small tweak though please? Management just asked me to amend "Analysis 1" (column L) so that if [@Inactive Date]-[Final Approval Date]>=-365, "Current Customer", "New Customer".
I made the change to L to accommodate, however, now it is causing M (in instances of the same customer) to display both "New Customer" and "Current Customer" in the grouping whenever one instance of the customer is <-365, but other instances are Active or >=-365

Example (revised from the original table to illustrate):

Row #	Grouping	ID #	Name	Final Approval Date	File Status	Liability	Liability Co. Name	Liability Acct #	ABC Co. Status	Inactive Date	Analysis 1	Current or New
2	2	987654	Jill	04/01/15	10	1st Mortgage	ABC Co.	456	Inactive	03-01-14	New Customer	New Customer
3	2	987654	Jill	04-01-15	10	1st Mortgage	ABC Co.	789	Active			Current Customer
4	2	987654	Jill	04-01-15	10	1st Mortgage	BMX Industrial	987				New Customer

I would need it to say "If one instance of Current Customer" is present in the matching group, then always Current Customer. I apologize for asking assistance again after that good fix you proposed, but do you have a suggestion for this scenario as well?

Thank you again!

 

[jorismoerings]

hi,

That's not caused by the changes you did but is caused by the first part of the formula which looks at Active Stage. In your Sample above [Name] is now inactive and Active at same time. To solve change the formula so if any of grouped rows is Active; change to Current Customer.
See below:

	M
1	Current or New
2	Current Customer

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula M2 =IF(COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name],[ABC Co. Status],"Active")>0,"Current Customer",IF(COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name],[Analysis 1],"New Customer")=COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name]),"New Customer","Current Customer")) <tbody> </tbody>

<tbody>
</tbody>

 

[Ebo12]

Hi Jorismoerings,

I tweaked it slightly to the following (based on your earlier formula) and it worked perfectly:
=IF(COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name],[ABC Co. Status],"Active")>0,"Current Customer",IF(COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name],[Analysis 1],"")=COUNTIFS([ID '#],[@[ID '#]],[Name],[@Name]),"New Customer","Current Customer"))

Thank you so much for helping out on this one and best!

 

[jorismoerings]

We got there in the end.
Glad I could help.