multiple tables to 1 list with flexible rows

[sukin]

Hi all,

I am working on a new sheet combining multiple tables from one sheet into 1 long list.

Only the length of the tables can be variable and dynamic.

Currently, I made a work around to code lines in use and empty/last lines.

Below is an example of the coded lines I already made.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]
[TABLE="width: 479"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Corresponds with row no.[/TD]
[TD] A [/TD]
[TD] B [/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

Now, all the ones need to be listed in a single list.

So far, when column A --> the ones end my list start with the 1's in column B.

The problem is when column A for example expands of shrinks with filled lines I need the flexibility to keep starting the first line of column B after the last line of column A.
Now, a VBA solution would be the best way I guess, but is there also an option to just use a combo of regular formulas?

I am this far:

IF($C6=1,$C$5,IF(INDIRECT("$D"&IF($C6<>1,$B$6+$A7,1))=1,$D$5,IF(INDIRECT("$e"&IF(AND($C6<>1,$D6<>1),$B$6+$A7,1))=1,$E$5,IF(INDIRECT("$f"&IF(AND($C6<>1,$D6<>1,$E6<>1),$B$6+$A7,1))=1,$F$5,IF(INDIRECT("$g"&IF(AND($C6<>1,$D6<>1,$E6<>1,$F6<>1),$B$6+$A7,1))=1,$G$5,0)))))

I need something to make the $A7 part flexible

kind regards,

Suk In

 

[sandy666]

post a link to shared excel file with representative example source and expecting result.
you can use google drive, one drive or any similar service

edit:
what is your Excel (Office) version. eg. 2007, 2016, 2019 or eny other.

 

[sukin]

Hi,

below is the link to a test file of mine.

https://1drv.ms/x/s!AnPMESFKI9iwgZtt9eFP0wLd5Wlc1g

I am using excel for office 365, so I guess it's like 2016.