欄位轉換
請教各位大大小弟我有一資料轉換格是如下A 將 COST 跟 DATE轉換格式，不知道該怎麼寫比較好呢
格式A
ID NAME COST DATE
AXXX JACK 50 1月1日
AXXX JACK 60 1月2日
BXXX MARY 30 1月3日
BXXX MARY 40 1月4日
想轉換成格式B
ID NAME COST1 COST2 DATE1 DATE2
AXXX JACK 50 60 1月1日 1月2日
BXXX MARY 30 40 1月3日 1月4日 假設你的資料 "ID" 就在 [A1] 開始, 代碼如下:[code]Sub zz()
Dim d As Object, dd As Object, k, t, n&, m&, HL$, b()
Set d = CreateObject("scripting.dictionary")
Set dd = CreateObject("scripting.dictionary")
a = [a1].CurrentRegion
For i = 2 To UBound(a)
k = a(i, 1)
d(k) = d(k) + 1
dd(k) = dd(k) & "|" & i
Next
n = Application.Max(d.items)
HL = a(1, 1) & "|" & a(1, 2)
For j = 3 To UBound(a, 2)
For jj = 1 To n
HL = HL & "|" & a(1, j) & jj
Next
Next
t = Split(HL, "|")
ReDim b(1 To d.Count + 1, 1 To UBound(t) + 1)
Workbooks.Add 1
[a1].Resize(1, UBound(b, 2)) = t
k = dd.keys: n = 0
For i = 0 To UBound(k)
t = Split(dd(k(i)), "|")
n = n + 1
b(n, 1) = a(t(1), 1)
b(n, 2) = a(t(1), 2)
m = 2
For j = 3 To UBound(a, 2)
For jj = 1 To UBound(t)
m = m + 1
b(n, m) = a(t(jj), j)
Next
Next
Next
[a2].Resize(n, UBound(b, 2)) = b
End Sub
[/code] [url]https://blog.xuite.net/hcm19522/twblog/589226148[/url]
頁:
[1]