Repeat Multi Row Formula

[G_SAND]

Hi All

I have a set of data where I want to 'copy' the information from the rows above but on multiple columns. I can do it with multiple 'Multiple Row Formulas' but wondered if there was a quicker way

From:

Name	Batch	D1	D2	D3	D4	D5	D6	D7	D8
A	X	25	9	2	6	3	7	9	12
A	X
A	X

To

Name	Batch	D1	D2	D3	D4	D5	D6	D7	D8
A	X	25	9	2	6	3	7	9	12
A	X	25	9	2	6	3	7	9	12
A	X	25	9	2	6	3	7	9	12

I'm sure it is simple.

Thanks

[LordNeilLord · Accepted solution]

Hi,

I think the easiest way would be to transpose the data and then use the multirow...once the data is filled you can crosstab it back to return the original format:

[DataBlender]

Hi @G_SAND

Here's two possible ways of doing it.

The first is to transpose your data so that you only need to do one multi-row formula.

The other is to summarize to take the max value for each name, batch and column, then join it on to just the name and batch columns of your original data - that way you keep the required number of rows.

I'm sure there are other ways!

[Claje]

EDIT: LordNeil has the same solution I do.

Hi,

Depending on how big your dataset is, the attached workflow should work.

Basically, by using transpose and crosstab, you can put all your D1-D8 values into one field, and then use that field, grouping on the name field generated by the transpose tool to make sure you get valid values.

If your dataset is large (Read: 1,000,000+ rows and 100+ columns) this may not be the fastest way of solving the problem.

[G_SAND]

Thanks @LordNeilLord, I've gone with this one for now.

I am just checking a small set of data first to see if the process I want to do will work, then I'll be adding more to the data set.

So @DataBlender and @Claje - I may try your solutions as well.

Thanks as always