Google Sheets - Combine Multiple Dynamic Sheets with Different Structure into One Sheet

Question

I am trying to create a cash flow report which should summarize transactions made in several bank accounts. Every bank account has a separate tab and a different layout, these transactions are uploaded on a regular basis so the data is changing regularly.

I'd like to create a Pivot table, but in order to do so, I should combine all the tabs into one tab with a unified layout and also extract the tab (name with the bank account name). The "unified table" should consist of the following columns/headers - Date; Amount; Category; Reference; Tab (sheet) Name. The table should be sorted by date. Once I have the table prepared, I'll stream this data to a dynamic dashboard.

How would you recommend doing it?

Here is a link to the sample file - https://docs.google.com/spreadsheets/d/10dXvOIr4vcL6M2UARUxfi88QKDaBCREuW5ca4kOqpQE/edit?usp=sharing

Any help would be greatly appreciated, thanks

I tried to do it using query function but couldn't choose different columns from each sheet to be extracted since on every sheet Col# contains different data. I managed only to extract each column separately (date, amount, etc) but then, the columns received were not synched, so it contains errors.

Answer 1

Use vstack() and friends, like this:

=let( 
  get_, lambda(sheetName, data, 
    filter( 
      hstack(byrow(data, lambda(row, sheetName)), data), 
      len(choosecols(data, 1)) 
    ) 
  ), 
  account1, choosecols('Account 1'!A2:K, 1, 6, 11, 10), 
  account2, choosecols('Account 2'!A2:G, 2, 6, 7, 4), 
  account3, choosecols('Account 3'!A2:D, 1, 2, 4, 3), 
  data, vstack( 
    get_("Account 1", account1), 
    get_("Account 2", account2), 
    get_("Account 3", account3) 
  ),  
  vstack( 
    { "Account", "Date", "Amount", "Category", "Reference" }, 
    data 
  ) 
)

See the Solution tab, and let(), lambda(), vstack(), hstack(), choosecols() and filter().

Answer 2

Added one possible solution to your test sheet. Tab names needs to be manually updated in Column_A(as in screenshot) & the four headers (Date; Amount; Catagory; Reference) are automatically searched for in each tab and rearranged in final output

=let(Σ,C1:F1,
     reduce(tocol(,1),tocol(A:A,1),lambda(a,c,vstack(a,
           let(Ξ,rows(indirect(c&"!A:A")), Λ,indirect(c&"!A1:"&Ξ),
               Γ,hstack(choosecols(Λ,index(ifna(xmatch(Σ,chooserows(Λ,1)),26))),wrapcols(c,Ξ,c)),
                 filter(Γ,index(Γ,,1)<>"",index(Γ,,1)<>"Date"))))))