Column data into rows (with a limit on number of records in each column)

[Empower49]

Hello Community. As a newbie, I've tried the basic table tool, cross tab tool, render tool, and transpose tool, but am stuck.

Current output looks like this:

Group 1

Pumpkin pie

Gravy

Mashed Potatoes

Group 2

Salad

Rolls

Wine

Group 3

Sweet potatoes

Cranberries

Stuffing

Goal is this:

Group 1

Pumpkin pie, Gravy, Mashed Potatoes etc. through #9

more, more, more, through #18

more, more, more, through #27

Group 2

Salad, Rolls, Wine etc. through #9

more, more, more, through #18

more, more, more, through #27

BUT each group includes ~50 records and I need to format this so there are only 9 records per row (each row is only one column) and do not want each value in a separate column. Do you have any recommendations? Thank you!

[ponraj]

Here is the sample logic for your case.  Share the complete sample data in excel sheet for any further help. 

WorkflowResults

[Empower49]

Wow! You are awesome. Thank you.

SO close! I must be doing something wrong because my output is not quite right, it's repeating the data in each row and putting new records in row 6, 12, 18, etc.

RecordID	RowNo	Concat_Plan_number2
1	0	'1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01'
1	1	'1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01'
1	2	'1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01'
1	3	'1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01'
1	4	'1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01','1260459-01'
1	5	'1260459-01','1260459-01','1260459-01','1260459-01','1260459-01'
2	6	'1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01'
2	7	'1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01'
2	8	'1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01'
2	9	'1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01'
2	10	'1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01','1260490-01'
2	11	'1260490-01','1260490-01','1260490-01','1260490-01','1260490-01'
3	12	'1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01'
3	13	'1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01'
3	14	'1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01'
3	15	'1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01'
3	16	'1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01','1260491-01'
3	17	'1260491-01','1260491-01','1260491-01','1260491-01','1260491-01'
4	18	'333932-01','333932-01','333932-01','333932-01','333932-01','333932-01','333932-01','333932-01','333932-01'

[jdunkerley79]

If I am understanding what you are wanting correctly (i.e. just capture first 9 rows)

You can use a Sample tool to do this. In First N Rows mode, it will truncate the data after a set number of rows.

[ponraj]

Possible to share sample source data with workflow?

[Empower49]

Hi! Actually, I want each row to include 9 values, but want all rows to be populated (not sampled.) So, I want it to look exactly like Ponraj's image, but need my data in the concat_dummay column. Thank you!

[Empower49]

Sure! Thanks SO much for your help.

[Empower49]

Here's a sample data file. Thank you!

[jdunkerley79 · Accepted solution]

Sorry I had misunderstood the problem.

I suggest:

The multi row formula controls number of items in each line and is set to reset every change in 'Database'

The multi field formula converts from the fractional value to an integer and change the type of the row

Finally as per @ponraj solution you can use a summarise to make all 9 values appear in a comma separated list

[Empower49]

Wow! Thank you SO much for sharing your expertise. This is awesome. I really appreciate your help and can't thank you both enough.