归一化级联的Excel数据 [英] Normalize concatenated Excel data

问题描述

 任务| TaskRepeat 
 Task1 | M，W ，F 
 Task2 | T，Th 
  

我想将其转换为： / p>

  Task1 | M 
 Task1 | W 
 Task1 | F 
 Task2 | T 
 Task2 | Th 
  

我熟悉VBA，但在Access中使用它比Excel更多。是否有Excel解决方案（公式或VBA）？

解决方案

这是代码，它假设你的列表在Col A和B中，将列表放在D和E中，数据从第2行开始

  Sub Normalize ）
 
 Dim lngFirstRow As Long 
 Dim lngLastRow As Long 
 Dim cRow As Long 
 Dim shSrc As Worksheet 
 Dim lngNextDestRow As Long 
 Dim varDay As Variant 
 Dim arrDays（）As String 
 
 Application.ScreenUpdating = False 
 
 lngFirstRow = 2 
 lngNextDestRow = 2 
 
 
设置shSrc = ActiveWorkbook.ActiveSheet 
 
使用shSrc 
 
 .Range（D1）= .Range（A1）
。范围（E1）= .Range（B1）
 
 lngLastRow = .Cells.Find（What：=*，After：=。Cells.Cells（1），LookAt：= xlPart，LookIn：= xlFormulas，SearchOrder：= xlByRows，SearchDirection：= xlPrevious，MatchCase：= False）.Row 
 
 cRow = lngFirstRow To lngLastRow步骤1 
 
 arrDays（）= Split（.Range（B& cRow），，）
 
对于每个varDay在arrDays 
 .Range（D& lngNextDestRow）= .Range（A& cRow）
。范围（E& lngNextDestRow）= varDay 
 
 lngNextDestRow = lngNextDestRow + 1 
下一个varDay 
 
下一个cRow 
结束
 
 Application.ScreenUpdating = True 
 End Sub 
  

I have an Excel spreadsheet that has data like this:

Task  |TaskRepeat  
Task1 |M,W,F  
Task2 |T,Th  

I would like to transform it to this:

Task1  | M  
Task1  | W  
Task1  | F  
Task2  | T  
Task2  | Th  

I am familiar with VBA, but use it a lot more in Access than Excel. Is there an Excel solution to this task (either a formula or VBA)?

解决方案

Here is the code, it assumes your list is in Col A and B and will put the List in D and E and that the data starts on row 2

Sub Normalize()

    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim cRow As Long
    Dim shSrc As Worksheet
    Dim lngNextDestRow As Long
    Dim varDay As Variant
    Dim arrDays() As String

    Application.ScreenUpdating = False

    lngFirstRow = 2
    lngNextDestRow = 2


    Set shSrc = ActiveWorkbook.ActiveSheet

    With shSrc

     .Range("D1") = .Range("A1")
     .Range("E1") = .Range("B1")

      lngLastRow = .Cells.Find(What:="*", After:=.Cells.Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

      For cRow = lngFirstRow To lngLastRow Step 1

           arrDays() = Split(.Range("B" & cRow), ",")

            For Each varDay In arrDays
                .Range("D" & lngNextDestRow) = .Range("A" & cRow)
                .Range("E" & lngNextDestRow) = varDay

                 lngNextDestRow = lngNextDestRow + 1
            Next varDay

        Next cRow
    End With

         Application.ScreenUpdating = True
 End Sub

这篇关于归一化级联的Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！