一个客户多列信息转成一行

小花鹿

carlostevez1984 发表于 2019-2-22 15:18
烦请您赐教
For j = 1 To 10  这个10：意思是原始数据的10列吧
For j = 1 To 4   这个4：是客户名称 ...

是电话后面的4列

一把小刀闯天下

'假设E列有序，，，

Option Explicit

Sub test()
  Dim arr, i, j, m, n
  arr = [a1].CurrentRegion.Offset(1)
  ReDim brr(1 To UBound(arr, 1), 1 To 6 + 4 * 20) '最大支持20组，自己修改
  m = 1: n = 6
  For i = 1 To UBound(arr, 1) - 1
    For j = 7 To 10
      n = n + 1: brr(m, n) = arr(i, j)
    Next
    If arr(i, 5) <> arr(i + 1, 5) Then
      For j = 1 To 6: brr(m, j) = arr(i, j): Next
      m = m + 1: n = 6
    End If
  Next
  [a31].Resize(m - 1, UBound(brr, 2)) = brr
End Sub

duquancai

只需要4行代码就能搞定》》》》》

1. # -*- coding: utf-8 -*-
   
2. # version: Python 3.7.0
   
3. from collections import defaultdict
   
4. 
   
5. scro = [
   
6.     ["demao","GC",123456,"1","张三","SD",12345456644,"XX1","移动电话","非亲属"],
   
7.     ["demao","GC",123456,"1","张三","SD",12345456645,"XX2","E-mail","客户"],
   
8.     ["demao","GC",123456,"1","张三","SD",12345456646,"XX3","移动电话","非亲属"],
   
9.     ["demao","GC",123456,"1","张三","SD",12345456647,"XX4","E-mail","客户"],
   
10.     ["demao","GC",123456,"1","张三","SD",12345456648,"XX5","移动电话","非亲属"],
    
11.     ["demao","GC",123456,"1","张三","SD",12345456649,"XX6","固定电话","父亲"],
    
12.     ["demao","GC",123456,"1","张三","SD",12345456650,"XX7","移动电话","父亲"],
    
13.     ["demao","GC",789123,"1","李四","SD",12345456653,"XX10","移动电话","其他亲属"],
    
14.     ["demao","GC",789123,"1","李四","SD",12345456654,"XX11","主要手机号码","客户"],
    
15.     ["demao","GC",789123,"1","李四","SD",12345456655,"XX12","工作电话","客户"],
    
16.     ["demao","GC",789123,"1","李四","SD",12345456656,"XX13","E-mail","客户"],
    
17.     ["demao","GC",789123,"1","李四","SD",12345456657,"XX14","固定电话","父亲"],
    
18.     ["demao","GC",789123,"1","李四","SD",12345456658,"XX15","验证电话","客户"],
    
19.     ["demao","GC",789123,"1","李四","SD",12345456659,"XX16","E-mail","客户"]
    
20. ]
    
21. 
    
22. my_dic = defaultdict(list)
    
23. 
    
24. for i in scro:
    
25.     my_dic[tuple(i[:6])] += i[6:]
    
26. 
    
27. my_res = [list(k)+v for k, v in my_dic.items()]
    
28. 
    
29. print(my_res)

复制代码

zhangcheng6688

小花鹿 发表于 2019-2-22 12:55
Sub test()
Dim d, d1, i&, ar, r&, j&, c&
Set d = CreateObject("scripting.dictionary")

留底精品！！！

zpy2

duquancai 发表于 2019-2-22 23:38
只需要4行代码就能搞定》》》》》

这个没有读Excel表的，是不是olefileio这个库要用下

duquancai

zpy2 发表于 2019-2-23 09:37
这个没有读Excel表的，是不是olefileio这个库要用下

读写Excel就不再赘述！

zpy2

duquancai 发表于 2019-2-23 10:08
读写Excel就不再赘述！

我也来一个 sql
字段
机构,model,GROUP_CONCAT(DISTINCT(合同号)),有效合同,客户名,城市,GROUP_CONCAT(电话||'|'||电话所属人||'|'||电话类型||'|'||与客户关系||'|','<br>') as t

分组
机构,model,客户名,城市

nifeng123

chxw68 发表于 2019-2-22 12:52
详见附件。

谢谢大神。可以用的

nifeng123

小花鹿 发表于 2019-2-22 12:55
Sub test()
Dim d, d1, i&, ar, r&, j&, c&
Set d = CreateObject("scripting.dictionary")

66666666666