combine two tables in single column without blank cells

[jb]

I have two sample tables table-1 and table-2 on sheet1 (sample file attached).

Table-1 a4 to c6 and table2 a10 to c12.

I want to combine two tables in single column and blank cells are to be removed in output.

I have 4 solutions for creating seperate list for table-1 and table-2.

3 solutions are on sheet1 which uses formula and 4th solution is on sheet2 which uses VBA coding.

I prefer 4th or 3rd solution. But now I am not able to create single list for both the tables. Rather I want single list to be created directly without creating seperate list.

 

[GraH - Guido]

Perhaps a small tweak of your formula in K4
=IFERROR(INDIRECT("Sheet1!"&TEXT(SMALL(IF((Sheet1!$A$4:$C$12<>"")*(LEFT(Sheet1!$A$4:$C$12,5)<>"Table") ,ROW(Sheet1!$A$4:$C$12)*10^4+COLUMN(Sheet1!$A$4:$C$12)),ROWS($A$4:A4)),"R0000C0000"),0),"")

 

[bosco_yip]

Maybe,

Solution-4 : combine your Solution-3 two formulas into one :

=IFERROR(INDIRECT(TEXT(IF(ROWS($1:1)<=COUNTA(A$4:C$6),SMALL(IF(A$4:C$6<>"",ROW($4:$6)/1%%+{1,2,3}),ROWS($1:1)),SMALL(IF(A$10:C$12<>"",ROW($10:$12)/1%%+{1,2,3}),ROWS($1:1)-COUNTA(A$4:C$6))),"R0000C0000"),0),"")

Regards
Bosco

 

[jb]

Perhaps a small tweak of your formula in K4
=IFERROR(INDIRECT("Sheet1!"&TEXT(SMALL(IF((Sheet1!$A$4:$C$12<>"")*(LEFT(Sheet1!$A$4:$C$12,5)<>"Table") ,ROW(Sheet1!$A$4:$C$12)*10^4+COLUMN(Sheet1!$A$4:$C$12)),ROWS($A$4:A4)),"R0000C0000"),0),"")

Thanks a lot. Worked perfectly.

 

[jb]

Maybe,

Solution-4 : combine your Solution-3 two formulas into one :

=IFERROR(INDIRECT(TEXT(IF(ROWS($1:1)<=COUNTA(A$4:C$6),SMALL(IF(A$4:C$6<>"",ROW($4:$6)/1%%+{1,2,3}),ROWS($1:1)),SMALL(IF(A$10:C$12<>"",ROW($10:$12)/1%%+{1,2,3}),ROWS($1:1)-COUNTA(A$4:C$6))),"R0000C0000"),0),"")

Regards
Bosco

Thanks a lot. It worked perfectly.

 

[bines53]

Hi jb ,

You got a nice solution, but forced because of the limitations of Excel, the function is built on a volatile and array, "lethal combination", I recommend you this solution

=INDEX(XlSort(XlSkip(XlAggRow($A$4:$A$6,$B$4:$B$6,$C$4:$C$6,$A$10:$A$12,$B$10:$B$12,$C$10:$C$12),""),,1),ROW(A1))

You need to download the plugin from here,

https://tkljh.s3.amazonaws.com/quant/tools/excel.html

David

 

[GraH - Guido]

It highly depends on the software policy applied in the company if installing such a plugin is allowed. Also if others are using the solution, thy need to installed the plugin as well. Not always advisable.
In case of home use those constraints are likely to be non-existing.
Personally I would go for a Power Query solution to avoid both the volatile/array and the plugin solution.

Some steps could be nested, but I prefer using separate steps.

let
  Source = Excel.Workbook(File.Contents("G:\Uploads\Copy of test_table.xlsm"), null, true),
  Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
  FilterOutNullAndTable = Table.SelectRows(#"Changed Type", each [Column1] <> null and not Text.StartsWith([Column1], "Table")),
  GroupAllinTable = Table.Group(FilterOutNullAndTable, {}, {{"All", each _, type table}}),
  MakeLists = Table.AddColumn(GroupAllinTable, "List", each Table.ToList([All])),
  ExpandList = Table.ExpandListColumn(MakeLists, "List"),
  SplitListAsList = Table.AddColumn(ExpandList, "ListSplit", each Text.Split([List], ",")),
  ExpandListSplitInRows = Table.ExpandListColumn(SplitListAsList, "ListSplit"),
  KeepListSplit = Table.SelectColumns(ExpandListSplitInRows,{"ListSplit"}),
  FilterOutBlanks = Table.SelectRows(KeepListSplit, each ([ListSplit] <> ""))
in
  FilterOutBlanks

 

[Haz]

Formula without using INDIRECT.

 

[GraH - Guido]

OFFSET is also volatile, but it might not be a big issue when it is well designed.
https://docs.microsoft.com/en-us/pr...erf_FindingPrioritizingCalculationBottlenecks

 

[Haz]

That's good to know.
You can change it to use INDEX pretty easily instead.

 

[bines53]

Alphabet,I did something simplistic, without volatile, but with array
{=IFERROR(MID("abcdefghijklmnopqrstuvwxyz",AGGREGATE(15,6,1/(1/SMALL(IFERROR(SEARCH($A$4:$C$12,"abcdefghijklmnopqrstuvwxyz")*(LEN($A$4:$C$12)=1),0),ROW($A$1:$A$30))),ROW(A1)),1),"")}

David