INDEX(DISTINCT) Help

AlannaPnewbie

Hi,

I'm working on a formula that will pull names from a sheet into a list (see below). The sheet I'm pulling from sometimes has multiple names in one cell and also repeats names. I am trying to list each unique name just once. I've been trying the following, but it's pulling multiple names into one row on my list if they are in a shared cell on my sheet (if that makes sense).

=INDEX(DISTINCT({Copy of Management Action Plans Range 2}), 1)

Sheet:

List:

Is there a way to do this or do I need to reformat my sheet?

Matt Lynn ACT

@AlannaPnewbie This is tricky! I had some ideas but ultimately I don't think they work well. If it were limited to how many names could be in one cell then maybe you could prepare for that. You're basically trying to un-concatenate or un join a text string.

I think what you need to do unfortunately is manually list the names that are possible, then your formula would work. I mean you can write a formula to sum or count if the responsible party has() a value which would pull the result even if it's got other names with it, but in that example you still have to tell it what to look for, which is why you'd need to list the possible names for it to pull against.