Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible data)?

Hello,

I want to merge these 2 dynamic ranges A2:B and D2:E of data, but skip rows, which do not show any visible value. The amount of rows with visible data will change from time to time in these ranges.
Can this be done automatically - only using formulas?

merge3.PNG

Which version of Excel are you using?

There are no blank rows in your sample data ...

So what? If you want a solution to a problem that includes blank rows, then you need to provide suitable sample data including blank rows!

Please answer my question in post #2. "Latest" is meaningless, as there are several versions that are current.

PQ solution:

1. Load each of the tables to connection only - change headers to Meal and Number.
2. Combine queries - append.
3. Filter out any blank rows in the Number column.
4. Close & load to worksheet.

Please update your forum profile with the correct version of Excel.

What do you mean by "many of similar columns"? How many? Even if you want a formula solution, you need to be more specific. Where will these data tables be?

The PQ is automatic once tables have been uploaded once - it's just a case of setting the queries to refresh.

Maybe 15 or 20 situations, where I will need this data manipulation. So any work by hand is not what I want. Work with hands also creates mistakes.

Now these problems occur:
1. If I write additional data outside range - it is not combined. I want the range to be dynamic till the very last row - A2:B and D2:E.
2. If I want to make range bigger, by adding rows - Excel warns, it want add more rows to the sheet. But there is plenty of rows in the sheet. In this case combined ranges would only take about 30 rows.
I need another way to do this if there is.
merge2.PNG


Who knows how to do this? It comes to my mind to get the index of the last row in range with visible data and then somehow to narrow range size.

I can see that you are a novice when it comes to PQ and have yet to understand its power (or, indeed, how it works).

Once you have loaded the columns to connection for the first time (set-up), there is nothing manual about it.

Attached is a full column version. I have added extra data to each source table and refreshed all (data ribbon - this bit can b automated easily by setting all queries to refresh once a minute or by adding a little bit of VBA code to do it for you whenever anything changes on the worksheet) - you will see that the new data has been appended with no issue. Try it yourself.

Why have you opened another thread on this? If this needs to work in Google Sheets, then you can't use PQ.

Yes I want it to work with Google Sheets. I thought the formulas will be the same, but I was wrong. There are more viewers in this section.

Thread moved to the correct section.

By doing this, you have wasted your time and my time. Next time, post in the correct section.

This thread is now in the right section and I've edited the title, but you will now need to wait for someone who uses Google Sheets to help you. I don't - sorry.