Move Data From Merged Columns and Repeat In Rows

CGIQV

Here is an example of the report I can pull:

EDIT: After posting I realized the merged columns did not come through.  The "FRUIT: xxxx" cells are merged across all of the data headers in the report:

Fruit: APPLES
random data
more random data
Account Number	Code	Total
10000	x	100
10001	y	500
10002	z	200
10003	s	200
10004	a	250
Fruit: PEARS
other random data
Account Number	Code	Total
10000	y	854
10005	n	12

I want to be able to create a column all the way to the left from the "Fruit: xxxx" merged data.  It should end up looking like this:

Fruit	Account Number	Code	Total
Apples	10000	x	100
Apples	10001	y	500
Apples	10002	z	200
Apples	10003	s	200
Apples	10004	a	250
Pears	10000	y	854
Pears	10005	n	12

Any thoughts on how to accomplish this?

CharlieS

Hi @CGIQV 

I started out with a Multi-Row Formula tool to identify the fruit value and then keep repeating it until another fruit value was found, After some clean up of values and unnecessary rows, I went about identifying the header rows. I did this by looking for duplicated values in the non-numeric fields. This is just one example that works in this case, so other methods might be necessary in other scenarios. Finally, I used a Dynamic Rename tool to apply those field names and used a Select tool to re-order the fields for the final result.

Check out the example solution attached to see this in action.

20190604-FruitTransformation.yxmd