Calc : Stack two or more Columns/Pivot

[Brijesh]

Hello dear community,

I am trying to stack two or more columns from pivot tables(columns/data).
I mean, data should appear from the first column until it is empty/null, then data from the other column should appear, just like a stack of two or more columns, change/refresh dynamically/automatically, in short print/export ready.
For sorting and filtering purpose I am using pivot tables.
Sample calc file is attached. Two sheets are there, first have data/pivot/columns and the second contains the desired result, which I want to done dynamically/automatically. Stacked columns may have min-40 to max-100 row height. When pivot table refreshed (change in height-rows) the second sheet (stacked column/s) should change accordingly.

(Declaration… This is just an example, I don’t want exact result, I need idea/formula.)
stack columns.ods (48.1 KB)

Thanks for your help!!

Reffered Topics till this post…

1. Calc: Way to join/merge multiple data ranges to one
2. Calc Question- How Merge Columns
3. Calc Question- How Merge Columns

Version: 25.2.2.2 (X86_64) / LibreOffice Community
Build ID: 7370d4be9e3cf6031a51beef54ff3bda878e3fac
CPU threads: 8; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win
Locale: en-IN (en_US); UI: en-US
Calc: CL threaded

[fpy]

@Brijesh

community

@Villeroy surprisingly missed an opportunity to promote a move from spreadsheets to a proper database ?

e.g. Copying a Functional Pivot Table to New Workbook

[Villeroy]

@fpy

@Villeroy surprisingly missed an opportunity to promote a move from spreadsheets to a proper database ?

I’m getting tired of it.

[Lupp]

@Brijesh

I am trying to stack two or more columns from …

Can you explain for what reason you think this makes sense?

@Brijesh

I need idea …

Something like … ?

Function reArrangeArray(pArray, pReadCols As Boolean, pWriteCols As Boolean, pTrows As Long, pTcols As Long)
REM V 0.0 of 2022-01-11 by Wolfgang Jäger disregarding older sketches
REM This implementation puts the concept of memory mapping over efficiency considerations.
REM For calls from Calc an omitted parameter will have its type-conforming default value. 
If (pTrows<0) OR (pTcols<0) OR (pTrows+pTcols<=0) Then Exit Function
Dim numCells As Long, _
    lC As Long, uC As Long, lR As Long, uR As Long, rs As Long, cs As Long, _
    x  As Long, y  As Long, r  As Long, c  As Long, n  As Long
lR = Lbound(pArray, 1) : uR = Ubound(pArray, 1) : rs = uR - lR + 1
lC = Lbound(pArray, 2) : uC = Ubound(pArray, 2) : cs = UC - lC + 1
numCells = cs * rs
If pTrows=0 Then pTrows = numCells \ pTcols + IIf((numCells MOD pTcols)>0, 1, 0)
If pTcols=0 Then pTcols = numCells \ pTrows + IIf((numCells MOD pTrows)>0, 1, 0)
Dim res(1 To pTrows, 1 To pTcols)
On Local Error Goto resFilled REM The onmly possible reason should now be "out of res space".
If pReadCols Then
  n = -1
  For c = lC To uC
    For r = lR To uR
      n = n + 1
      If pWriteCols Then
        x = n \ pTrows   + 1
        y = n Mod pTrows + 1
      Else
        y = n \ pTcols   + 1
        x = n Mod pTcols + 1
      End If
      res(y, x) = pArray(r, c)
    Next r
  Next c
Else
  n = - 1
  For r = lR To uR
    For c = lC To uC
      n = n + 1
      If pWriteCols Then
        x = n \ pTrows   + 1
        y = n Mod pTrows + 1
      Else
        y = n \ pTcols   + 1
        x = n Mod pTcols + 1
      End If
      res(y, x) = pArray(r, c)
    Next c
  Next r
EndIf
resFilled:
reArrangeArray = res 
End Function

[Brijesh]

Hello @Lupp

@Lupp

I am trying to stack two or more columns from …
Can you explain for what reason you think this makes sense?

@Brijesh

in short print/export ready.

I want a column which sort some data dynamically so that it will be a print ready sheet. e.g. all students whose result is “pass” will be there on the top and then “pass+” and then “pass^” and “fail” are at the bottom.
So, this sheet works flawlessly for multiple grade/standard/division.

@Lupp

Something like … ?

I need sometime to check wether it is working for me or not… thanks!!
I am going to try SORT, SORTBY and FILTER/OFFSET, I got success on some level. I will reply soon!

[EarnestAl]

Something like this? It uses some random fake names and marks but relies on a helper column to order the names of the pass/fail levels
StackedChartPivotTable.ods (35.3 KB)

[Brijesh]

Thanks @EarnestAl
I learned that array formulas are real-time dynamic/live, pivot tables are not. Furthermore copy/pasting pivot is hard/impossible (afaik).

[Brijesh]

Gooooaaaaalll!!!
Here is, how I successfully achieved the dynamic/live stack/column and thus a dynamic/live/auto sorted table.
Pivot table is omitted because it is not real-time dynamic/live/auto, it required to be refreshed every time after a change in the data.

stack columns ver 2.ods (43.3 KB)

The ‘raw data’ have ‘rollno’ (unique value for each row). So “rollno” was sorted & filtered individually, according to the sorting criteria - ‘result’ (pass, pass+, pass*, fail, left). And then using IFS formula, made a live/dynamic/auto sorted stack/column.
Using that, a table which is ‘live/dynamic/auto sorted’ created.

If there is a change in result it will be auto sorted accordingly. (Try changing result value - pass, pass+, pass*, fail, left)

[Just for more clarity… Result may have 5 values. 1. pass 2. pass+ (promoted by percentage) 3. pass* (promoted by percentage and/or grace) 4.fail 5. left (transferred from the class)]