按格式合并数据

youyukuku

请伦论坛大神帮忙：
                          按格式需求提取数据！（详见附件）🙏

无关岁月

1. =LEFT(B2)&":"&TEXTJOIN(",",,IF(C2:AG2<>"",C2:AG2&"/"&TEXT(C$1:AF$1,"dd"),""))

复制代码

mykitami

=LEFT(B2)&":"&TEXTJOIN(",",,IF($C2:$AG2<>"",$C2:$AG2&"/"&TEXT($C$1:$AG$1,"dd"),""))

swl4561255

1. Sub test()
   
2. ar = [b65536].End(3).Row
   
3. arr = Range("B1:AG" & ar)
   
4. Dim brr(1 To 1000, 1 To 1)
   
5. For i = 2 To ar
   
6.     mys = Left(arr(i, 1), 1) & ":"
   
7.     st1 = ""
   
8.     For j = 2 To 32
   
9.         If arr(i, j) <> "" Then
   
10.             st1 = st1 & "，" & arr(i, j) & " / " & Day(arr(1, j))
    
11.         End If
    
12.     Next
    
13.     brr(i - 1, 1) = mys & Mid(st1, 2)
    
14. Next
    
15. [AJ2].Resize(UBound(brr), 1) = brr
    
16. End Sub

复制代码

quqiyuan

=LEFT(B2)&":"&TEXTJOIN(",",,IF(C2:AF2<>"",C2:AF2&"/"&TEXT(DAY($C$1:$AF$1),"00"),""))

E花E世界

1. =LEFT(B2)&"："&TEXTJOIN(",",,IF(C2:AG2<>"",C2:AG2&"/"&DAY(C$1:AG$1),""))

复制代码