How to Append Tables with Different Column Order with Automatic Refresh

Your process sounds very manual, so I am imaging that somewhere in your wookbook you have the four "source" tables that you update periodically and one combined table somewhere in a worksheet.  What you need is a programmatic way to combine them on a sheet.

The basic idea is that you need to make the columns be able to be stacked properly.  I have no idea how many columns you have, but regardless, you need to form a "mapping" between what you have and what you want.  If your columns are always fixed, it is just the row data that changes, the problem is fairly simple - just directly map things like:

vstack(choosecols(table1, 3), choosecols(table2,6), choosecols(table3,4), choosecols(table4,2)).

In this way, you'll get a single column with each of your source table's related data included.  

A second choice is to take advantage of standardizing names.  For instance, you can add a row above your source tables where you write down the standard name of each column.  You then use a match based approach to put the columns into a vstack, e.g.

"purple", "green", "blue", "red"   for table 1

"apple","plum","blueberry" for table 2

you create a mapping for table 2, say 

"red","purple","blue"

then you get the proper column using xlookup:

xlookup("red",table_2_mapping,table_2)

So now you can just stack:

vstack(

  xlookup("red",table_1_headers,table_1)

  xlookup("red",table_2_mapping, table2)

 etc.

)

Obviously the more involved your column names, the more complicated the mapping.  Well structured data and consistent naming will go a long way to making your situation easier.  

This process is very manual indeed.

The table names are all the same, just for whatever reason the system that spits them out changes the order of the columns

Hi lankyman22 

Tables all appended with the correct columns all lining up

No problem with Power Query/Get & Transform. Resulting table columns order depends on the order of the 1st table

Automatically refresh when new data is pasted into the data tables

The resulting query table can auto-refresh every 1 minute (and/or on workbook opening), not as you enter/paste new data - this would require VBA (not my expertise) to "detect" a change on any of your tables and to refresh the query on your behalf

lankyman22 

Provided you run 365 a LAMBDA based option is attached:

You define the order in which your table columns should be appended + the name of your tables (first 2 lines of the following LET expression):

=LET(
  expected_cols_order,  {"A","B","C","D"},
  input_tables,         {"Table1","Table2","Table3","Table4"},
  stack_table,
    LAMBDA(seed,table_name,
      LET(
        table,  INDIRECT( table_name & "[#All]" ),
        VSTACK(seed,
               CHOOSECOLS( DROP( table, 1 ), SORT( XMATCH( CHOOSEROWS( table, 1 ), expected_cols_order ),,, TRUE ) )
        )
      )
  ),
  stacked_tables, REDUCE(expected_cols_order, input_tables, stack_table ),
  IF( ISBLANK( stacked_tables ), "", stacked_tables )
)

(this can be put as a Named LAMBDA in the Name Manager instead of the LET expression on the sheet)