How to generate automatic Family Unique Group ID

[bala04msw]

Dear Forum

I want to create an automatic ID match with two names. Model sheet has been attached for your kind reference.

Describing requirement:

I have a master data set, which catches regular visitors of health center. Column C and M has a couples name. For example: If Column C has husband name, then Column M would have wife name. It happens wise versa also.

Now I need a automatic family unique ID should be created for each family based on name matches with C and M. I have tried the following code. But no positive results.

Please Login or Register  to view this content.

Kindly do the needful.

Thanks in advance.

[Neil_]

I see you are using Parent for unmarried individuals. What happens to the unique ID once they marry? Personally, I'd just use their address, as they are likely to be living together.

[bala04msw]

Dear Mr. Neil,

Thanks for your reply. Here i can use only bread winner name for the family. Bread winner name will be the match name for wife and children. If that adult get married, automatically, they will become a separate family. Hence Kindly do the needful.

Thanks in advance

[Neil_]

Hope this is what you wanted. I've done it with an array formula rather than code.

[bala04msw]

Dear Mr. Neil,

Really thanks for your support. But i really want to match with names. See, This master data set is dynamic one. As you guided, if i apply for rest of the cells and do any new entry it returns each ID for family members also. Kindly help me on this. It would be really appreciated, if VBA code.

Thanks

[Neil_]

I see. Let me re-think

[mikerickson]

Rather than create a new ID for each pair of couple names. Would it work if you just contented the two names and used MaryJonesSamJones as the family ID.

You could alphabetize before concantnating


=IF(C1<M1, C1&M1, M1&C1)

[Neil_]

Hope this is ok for you.

[bala04msw]

Dear Mr. Neil,

Thanks for your efforts and guidance, I will do the test and come back.

Bala

[bala04msw]

Dear Mr. Nail,

Thanks for the support to write a code. But my requirement is very simple. I want to generate ID based on match those two names (Column C (James) and Column V (Reeta)) and vise versa, when Reeta comes in Column C and James Comes in Column V. I dont want to complicate finding Male and all. Sometimes Reeta may get first entry in the list.

Please guide me

[mikerickson]

How about the formula =IF(V1<C1, V1&C1, C1&V1) creating the ID "JamesRita".

If James is in either C1 or V1 and Rita is in the other, the ID will be the same.

[bala04msw]

Dear Mr. mikerickson,

Thanks for your help and support. That was really nice formula and has few disadvantage too. While i am applying this formula, if spouse name is not available, automatically it returns the main person's name. I need to have a Family ID just to find out How many members has been visited as family to my health center as well as services taken by them as family. Hence Could you please help me on this to get Family ID (OD/GJM/FID/0001) in this pattern. Either by formula or VBA code. If VBA code, really i will happy for this.

Thanks in advance.