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]

Please view video, it almost works.
http://screencast.com/t/qWkxf4SAK7TU

Here's a small sample file that demonstrates this.

zzzUniques.xlsx 10kb

http://cjoint.com/?BHftacHeR1a

 

[DavisNRJr]

Here's a small sample file that demonstrates this.

zzzUniques.xlsx 10kb

http://cjoint.com/?BHftacHeR1a

That looks perfect, but I have to play around with it in my spreadsheet. Thanks, I should be able to make it work, I hope.

 

[DavisNRJr]

The two formulas got me. :0(
I have never tried anything like that.

Still working on this.
Davis

 

[DavisNRJr]

Here's a small sample file that demonstrates this.

zzzUniques.xlsx 10kb

http://cjoint.com/?BHftacHeR1a

Yeh, Got It!!!!!!!!!!!!!!!!!!!!!!
What a challenge for me. Thanks for everyones help.


Davis

 

[DavisNRJr]

Here is the Fish that took me a while to get in the Boat.

=IFERROR(INDEX('[Import A Clean Master Parts File QG.xlsm]A Master Clean Master Parts Fil'!$F$5:$F$59,MATCH(0,COUNTIF($A1:A1,'[Import A Clean Master Parts File QG.xlsm]A Master Clean Master Parts Fil'!$F$5:$F$59),0)),"")

Thanks Everybody.
Davis

 

[DavisNRJr]

OOps,

I needed a Dynamically expanding formula. When I add items to the list in the column, I need the headers to grow with the list. Is anybody still there?, so far it works great, but it isn't expanding with added data.

Davis

 

[T. Valko]

OOps,

I needed a Dynamically expanding formula. When I add items to the list in the column, I need the headers to grow with the list. Is anybody still there?, so far it works great, but it isn't expanding with added data.

Davis

This means we're going to have to start from scratch since you're trying to extract from another file!

One thing that can make a big difference in the readability of the formulas is using SHORT file names and sheet names! To get around the very long file/sheet names that you have I'm going to use defined named ranges.

In the file Import A Clean Master Parts File QG.xlsm, create a named range that refers to:

='A Master Clean Master Parts Fil'!$F$4:$F$100

I use down to row 100. Adjust that to suit your needs that will allow for future data addition.

I'm naming this range Rng1.

In the file where you want the unique data to appear create another named range that refers to:

='Import A Clean Master Parts File QG.xlsx'!Rng1

I'm naming this range Rng2.

Then, in the file where you want the unique data to appear, on some sheet in cell A1 enter this array formula**:

=IFERROR(INDEX(Rng2,SMALL(IF(Rng2<>"",IF(ROW(Rng2)-MIN(ROW(Rng2))+1=MATCH(Rng2,Rng2,0),ROW(Rng2)-MIN(ROW(Rng2))+1)),COLUMNS($A1:A1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across until you get blanks.

 

[DavisNRJr]

OK, I will try this,
PS. I thought this post was closed after i gave it 5 stars. I would like to know if there is a way to merge the two threads?

Please advise.

Davis

 

[T. Valko]

OK, I will try this,
PS. I thought this post was closed after i gave it 5 stars. I would like to know if there is a way to merge the two threads?

Please advise.

Davis

When the moderators see that there are 2 duplicate threads they may merge both threads. They will usually do that.

 

[DavisNRJr]

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?