一列中查找值重复怎么提取

rpingxu

一列中的查找值单号有重复的，如果有VLOOKUP只可以提取出一个，怎么用公式跟表格中的显示结果一样？那位大神帮忙下

ovxyz

G2=UNIQUE(A2:A10)
H2=TOROW(FILTER($B$2:$C$10,$A$2:$A$10=G2))


当然你硬要合在一起就是这样。
=LET(a,DROP(IFERROR(REDUCE("",UNIQUE(A2:A10),LAMBDA(s,x,VSTACK(s,HSTACK(x,TOROW(FILTER($B$2:$C$10,A2:A10=x)))))),""),1),VSTACK(HSTACK(A1,REPTARRAY(B1:C1,,COLUMNS(a)/2)),a))
当相同书数量增加到5时需要保证标题行同步增加。

hxj119

1. =REDUCE(G1:M1,UNIQUE(A2:A10),LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(y,TOROW(FILTER(B2:C10,A2:A10=y)))),"")))

复制代码

mykitami

=REDUCE(HSTACK("单号",TOROW(B1:C1&EXPAND("",MAX(COUNTIF(A2:A10,A2:A10)),,""))),UNIQUE(A2:A10),LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(y,TOROW(FILTER(B2:C10,A2:A10=y)))),"")))

你是那块小饼干

=REDUCE({"单号","商品","数量","商品","数量","商品","数量"},UNIQUE(A2:A10),LAMBDA(X,Y,IFNA(VSTACK(X,HSTACK(Y,TOROW(FILTER(B2:C10,Y=A2:A10,"")))),"")))

s971005

低版本：
H2=INDIRECT(TEXT(SMALL(SMALL(IF(ISERROR(FIND($G2,$A$2:$A$10)),65536,ROW($A$2:$A$10)*100+COLUMN($B$1:$C$1)),ROW($A$1:$A$9)),COLUMN(A1:A1)),"R0C00"),)&""
横拉下拉。

zhangcheng6688

学习一下字典，写得不怎么好
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
ReDim brr(1 To UBound(arr), 1 To 1000)
m = 1
For i = 2 To UBound(arr)
   If Not d.exists(arr(i, 1)) Then
      m = m + 1
      For j = 1 To UBound(arr, 2)
         brr(m, j) = arr(i, j)
      Next
      d(arr(i, 1)) = m
      d1(arr(i, 1)) = 4
   Else
      r = d(arr(i, 1))
      c = d1(arr(i, 1))
      For j = 2 To 3
         brr(r, c) = arr(i, j)
         c = c + 1
      Next
      d1(arr(i, 1)) = c
   End If
   If Max < c Then
      Max = c
   End If
Next
brr(1, 1) = "单号"
For k = 2 To Max - 1 Step 2
   brr(1, k) = "商品"
   brr(1, k + 1) = "数量"
Next
[g1].Resize(m, Max - 1) = brr
End Sub