Auto Spill Rearranged Data Across Range

[Mgc26133]

Hello all

Every day I am receiving a pdf file, and I'm converting it to Excel. However, long cells in the range split their data in a separate row due to their formatting on the pdf. I'm trying to come up with a spill formula to automatically control the range and fix the data to the way it should be, but I can't get it to spill across the range.

Attaching the manually done version on a simplified sample. Can you take a look and see if it's doable?

Regards

[nick.williams]

Try something like this?

=LET(a,IF($A$2:$A$16="",0,1)+IF($B$2:$B$16="",0,1),b,IF($A$3:$A$17="",0,1)+IF($B$3:$B$17="",0,1),c,CHOOSE({1,2},IF(a=0,"",IF(b=0,$C$2:$C$16&" "&$C$3:$C$17,IF($C$2:$C$16="","",$C$2:$C$16))),IF(a=0,"",IF(b=0,$D$2:$D$16&" "&$D$3:$D$17,IF($D$2:$D$16="","",$D$2:$D$16)))),c)

[nick.williams]

Or maybe this if you want to get rid of the blanks in columns A and B?

=LET(a,IF($A$2:$A$16="",0,1)+IF($B$2:$B$16="",0,1),b,IF($A$3:$A$17="",0,1)+IF($B$3:$B$17="",0,1),c,CHOOSE({1,2,3,4},$A$2:$A$16,$B$2:$B$16,IF(a=0,"",IF(b=0,$C$2:$C$16&" "&$C$3:$C$17,IF($C$2:$C$16="","",$C$2:$C$16))),IF(a=0,"",IF(b=0,$D$2:$D$16&" "&$D$3:$D$17,IF($D$2:$D$16="","",$D$2:$D$16)))),FILTER(c,a>0))

[Bo_Ry]

Maybe try

=FILTER(C2:D16,A2:A16)&FILTER(REPT(C2:D16,A2:A16=0),N(+A1:A15))

or

=LET(a,A2:A16,b,A2:B16,z,A1:A15,c,A2:D16,FILTER(c,a)&FILTER(REPT(c,a=0),N(+z)))

[Mgc26133]

@Nick
That didn't work, unfortunately. It gave two working results at the top, and spilled #N/A error across the rest of the range.

@Bo_Ry
That is amazing! However, is it possible to make it work with full column references, like instead of A2:A16, it would be A:A? The reason is, the main data has varying number of rows, it changes every day. Sometimes it's 362, sometimes 545, sometimes 297, another day 644, etc. So with your formula I would still have to manually change the ranges in the formula each time. So if there could be a formula that works regardless of the data length that would be the best. Preferably the 2nd one because in the end that's the data I'm aiming to get, but I can work with the 1st one also.

[nick.williams]

That's strange, it worked when I tested it, but Bo_Ry's solution looks nicer anyway.

[Mgc26133]

Bo_Ry hi.

So I figured the range problem. I just gave it an astronomical number and that solved it. But now the trouble is, since this file is converted from pdf, it also has the column titles from the top of each page. I didn't realize this would be important so I didn't include it to the sample, and that's my bad, I apologize for that.

I tried to fix it as much as I can using SUBSTITUTE and IFERROR, but I've hit a dead end.

I reattached the file, can you take a look? This time it's near identical to the main data, I promise so if we figure this one out that would solve this thread.

[Bo_Ry]

Please try

=LET(a,A2:A999,b,A2:B999,z,A1:A998,c,A2:J999,x,FILTER(c,N(+a))&FILTER(REPT(" "&c,a=0),N(+z)),IFERROR(--x,x))

[Mgc26133]

Works like a charm Many thanks!