DATA PREPARATION | Append to Rows (for lack of a better description)

I need to move some rows of data into columns.

Note: All of the rows are currently in one column. I will eventually use the Text-to-Column tool using the space delimiter to break it out into columns.

THIS IS THE SOURCE DATA:

LOCATION-123456 Period-122612-010813 Page-1
6789 6789 JANE Q PUBLIC 9010
TI-A 122812 1105 BK-A 122812 1442
TI-A 122912 1600 TO-A 123012 0126
TI-A 123012 1701 TO-A 123112 0004
0123 5678 JOHN SMITH 9010
TI-A 122612 0930 BK-A 122612 1118
TI-A 122612 1430 BK-A 122612 1544
TI-A 122712 0933 BK-A 122712 1107
TI-A 122712 1440 BK-A 122712 1617
LOCATION-234567 Period-122612-010813 Page-2
4567 4567 JILL Z PRIVATE 9010
TI-A 122812 1105 BK-A 122812 1442
TI-A 122912 1600 TO-A 123012 0126

HOW I WOULD LIKE THE DATA TO BE:
LOCATION-123456 Period-122612-010813 Page-1 6789 6789 JANE Q PUBLIC 9010 TI-A 122812 1105 BK-A 122812 1442
LOCATION-123456 Period-122612-010813 Page-1 6789 6789 JANE Q PUBLIC 9010 TI-A 122912 1600 TO-A 123012 0126
LOCATION-123456 Period-122612-010813 Page-1 6789 6789 JANE Q PUBLIC 9010 TI-A 123012 1701 TO-A 123112 0004
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122612 0930 BK-A 122612 1118
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122612 1430 BK-A 122612 1544
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122712 0933 BK-A 122712 1107
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122712 1440 BK-A 122712 1617
LOCATION-234567 Period-122612-010813 Page-2 4567 4567 JILL Z PRIVATE 9010 TI-A 122812 1105 BK-A 122812 1442
LOCATION-234567 Period-122612-010813 Page-2 4567 4567 JILL Z PRIVATE 9010 TI-A 122912 1600 TO-A 123012 0126

Thanks for any guidance you can provide.

Preparation

Accepted answers

KaneG

Hi @JBO,

In this, you will need to identify each row first. From that point, there's probably 10 different ways to solve it, but I find it's easiest to just split and join.

So, a Multi-row to identify the top grouping (Field: __Row1__):

IF StartsWith([Field1], 'LOCATION')
THEN [Row-1:__Row1__]+1
ELSE [Row-1:__Row1__]
ENDIF

And then another to identify the next grouping (Field: __Row2__):

IF REGEX_Match(LEFT([Field1],9),'\d{4} \d{4}')
THEN [Row-1:__Row2__]+1
ELSE [Row-1:__Row2__]
ENDIF

2 filters, 1 with StartsWith([Field1], 'LOCATION') , the other with REGEX_Match(LEFT([Field1],9),'\d{4} \d{4}')

And then join the main records (false from the 2nd filter) to the other 2 columns

I've included a couple of other methods that I quickly thought of while building the above.

Also, another tip in this kind of operation is a "RecordID with Grouping" is just a Multi-Row with Grouping: [Row-1:Field1]+1

Append Headers as columns.yxmd

All comments