Jun 12 2019 07:58 PM
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
Jun 12 2019 08:05 PM
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.
Jun 12 2019 08:28 PM
@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
Jun 13 2019 03:31 AM
Hi Patrick,
Another way is to transform by Power Query - convert strings with Territory to list and expand it into new rows