Splitting Columns into Multiple

[barrywhitelaw]

Hopefully this can be done without a Macro?

Using Power Query, I get a result with 3 columns, and varying number of rows.

This is then going to be linked to a PowerPoint document, but the number of rows is too many to fit and be readable on a single slide. We are currently manually moving a group of cells around to make it look better before pasting onto the slide. All other slides are linked back to the workbook so just needs a refresh..

is there a way to split by say 30 rows into as many columns as need?

Sample file enclosed.

thanks

[Greg M]

Hi there,

Based on your workbook:

What's the maximum number of rows you can fit on a slide? 30??

What's the maximum number of columns you can fit on a slide? 3??

Regards,

Greg M

[Fluff13]

How about

Please Login or Register  to view this content.

[barrywhitelaw]

Hi there,

Based on your workbook:

What's the maximum number of rows you can fit on a slide? 30??

What's the maximum number of columns you can fit on a slide? 3??

Regards,

Greg M

Hi Greg

So, the link between the workbook and the slide places it into the slide as an image, so this can be resized slightly, but as a guide I would say 30-40 rows max. As for the columns, we are placing 2 wide each time, so we may get away with 4 or 5 double columns.

thanks
Barry

[barrywhitelaw]

How about

Please Login or Register  to view this content.

Hi there

Thanks for this, not sure how I use that though. Would this be used as a formula in Power Query, or somewhere in the Excel worksheet?

thanks
Barry

[AliGW]

It's a formula - nothing to do with PQ. Just paste it on a new worksheet.

[Greg M]

Hi again,

Thanks for that, but ...

As for the columns, we are placing 2 wide each time

the workbook you posted shows THREE columns (Index, Cost Centre and Spend). Which of these columns should be excluded?

Regards,

Greg M

[barrywhitelaw]

Hi again,

Thanks for that, but ...

the workbook you posted shows THREE columns (Index, Cost Centre and Spend). Which of these columns should be excluded?

Regards,

Greg M

My bad

We can exclude the Index column, that's not required. I put it there thinking it may be needed for some formula.

thanks
Barry

[Fluff13]

If you don't want the 1st column you can just use the original table

Please Login or Register  to view this content.

[barrywhitelaw]

If you don't want the 1st column you can just use the original table

Please Login or Register  to view this content.

Perfect, this does exactly what I need. Thank you!

[Fluff13]

Glad to help & thanks for the feedback.

[Greg M]

Hi there,

Here's a totally different approach which you might find interesting.

You can choose to spread the values involved can over 3, 4 or 5 "mini-tables" which can contain up to 40 rows. Values are distributed evenly over the appropriate number of rows.

The above choice is implemented in Cell F1.

Conditional formatting is used to hide unused rows (and tables 4 and 5 if appropriate).

The Index columns (i.e. H, L, P, T and X) can be hidden if required

Take a look at the attached workbook and see what you think - formulas and conditional formatting are relatively straightforward, but would be difficult to explain here in a few words!

I'd be pleased to hear any comments you might have.

Regards,

Greg M