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:
1 Date1 Territory1,Territory2,Territory3
2 Date2 Territory4
3 Date3 Territory5,Territory6
I need to do analysis by territory and I'm looking for a way to produce an output similar to below:
1 Territory1 Date1
2 Territory2 Date1
3 Territory3 Date1
4 Territory4 Date2
5 Territory5 Date3
6 Territory6 Date3
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.
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.
@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