In NEED of a Vertical Lookup to remove duplicates and return unique values horizontally!!

[fisht]

Hi All-

I can't seem to figure this one out.. here are the details of the data I am looking to reorganize:

In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.

Example: CT11:CT14 looks like
5x20
6x4
5x20
5x8
and so on and so forth..

I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.

Thank you so much in advance, this will be a huge help!!! and of course let me know if you have any questions.

 

[Weazel]

maybe something like...

Excel 2010

	CT	CU	CV	CW	CX	CY	CZ	DA
9				5x20	6x4	5x8
10
11	5x20
12	6x4
13	5x20
14	5x8

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula CW9 {=IFERROR(INDEX($CT$11:$CT$14,SMALL(IF(FREQUENCY(IF($CT$11:$CT$14<>"",MATCH($CT$11:$CT$14,$CT$11:$CT$14,0)),ROW($CT$11:$CT$14)-ROW($CT$11)+1),ROW($CT$11:$CT$14)-ROW($CT$11)+1),COLUMNS($CW9:CW9))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Jubjab]

Try first doing an advanced filter on the data:

Data->Filter->Advanced-> Select range CT11:CT210, select "Copy to another location" and tick "Unique records only" and "copy to:" an empty column in the same sheet. Click ok and you get the unique records. Select these records, copy, go to cell CW9 and click paste special->transpose.

 

[Comfy]

Excel 2010

	C	D	E	F
6	5x20	6x4	5x8	0
7
8
9
10
11
12	5x20
13	6x4
14	5x20
15	5x8
16	5x20
17	6x4
18	5x20
19	5x8
20	5x20
21	6x4
22	5x20
23	5x8

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C6 {=IFERROR(INDEX($C$12:$C$31, MATCH(0, COUNTIF($B$6:B$6,$C$12:$C$310), 0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[fisht]

Weazel- that's exactly what I needed, thank you very much for you prompt response. It is much appreciated and now I can rest my mind lol.

maybe something like...

Excel 2010

	CT	CU	CV	CW	CX	CY	CZ	DA
9				5x20	6x4	5x8
10
11	5x20
12	6x4
13	5x20
14	5x8

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula CW9 {=IFERROR(INDEX($CT$11:$CT$14,SMALL(IF(FREQUENCY(IF($CT$11:$CT$14<>"",MATCH($CT$11:$CT$14,$CT$11:$CT$14,0)),ROW($CT$11:$CT$14)-ROW($CT$11)+1),ROW($CT$11:$CT$14)-ROW($CT$11)+1),COLUMNS($CW9:CW9))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>