Generate List of Unique Values - INDEX? DISTINCT? VLOOKUP?

Here is what I would suggest:

On your Master Sheet, add in a Helper column of the text/number type. In row 1, enter the following...

=COUNT(DISTINCT(Names$1:Names@row))

then in row 2 enter

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names1)), COUNT(DISTINCT(Names$1:Names@row)))

and dragfill that down the rest of the rows.

.

In your Unique Names sheet, you would then add a column where you simply enter 1, 2, 3, 4, 5, etc... basically entering the row number. We will call this column [Row ID] for this example. You will want to extend beyond what you anticipate, but we can build in an Alert that lets you know you need to add new rows if needed/preferred.

Then in the Unique Names column, you would enter something along the lines of...

=IFERROR(IF(ISNUMBER([Row ID]@row), INDEX({Master Sheet Names Column}, MATCH([Row ID]@row, {Master Sheet Helper Column}, 0))), "")

.

Sorting wouldn't be accomplished through the formula, but this will successfully pull a listing of each unique name from the other sheet.

.

To alert you to needing more rows, you would enter this into a cell on your Unique Sheet in a column that is not the [Unique Names] or [Row ID] column.

=IF(MAX(COLLECT([Row ID]:[Row ID], [Unique Names]:[Unique Names], ISTEXT(@cell))) <> MAX({Master Sheet Helper Column}), "Add " + (MAX({Master Sheet Helper Column}) - MAX(COLLECT([Row ID]:[Row ID], [Unique Names]:[Unique Names], ISTEXT(@cell)))) + " more rows", "No additional rows needed")

.

The above will look at how many rows are populated on your Unique Sheet. It will then compare it to how many rows SHOULD be populated based on the highest number in your Helper column of the Master Sheet.

It will then either display "Add ## more rows" if you need more rows with the ## being replaced with the number of rows needed, or, if there are no more rows needed, it will display "No additional rows needed".

.

You can then either leave as is if you intend to check frequently, use conditional formatting to highlight the cell either red or green depending on whether or not more rows are needed, use an Alert to notify you that you need more rows with the trigger being that particular cell changing to contain the word "more", or you can use a combination of the options.

This will keep you alerted to when new rows are needed.

.

Let me know how all of this works out.

Happy to help! 

You are correct.

Add a row at the top of the list. In the Names column, enter something along the lines of

This row used for formatting

and then lock that row. Make the rest of the rows (the ones containing actual names) child rows under this one.

You would then make a slight modification to the main formula that you dragfilled and use it for ALL rows containing a name including the first name.

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names1)), COUNT(DISTINCT(Names$1:Names@row))) - 1

Then the names will appear in your Unique-Names listing in whatever order they are on your master sheet.

That should solve your sorting issues on both sheets.

Happy to help! 

@Clark Coles I am not sure I follow. Could you set up some mock screenshots that show what you are working with and what you are trying to accomplish?

@Clark Coles Have you tried building out the list and corresponding helper columns on a separate sheet? This will allow you to reference entire columns to pull from for the listing and will not be affected by new rows being added to the main sheet.

Clark,

You do not need a helper column at all in your source sheet.

Create a new sheet that has:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
3. Random column used just to get rows added to your sheet, so keep on typing into more rows and save to add additional rows.
4. "Names" : Column Formula: "=IFERROR(INDEX(DISTINCT(COLLECT({NAMES}, {NAMES}, <>"")), [ROW#]@row), "")

This process makes sure that everything (besides creating the additional rows) runs off of column formulas automatically.

@Leibel Shuchat Thank you, that is working and updating when a new row comes in, I just have to remember to manually add rows.

@Paul Newcome yes I do, I'm open to other suggestions as well :) If I can get it all automated, that would be ideal.