Dynamically extract unique records to a horizontal column header with formula

[DavisNRJr]

Dear Smartest Excelers in the World

I have a list1 in column A starting in cell a1 and a host of other list in column B-M.
I would like to extract unique values from list 1 and create a dynamic column Header for a new table.

Example:

list 1
1
1
2
3
4
4
5
6
7
7
7

New Table with Dynamic Header Column from list 1
1 2 3 4 5 6 7
Formula is desired.

Thanks Davis

 

[T. Valko]

Wow, That's neat. It works. I have to tell it how far to look in the range when defining the name, can I use counta to tell it how far to look in defining the name, so it can be more dynamic?

We're up against a character limit when defining the dynamic range.

The resulting formula would have to be less than 256 characters long and with your very long file name (including the full path to the file) and sheet name we can't make that character limit.

Just make the range big enough that it will cover any additional info (but don't use the entire column!).

 

[DavisNRJr]

I think I have found the solution, that allows for the idea to work. It was right in front of me the whole time.
=OFFSET(Sheet2!G1,0,0,COUNTA(Sheet2!G:G),COUNTA(Sheet2!1:1))
This allows for a Dynamic expand and contract of the rows and columns in the table.

Thanks for all of your help.

 

[T. Valko]

I think I have found the solution, that allows for the idea to work. It was right in front of me the whole time.
=OFFSET(Sheet2!G1,0,0,COUNTA(Sheet2!G:G),COUNTA(Sheet2!1:1))
This allows for a Dynamic expand and contract of the rows and columns in the table.

Thanks for all of your help.

Good deal. Thanks for the feedback!

 

[DavisNRJr]

Good deal. Thanks for the feedback!

Hi There,

Wanted to update you on the dynamic ability with this solution. If you use the offset function within the name of the range, You make this solution dynamic. Just replace the range a1:a100 with offset(a1,,,counta(a1:a1)). I hope you get the point. This is working very nicely, but I am now trying to make it sort the data in this effort. I will post the final formula when I am done. I wanted to just let you know that this is on the right track. I thought I would make it right and not settle for the issue of not worrying about duplicates. I really do need it to cater to duplicates after all.

Davis