Google sheets: append data from 2 tabs, with different column order-CodePudding

I have 2 tabs named S1 and S2. They both contain 3 columns of data (A, B and C). I just want to merge their content in a 3rd tab, using functions. Issue is that the order of columns is different in S1 and S2. S1 S2

S1 | Column A | Column B | Column C | | -------- | -------- | -------- | | 1 | A | DeptA | | 2 | B | DeptB | | 3 | C | DeptC |

S2 | Column A | Column B | Column C | | -------- | -------- | -------- | | 4 | DeptD | D | | 5 | DeptE | E | | 6 | DeptF | F |

What I want to get in a 3rd tab is: | Column A | Column B | Column C | | -------- | -------- | -------- | | 1 | A | DeptA | | 2 | B | DeptB | | 3 | C | DeptC | | 4 | D | DeptD | | 5 | E | DeptE | | 6 | F | DeptF |

I'm using the following formula: "query({'S1'!A1:C;'S2'!A2:A,'S2'!C2:C,'S2'!B2:B};"Select Col1, Col2,Col3 where Col1 is not null";1)". But I get a formula analysis error. I have also tried "

={
     query({S1!A1:C},"Select Col1,Col2,Col3");
         query({S2!A2:C},"Select Col1,Col3,Col2")
}

" But I also get a formula analysis error

Spreadsheet access: https://docs.google.com/spreadsheets/d/1FdaRSANfcqMkSBE-is8ek8bFWp80vVW8P3a-v0q4MwU/edit?usp=share_link

Thanks for your help

CodePudding user response：

Depending on locale setting its either:

=query({{'S1'!A1:C};{'S2'!A2:A\'S2'!C2:C\'S2'!B2:B}};"Select Col1, Col2, Col3 where Col1 is not null")

=query({{'S1'!A1:C};{'S2'!A2:A,'S2'!C2:C,'S2'!B2:B}},"Select Col1, Col2, Col3 where Col1 is not null")

CodePudding user response：

The issue is in your Locale Settings. You should use semi-colons instead of commas:

={
     query({'S1'!A1:C};"Select Col1,Col2,Col3");
         query({'S2'!A2:C};"Select Col1,Col3,Col2")
}