Forum Discussion

pbmc81's avatar
pbmc81
Copper Contributor
Jun 13, 2019

Need to restructure my data set. Any macro for this?

Hello,

 

I'm working with a large data set that outputs reports grouped by date, with territory values comma delimited in a single cell, as follows:

 

       A              B

1  Date1     Territory1,Territory2,Territory3

2  Date2     Territory4

3  Date3     Territory5,Territory6

Etc..

 

I need to do analysis by territory and I'm looking for a way to produce an output similar to below:

 

       A               B

1 Territory1   Date1

2 Territory2   Date1

3 Territory3   Date1

4 Territory4   Date2

5 Territory5   Date3

6 Territory6   Date3

Etc...

 

I've discovered the 'Text to Columns' feature and am able to split the territory values into separate columns, but so far I can't figure out how to merge them into a singular column connected to the appropriate date. I've spent all day looking up macros and messing around with the Power Query Editor to no avail. I'm about ready to give up and start doing all of this manually.

 

I'm pretty obviously out of my depth here. Any suggestions on how to accomplish this would be greatly appreciated.

 

Thanks,

Patrick

excel
Formulas and Functions
Macros and VBA

3 Replies

  • TheThinker1958's avatar
    TheThinker1958
    Copper Contributor
    Jun 13, 2019

    pbmc81in case you have too many territories for each date, you can run this macro.  Just select the first date of your data and run the macro

    Sub PREPARE_DATA()
    ' ------------------------
    'SELECT FIRST DATE
    XADDR = ActiveCell.Address
    XROW1 = ActiveCell.Row
    If Len(Cells(ActiveCell.Row + 1, ActiveCell.Column)) <> 0 Then
       Selection.End(xlDown).Select ' MOVE TO BOTTOM OF THE LIST IF MORE THAN 1 ROW OF DATA
    End If
    XROW2 = ActiveCell.Row
    XROWS = XROW2 - XROW1 + 1
    XCOL = ActiveCell.Column
    Range(XADDR).Select

    XNEWROW = XROW2 + 4 ' row where your data will go

    For I = XROW1 To XROW2
      XDATE = ActiveCell.Value
      Selection.Offset(0, 1).Select
      While Len(ActiveCell.Value) <> 0
        XTERRITORY = ActiveCell.Value
        Cells(XNEWROW, XCOL) = XDATE
        Cells(XNEWROW, XCOL + 1) = XTERRITORY
        XNEWROW = XNEWROW + 1
        Selection.Offset(0, 1).Select
      Wend
      Cells(ActiveCell.Row + 1, XCOL).Select
    Next I

    Cells(XROW2 + 4, XCOL).Select
    ' ------------------------
    End Sub

  • TheThinker1958's avatar
    TheThinker1958
    Copper Contributor
    Jun 13, 2019

    you can do it manually.

    after separating the territories into different columns, you need to copy the date column in between all the columns.  Then you just need to copy the 2nd set of  DATE/TERRITORY below the 1st set, then the 3rd set, etc.

    then you sort all the sets by territory and date.

    you probably will end with many dates without a territory. Those rows you can just delete.

    now you just have to make your territory column your first one.

Resources