INDEX WITH TRANSPOSE FORMULA iIN EXCEL

[SAXON10]

Hi,

Column A2:A20 Contain with duplication text and the result should be appear in C2 without duplication.

Any help much appreciated.

[TABLE="width: 336"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]CODE[/TD]
[TD][/TD]
[TD="colspan: 3"]RESULT[/TD]
[/TR]
[TR]
[TD]1515[/TD]
[TD][/TD]
[TD]1515[/TD]
[TD]1616[/TD]
[TD]1717[/TD]
[/TR]
[TR]
[TD]1515[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1515[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1515[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1515[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1717[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

Array formula in C2 copied across
=IFERROR(INDEX($A$2:$A$20,SMALL(IF(FREQUENCY(IF($A$2:$A$20<>"",MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMNS($C2:C2))),"")
Ctrl+Shift+Enter

M.

 

[AhoyNC]

If you have Excel 2016 you could use the TEXTJOIN function to be all the results into C2.

This is also an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	CODE		RESULT
2	1515		1515 1616 1717
3	1515
4	1515
5	1515
6	1515
7
8	1616
9	1616
10	1616
11	1616
12	1616
13	1616
14
15	1717
16	1717
17	1717
18	1717
19	1717
20	1717
Sheet

 

[SAXON10]

thanks for your help formula working fine.