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

[pbmc81]

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

[#2]

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.

[#3]

@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

[#4]

@pbmc81 

Hi Patrick,

Another way is to transform by Power Query - convert strings with Territory to list and expand it into new rows