I have two tables with the same header name. From this table I want to combine the data into one into one table by matching the header names.

The matching header names are "Link", "Review Date", "Review Author", "Review Remarks".

The logic to combine data with these conditions that I catch like this:

Search for column positions with a combination of REGEXMATCH:

=ArrayFormula(IF(REGEXMATCH('Multiple Table'!A2:Z2,"Link|Review\sDate|Review\sAuthor|Review\sRemarks")=TRUE,COLUMN('Multiple Table'!A2:Z2),""))

Change the column into a range with ADDRESS formula :

=ArrayFormula(IF(REGEXMATCH('Multiple Table'!A2:Z2,"Link|Review\sDate|Review\sAuthor|Review\sRemarks")=TRUE,ADDRESS(2,COLUMN('Multiple Table'!A2:Z2)),""))

I want the result from point 2 are A2:D, F2:H

Then to combine the data just enter the range A2:D and F2:D into the QUERY formula.

For the third step, I'm still confused about the formulation.

So I used a formula that wasn't dynamic yet, like this:

= QUERY ({'Multiple Tables'! A2: D; 'Multiple Tables'! F2: H}, "SELECT Col1, Col2, Col3 WHERE Col1 <> ''")

Spreadsheet File.

  • Looking at your sheet, it looks like you already solved this. "I used a formula that wasn't dynamic yet". If not, I fail to understand what the question is. – marikamitsos Jun 17 at 2:53
  • I want use dynamic formula. Combine multiple table into one table by matching header name (without i put manually of range). – Lutfi Creativesys Jun 17 at 4:17
  • scenario like that : =QUERY({get range contains header name as "Link", "Review Date", "Review Author", "Review Remarks"},"select A, B, C, D WHERE A<>''") – Lutfi Creativesys Jun 17 at 4:22
  • Thank you for the edit. But. I still cannot understand it. Sorry. Is it the rows that you have as not correct and skip that you do not want? – marikamitsos Jun 17 at 10:56
  • no.the data in status column no need to combine it – Lutfi Creativesys Jun 17 at 11:43

={query(A2:E10,"select *");query(F3:J10,"select *",0)}

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.