How to generate automatic Family Unique Group ID - Tried with few formula

[bala04msw]

Dear Forum,

Good morning everyone. Today i have come up with different and complicated requirement (As far as my concern, not for the genius who around here) to learn. Model Data set has been attached with anticipate result message box for your kind notice.

Explaining data set:

Data set has few individuals details, those who come for bank for proposing loan for their requirement and the entire family has also proposed individually for their respective needs.

Requirement: For analysis purpose, i want to get the data of "How many families have been sanctioned loan during this financial year".

1. To meet this requirement, i would like to generate automatic unique family ID for each and every family. Based on count of Family ID, i can easily get No. of family details and total amount of benefits.

2. To create Family ID, every individual detail has their spouse / parent name in "M" column. For example, If husband applies, he has mentioned his wife name and If wife applies, she has mentioned her husband name and If children applies, he /she mentioned his father name in the spouse / parent "M"column.

3. The logic to create automatic family ID is, If wife / Children applies, they mention their husband / father name in the "M"Column. In that case, if husband name match with Column "C", automatically Family ID should be generated and the same Family ID should be displayed for Husband also. The same process should be adopt for while child applies.

Additional Information: Already we have set automatic unique customer ID (Column H) for all the individual to propose loan at bank. This is entirely separate system and not at all related to this.

I have tried with MATCH function and created helper column to generate ID. =IFERROR(IF(MATCH(M5,C:C,0),"Yes",""),"")

HOPE PIVOT TABLE WON'T HELP IN THIS SITUATION, THOUGH I AM NOT PREFERRING IT.

Kindly guide me and Thanks in advance.
Bala

[AliGW]

Your problem here is that you cannot have both manual entry and a formula in a cell. You can heva a formula (INDEX MATCH) to return a family ID if one exists based on surname and name in column M, and that can return a blank if no match is found, but to enter a new family ID, it would have to be overwritten.

[jason.b75]

Array confirmed in N4, then filled down.

=IF(M4="","",IF(MATCH(M4,C:C,0)>ROW(N4),"FID - "&TEXT(MAX(IFERROR(RIGHT(N$3:N3,5)+1,1)),"00000"),INDEX(N$3:N3,MATCH(M4,C$3:C3,0))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[bala04msw]

Dear Ms. Ali,

Thanks for your understanding of my requirement. But Mr. Jason's formula helped me here. I will learn the logic, he applied and get back to you.

Thanks for addressing my requirement.

Bala

[bala04msw]

Dear Mr. Jason,

Thanks a lot for addressing my complicated requirement and helping me. It works well. I have only one concern, the Family ID comes only once in their respective family members row. For example: Could you please see, Row No: 16 & 23, contains, Ms. Sonia Karmavath's details, when she visited first time (22.08.2014) Family ID displayed, but when she came 2nd time to bank on 19.09.2014 Family ID is not displayed.

Is it the function of Index? or else. Could you please teach me on this?

Million thanks

Bala

[bala04msw]

Dear Ms. Ali,

=IF(M5="","", - Logical test for blank cells, if M column does not have any name.

IF(MATCH(M5,C:C,0)>ROW(N5), (Logical test for match the name M=C, but to be honest, do not understand why >Row(N5),


"FID -"&TEXT(MAX(IFERROR(RIGHT(N$3:N4,5)+1,1)),"00000"), - Formula for generate Automatic ID


INDEX(N$3:N4,MATCH(M5,C$3:C4,0)))) – Formula for getting table reference.

I need your guidance to get clarify 2 things:

1. Why >Row(N5)
2. Why Family ID is not generated, while the family members entry comes 2nd time.

Could you please teach me on this?

Thanks in advance

Bala

[jason.b75]

Row 23 is an individual application, not a family application.

Parent name is missing in column M.

You can work around it with this, but you really should be more consistent with your data and your requirements.

=IF(M4="",IFERROR(INDEX($N$4:$N$63,MATCH(TRUE,IF($C$4:$C$59=C4,$M$4:$M$59<>""),0)),""),IF(MATCH(M4,C:C,0)>ROW(N4),"FID - "&TEXT(MAX(IFERROR(RIGHT(N$3:N3,5)+1,1)),"00000"),INDEX(N$3:N3,MATCH(M4,C$3:C3,0))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[bala04msw]

Dear Mr. Jason,

yes, i am really sorry. yes i got it. Thanks a lot for your support.

Bala

[jason.b75]

IF(MATCH(M5,C:C,0)>ROW(N5), (Logical test for match the name M=C, but to be honest, do not understand why >Row(N5),

MATCH > ROW means ID has not yet been generated, so the formula needs to create one.

If the ID has already been generated then the formula looks for the existing ID.

[bala04msw]

Dear Mr. Jason,

Perfect ... I understood the formula. Million thanks.

Thanks