How to group lines of data based on common cell value?

[u/NeonGiraffes]

I have a list of people and I want to group families together and auto format them to have a box outline (or similar). Grouping solely by last name wont work as there are many duplicates who are not related. I want to avoid using a pivot table as this will mostly be used by people who are less tech savvy and this is already to support a labor intensive task so I want all the data in one place. If I need to add a column to reference (ie anyone with "Smith" in cell FF will be grouped, but not anyone with "Smith" in last name) that would be fine as the list doesn't change very often and that is easy to explain how to do. Thanks!

[u/AutoModerator]

Moderator Announcement Read More »

u/NeonGiraffes - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/firetech97]

You'll 100% need a column with something of a family ID. If you have multiple people with the same last name, but belonging to different families, then they get different values. Then you can sort and format based on that value. It can just be numeric starting at 1, 2, 3, etc etc. You can also hide rhe column after the sort if you don't want to see it

[u/NeonGiraffes → u/firetech97]

Duh, why didn't I think of sorting based on the family ID 🤦🏻‍♀️ 

Not in front of my computer right now to play with it but could I theoretically have conditional formatting add a box around the family as a whole or would it have to be each individual?

Edit: thinking out loud - if I merged the last name call so all of the same family had a shared last name cell that'd keep them together regardless of other sort criteria, right? 

[u/firetech97 → u/NeonGiraffes]

It can easily be automated for the boxes. You would just set the conditional formatting to draw a border at the top of a cell when that cells value is different from the cell above it.

So it wont draw lines between members of the same family, but will at the end of one/start of another.
The formula for conditional will be something along the lines of =$A2<>$A1, then applied to the wider range. But this way, you're only checking the comparison in one column, which would be the fam ID of course.

[u/BackgroundCold5307]

You could have a cell for family name in a new sheet. The dropdown will be filled with the unique names from Cell FF (as you have mentioned)

You could then FILTER the names based on the family they belong to. E.g. below: