如何将重复数据放到同一行？

楼主悟空师弟	题目：如何将重复数据放到同一行？说明： 1、A列为1-9这9个随机数。 2、将重复数据放到同一行。要求：一个公式右拉下拉完成！（纯函数题，且不可定义名称）效果如下：附件：如何将重复数据放到同一行？.rar 要求：答案贴出公式即可：奖励：正确答案即可获得评分（魅力值 + 5 ，技能 + 5）。截止时间：答案超过10个楼层。（水帖必删）
2楼 piny	D2 =IF(COLUMN(A1)>COUNTIF($A:$A,ROW(A1)),"",ROW(A1)) 右拉下拉
3楼 Zaezhong	=IFERROR(MID(REPT(INDEX($A:$A,SMALL(IF(MATCH($A$2:$A$30,$A$2:$A$30,)=ROW($1:$29),ROW($2:$30)),ROW(A1))),INDEX(COUNTIF($A:$A,ROW($1:$9)),$A2)),COLUMN(A1),1),"")
4楼鬼狐	=if(column(a1)>10,"",INDEX($A:$A,MOD(SMALL(IF(MATCH($A$1:$A$30,$A$1:$A$30,)=ROW($1:$30),IF(COUNTIF($A$1:$A$30,$A$1:$A$30)>=COLUMN($A:$J),ROW($1:$30),4^8)+ROW($1:$30)10^5,10^9+4^8),(ROW(A1)-1)10+COLUMN(A1)),10^5))&"") 没安装兼容，也不知道你有多少行，就暂时做到30行吧另外，此公式在03版本运行不了，因为A列是随机数值，听说在03以上版本可以运行，所以就弄出来看下~~
5楼 tldrtnnhtg	=IF(COUNTIF($A$1:$A$18,N(OFFSET($A$1,SMALL(IF(MATCH($A$1:$A$18,$A$1:$A$18,0)=ROW($A$1:$A$18),ROW($A$1:$A$18),20),ROW())-1,)))>COLUMN()-COLUMN($B$1),OFFSET($A$1,SMALL(IF(MATCH($A$1:$A$18,$A$1:$A$18,0)=ROW($A$1:$A$18),ROW($A$1:$A$18),20),ROW())-1,),"") 数组公式不过只能在07中应用
6楼 fangjianp	没有公式长度限制，公式长度：284。 D2=MID(REPT(MOD(SMALL(IF(ISNA(MATCH(COLUMN($A:$I),$A$2:$A$30,)),99,MATCH(COLUMN($A:$I),$A$2:$A$30,))/1%+COLUMN($A:$I),ROW(A1)),10),COUNTIF($A$2:$A$30,MOD(SMALL(IF(ISNA(MATCH(COLUMN($A:$I),$A$2:$A$30,)),99,MATCH(COLUMN($A:$I),$A$2:$A$30,))/1%+COLUMN($A:$I),ROW(A1)),10))),COLUMN(A:AD),1) 数组公式，右拉下拉上面公式太长了，给个短一点的，公式长度：232 D2=MID(REPT(RIGHT(LARGE(MMULT(2^(30-COLUMN($A:$AC)),($A$2:$A$30=COLUMN($A:$I))5)+COLUMN($A:$I),ROW(A1))),COUNTIF($A$2:$A$30,RIGHT(LARGE(MMULT(2^(30-COLUMN($A:$AC)),($A$2:$A$30=COLUMN($A:$I))5)+COLUMN($A:$I),ROW(A1))))),COLUMN(A1),1) 数组公式，右拉下拉来个更短一些的，公式长度：148 D2=RIGHT(TEXT(LARGE(MMULT(2^(34-COLUMN($A:$AC)),($A$2:$A$30=COLUMN($A:$I))5)+COLUMN($A:$I),ROW(A1))(SUM(N(C2=""&$A$2:$A$31))>=COLUMN(A1)),"[>9]0;")) 数组公式，右拉下拉再短一字符，公式长度：147 D2=RIGHT(TEXT(LARGE(MMULT(2^(34-COLUMN($A:$AC)),($A$2:$A$30=COLUMN($A:$I))5)+COLUMN($A:$I),ROW(A1))(SUM(N(C2=""&$A$2:$A$31))>=COLUMN(A1)),"[>9];")) 数组公式，右拉下拉最外层的函数可以为TEXT(……，"[>9]")。公式长度为146 以上公式在EXCEL2003中可用
7楼落雪绽菊	f2=IF(NOT(COLUMN(A1)>(--RIGHT(LARGE((FREQUENCY($A$2:$A$30,$A$2:$A$30)>0)ROW($A$2:$A$31)ROW($100:$100)+(FREQUENCY($A$2:$A$30,$A$2:$A$30)),ROW(1:1)),2))),INDIRECT("a" & INT(LARGE((FREQUENCY($A$2:$A$30,$A$2:$A$30)>0)ROW($A$2:$A$31)ROW($100:$100)+(FREQUENCY($A$2:$A$30,$A$2:$A$30)),ROW(1:1))/100)),"")
8楼 yuxin78	=IFERROR(INDEX(INDEX($A$2:$A$30,N(IF(1,SMALL(IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,ROW($A$2:$A$30)-1,""),ROW(INDIRECT(1&":"&SUM(N(COUNTIF($A$2:$A$30,$A$2:$A$30)>1)))))))),N(IF(1,TRANSPOSE(SMALL(IF(INDEX($A$2:$A$30,N(IF(1,SMALL(IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,ROW($A$2:$A$30)-1,""),ROW(INDIRECT(1&":"&SUM(N(COUNTIF($A$2:$A$30,$A$2:$A$30)>1))))))))=ROW(1:1),ROW(INDIRECT(1&":"&SUM(N(COUNTIF($A$2:$A$30,$A$2:$A$30)>1)))),""),COLUMN(A1)))))),"")
9楼 mn860429	重在参与，函数的不会写。 Sub cc() k = 1 For i = 2 To 30 If Application.CountIf(Range([a1], Cells(i, 1)), Cells(i, 1)) = 1 Then k = k + 1 Cells(k, 4).Value = Cells(i, 1).Value Else Cells(Application.Match(Cells(i, 1).Value, Columns(4), 0), 255).End(xlToLeft).Offset(, 1).Value = Cells(i, 1).Value End If Next i End Sub
10楼 wangg913	D2:AG10 =MID(INDEX(REPT(A2:A32,COUNTIF(A:A,A2:A32)),SMALL(IF(FREQUENCY(A:A,A:A),ROW(1:30),31),ROW(1:9))),COLUMN(A:AD),1)
11楼 laowuwyx	=INDEX($A:$A,SMALL(IF($A$2:$A$30=INDEX($A:$A,SMALL(IF(FREQUENCY($A$2:$A$30,$A$2:$A$30),ROW($2:$30)),ROW(A1))),ROW($2:$30),4^8),COLUMN(A1)))&"" 写了一个超级复杂的。
12楼 bluexuemei	=IF(COLUMN(A1)<=COUNTIF($A$2:$A$30,INDEX($A:$A,SMALL(IF(MATCH($A$2:$A$30,$A$2:$A$30,)=ROW($1:$29),ROW($1:$29),4^8)+1,ROW(1:1)))),INDEX($A:$A,SMALL(IF(MATCH($A$2:$A$30,$A$2:$A$30,)=ROW($1:$29),ROW($1:$29),4^8)+1,ROW(1:1)))&"","")
13楼 chenhh803	=INDEX($A:$A,SMALL(IF($A$2:$A$33=(ROW()-1),ROW($A$2:$A$33),34),COLUMN()-3))&""
14楼 sharprain	=IF(COLUMN()-3>COUNTIF($A$2:$A$30,ROW()-1),"",ROW()-1) 公式右拉，下拉