What's new
Carbonite

South Africa's Top Online Tech Classifieds!
Register a free account today to become a member! (No Under 18's)
Home of C.U.D.

Excel help

TheMerchant

Epic Member
Rating - 100%
24   0   0
Joined
Mar 3, 2016
Messages
749
Reaction score
40
Points
3,185
Hi guys, I'm not clued up on excel formulas that can help me figure out my problem, I have a list of about 4000 people, all these people are either main member spouse or child

I need to change the main members to "00", Spouse to "01" and the first child of each main member must be "02" and then if they have a second child it's "03" etc etc

Now with Main and Spouse it's a simple case of Find/Replace

But it becomes tricky with children

Can anyone more experienced please help?
 

Attachments

  • 2.png
    2.png
    11.7 KB · Views: 32
How is the data organised? Could you explain the attached image and the meaning of the colour coding?
 
What data do you use to know they are a main member or spouse or a child?
They are labeled as main, spouses are usually directly after main members if that member has a spouse
 
I don't see a way to do it with a formula. A macro can definitely sort it out.
If there are very few people with 2 or more kids, it would be more feasible to do that bit manually (by identifying who's got more than one kid via pivot table and 'count').
 
This should work, just replace with your correct text:
=IF(A5="Main";0;B4+1)
View attachment 39925
This looks like a good solution...

@TheMerchant , remember to :

1) backup data before you start
2) remember to copy the output of your formula and paste the values, so if you rearrange the data, it won't get mixed up
 

Users who are viewing this thread

Latest posts

Back
Top Bottom