Combine Similar Fields into multiple entries with one field

[KyleF]

I currently have a solution, but I am hoping that someone has a better way than just a lot of "Select" tools, renaming fields, and then a "Union" tool.

I have a dataset that generally looks like this:

Month

Day

1Bill

1To

1From

1Time

1B

1P

2Bill

2To

2From

2Time

2B

2P

3Bill

3To

3From

3Time

3B

3P

5

10

ABC

LAX

JFK

5

1

0

ABC

SEA

LAX

3

1

0

ABC

SFO

SEA

3

1

0

5

12

DEF

SFO

SEA

3

1

0

DEF

DEN

SFO

3

1

0

EFG

ORD

DEN

3

0

1

And I would like to get it into a dataset that looks like this:

Month	Day	Bill	To	From	Time	B	P
5	10	ABC	LAX	JFK	5	1	0
5	10	ABC	SEA	LAX	3	1	0
5	10	ABC	SFO	SEA	3	1	0
5	12	DEF	SFO	SEA	3	1	0
5	12	DEF	DEN	SFO	3	1	0
5	12	EFG	ORD	DEN	3	0	1

[Prometheus]

@KyleF I started out with the Transpose tool to flip the data and put the future field names into one field where I could use the RegEx tool to parse out the number as the first character and turn it into an ID field and the rest into a field named Field. Then I used the Cross Tab tool to pivot it back while grouping data on Month, Day, and ID. 

 

 

 

 

 

[binuacs]

@KyleF Another way of doing this