Distribute several unique data from one cell to multiple cells

What I'm trying to do is to distribute the date of encoding of several reports that I had merged in Excel into a set of columns where the dates are shown to each row of data. Here's a screenshot of the raw file:

1.jpg

I'm trying to make it like this one:

2.jpg

Any idea?

I'm also attaching the sample excel file here.

Hi,

Welcome to the forum.

See the attached file. I have used helper columns to get the date cells repeated & used the following formula.

In A12:

{=IFERROR(INDEX(A$1:A$8,SMALL(IF(A$1:A$8<>"",ROW(A$1:A$8)),ROW($A1))),"")}

The above is an array formula and needs to be confirmed by pressing CTRL+SHIFT+ENTER


In B12:
=VLOOKUP($A12,$A$1:$E$8,COLUMN(),0)
drag the above formula to the right and down the cells.


In D12:
=IFERROR(VLOOKUP($A12,$A$1:$E$8,COLUMN()+1,0),"")
drag this down the cells.

Hi cbatrody!

Thank you so much for helping. Although your method is quite complicated-- it works great and seamless.

I might stay in this forum to interact with you guys. I'm an auditor and am working for data analytics stuff.

Thank you so much once again.

Regards,

Frances

Originally Posted by bebo021999

Another solution without helper columns:

See the below image, the formula will fail if there is a bigger number in column C:

For the first three columns by applying the formula

Formula:

Please Login or Register  to view this content.