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]

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

Where do you want the results to appear?

What version of Excel are you uisng?

Is the data really numbers that are sorted in ascending order?

 

[DavisNRJr]

I am putting them into a new worksheet using Office 2010. The values are "34 Letters" Format.

 

[Teethless mama]

In C2: =IFERROR(INDEX($A2:$A12,MATCH(,COUNTIF($B2:B2,$A2:$A12),)),"")

ctrl+shift+enter, not just enter

 

[p45cal]

Edit post posting: Oops, misread the requirement, thought you wanted list of those with just one of in the list. Ignore. (Love the way it puts 4 asterisks in the 'reason for editing' field!

Array-Enter formula into F5 and copy to right:

*	A	B	C	D	E	F	G	H	I	J
list 1	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #ccffcc, align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]3[/TD]
[TD="bgcolor: #ccffcc, align: right"]5[/TD]
[TD="bgcolor: #ccffcc, align: right"]6[/TD]
[TD="bgcolor: #ccffcc"]#NUM![/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F5 {=SMALL(IF(COUNTIF($A$2:$A$12,$A$2:$A$12)=1,$A$2:$A$12),COLUMN()-5)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[T. Valko]

I am putting them into a new worksheet using Office 2010. The values are "34 Letters" Format.

Let's assume your data is on Sheet1 in the range A1:A11.

On some other sheet...

Enter this formula in A1:

=Sheet1!A1

Enter this array formula** in B1:

=IFERROR(INDEX(Sheet1!$A1:$A11,MATCH(0,COUNTIF($A1:A1,Sheet1!$A1:$A11),0)),"")

** 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]

I am looking at it now, and will reply shortly. Thanks

 

[DavisNRJr]

Please view video,

http://screencast.com/t/qWkxf4SAK7TU

 

[DavisNRJr]

Please review video.
http://screencast.com/t/qWkxf4SAK7TU

 

[DavisNRJr]

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