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).

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


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
	Current Customer

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Analysis 1[/TD]
[TD="align: center"]Current or New[/TD]

[TD="align: center"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]=IF(MEDIAN([@[Final Approval Date]]-15,[@[Final Approval Date]]+15,[@[Inactive Date]])=[@[Inactive Date]],"Current Customer","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=IF([@[ABC Co. Status]]="Active","Current Customer",IF([@[Analysis 1]]="Current Customer","Current Customer","New Customer"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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):

[table="width: 500, class: grid"]
[TR]
[th]Row #[/th]
[Th]Grouping[/Th]
[Th]ID #[/Th]
[Th]Name[/Th]
[Th]Final Approval Date[/Th]
[Th]File Status[/Th]
[Th]Liability[/Th]
[Th]Liability Co. Name[/Th]
[Th]Liability Acct #[/Th]
[Th]ABC Co. Status[/Th]
[Th]Inactive Date[/Th]
[Th]Analysis 1[/Th]
[Th]Current or New[/Th]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]987654[/TD]
[TD]Jill[/TD]
[TD]03-01-14[/TD]
[TD]10[/TD]
[TD]1st Mortgage[/TD]
[TD]ABC Co.[/TD]
[TD]456[/TD]
[TD]Inactive[/TD]
[TD]04-05-15[/TD]
[TD]New Customer[/TD]
[TD]New Customer[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]987654[/TD]
[TD]Jill[/TD]
[TD]04-01-15[/TD]
[TD]10[/TD]
[TD]1st Mortgage[/TD]
[TD]ABC Co.[/TD]
[TD]789[/TD]
[td]Active[/td]
[td][/td]
[td][/td]
[TD]Current Customer[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]987654[/TD]
[TD]Jill[/TD]
[TD]04-04-15[/TD]
[TD]10[/TD]
[TD]1st Mortgage[/TD]
[TD]BMX Industrial[/TD]
[TD]987[/TD]
[td][/td]
[td][/td]
[td][/td]
[TD]New Customer[/TD]
[/TR]
[/TABLE]

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):

[table="width: 500, class: grid"]
[TR]
[th]Row #[/th]
[Th]Grouping[/Th]
[Th]ID #[/Th]
[Th]Name[/Th]
[Th]Final Approval Date[/Th]
[Th]File Status[/Th]
[Th]Liability[/Th]
[Th]Liability Co. Name[/Th]
[Th]Liability Acct #[/Th]
[Th]ABC Co. Status[/Th]
[Th]Inactive Date[/Th]
[Th]Analysis 1[/Th]
[Th]Current or New[/Th]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]987654[/TD]
[TD]Jill[/TD]
[TD]04/01/15[/TD]
[TD]10[/TD]
[TD]1st Mortgage[/TD]
[TD]ABC Co.[/TD]
[TD]456[/TD]
[TD]Inactive[/TD]
[TD]03-01-14[/TD]
[TD]New Customer[/TD]
[TD]New Customer[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]987654[/TD]
[TD]Jill[/TD]
[TD]04-01-15[/TD]
[TD]10[/TD]
[TD]1st Mortgage[/TD]
[TD]ABC Co.[/TD]
[TD]789[/TD]
[td]Active[/td]
[td][/td]
[td][/td]
[TD]Current Customer[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]987654[/TD]
[TD]Jill[/TD]
[TD]04-01-15[/TD]
[TD]10[/TD]
[TD]1st Mortgage[/TD]
[TD]BMX Industrial[/TD]
[TD]987[/TD]
[td][/td]
[td][/td]
[td][/td]
[TD]New Customer[/TD]
[/TR]
[/TABLE]

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
Current Customer

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Current or New[/TD]

[TD="align: center"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=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"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.