最新情况反馈表

西瓜9527

希望把每周最新反馈的情况（例如标绿的部分）体现在“G列-I列”的最新反馈结果
应如果实现，用函数或者VBA都可以，谢谢~

Jaofuan

1. Sub jaofuan()
   
2.     With Sheet1
   
3.         hw = .Cells(Rows.Count, 1).End(xlUp).Row
   
4.         .Range("g5:i" & hw) = ""
   
5.         For i = 5 To hw
   
6.             lw = .Cells(i, Columns.Count).End(xlToLeft).Column
   
7.             If lw > 9 Then
   
8.                 If .Cells(3, lw) = "客户情况" Then r = 1 Else r = 2
   
9.                 ar = .Cells(i, lw - r).Resize(1, r + 1)
   
10.                 .Cells(i, "g").Resize(1, r + 1) = ar
    
11.             End If
    
12.         Next
    
13.     End With
    
14. End Sub
    

复制代码

NadrsSaber

Sub test()

    Dim xRow&
    Dim xRng As Range
    With Sheet1
    xRow = .Range("A5").End(xlDown).Row
    For Each xRng In .Range("A5:A" & xRow)
        If Len(xRng.Offset(0, 21)) <> 0 Then
            .Range(Cells(xRng.Row, 22), Cells(xRng.Row, 24)).Copy .Cells(xRng.Row, 7)
        ElseIf Len(xRng.Offset(0, 18)) <> 0 Then
            .Range(Cells(xRng.Row, 19), Cells(xRng.Row, 21)).Copy .Cells(xRng.Row, 7)
        ElseIf Len(xRng.Offset(0, 15)) <> 0 Then
            .Range(Cells(xRng.Row, 16), Cells(xRng.Row, 18)).Copy .Cells(xRng.Row, 7)
        ElseIf Len(xRng.Offset(0, 12)) <> 0 Then
            .Range(Cells(xRng.Row, 13), Cells(xRng.Row, 15)).Copy .Cells(xRng.Row, 7)
        ElseIf Len(xRng.Offset(0, 9)) <> 0 Then
            .Range(Cells(xRng.Row, 10), Cells(xRng.Row, 12)).Copy .Cells(xRng.Row, 7)
        End If
    Next
    End With

End Sub

朝花夕拾2018

2楼可以实现，

wodewan

函数：G5单元格公式下拉右拉：=TEXT(IFNA(INDEX(5:5,LOOKUP(9^9,$J5:$BB5,ROW($10:$100))+COLUMN(A5)-1)&"",""),"YYYY/M/D")