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 · Accepted solution]

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.

[CGIQV]

Worked as advertised.

Thanks!

[Abbey_Kay]

Hi I have a similar issue, but it is a bit more complex. I am not able to identify the "fruit" (in my case the customer number & name) like you were able to do here since it does not specifically say anything before it. How could I do this? I also want to pull item code, description and salesperson and unit of measure to another column. So it would look like the attached file.