+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    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
    Attached Files Attached Files
    Last edited by Romkeris; 06-21-2020 at 09:08 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: How to merge ranges ignoring blank (but not empty-they have formulas) rows?

    Which version of Excel are you using?

    There are no blank rows in your sample data ...
    Last edited by AliGW; 06-21-2020 at 06:00 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: How to merge ranges ignoring blank (but not empty-they have formulas) rows?

    Latest Excel. B and E column cells have formulas inside.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: How to merge ranges ignoring blank (but not empty-they have formulas) rows?

    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.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: How to merge ranges skipping rows without any visible data?

    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: How to merge ranges ignoring blank (but not empty-they have formulas) rows?

    Sorry, not latest exactly - Excel 2016. And I also corrected thread name.

  7. #7
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: How to merge ranges skipping rows without any visible data?

    Thanks, but can this data manipulation be done automatically - only using formulas. Because I will have many of similar columns and their size will change frequently.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: How to merge dynamic ranges automatically (skipping rows without any visible data)?

    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.

  9. #9
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: How to merge dynamic ranges automatically (skipping rows without any visible data)?

    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.
    Last edited by Romkeris; 06-21-2020 at 07:38 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: How to merge dynamic ranges automatically (skipping rows without any visible data)?

    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.
    Attached Files Attached Files
    Last edited by AliGW; 06-21-2020 at 08:54 AM.

  11. #11
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: How to merge dynamic ranges automatically (skipping rows without any visible data)?

    Yes I hear for the first time about Power Query. Thank you. Now I have to figure out this, to make it myself. I see it uses different principle, because I cannot find any formula.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: How to merge dynamic ranges automatically (skipping rows without any visible data)?

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

  13. #13
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: How to merge dynamic ranges automatically (skipping rows without any visible data)?

    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.
    Last edited by Romkeris; 06-21-2020 at 09:16 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible dat

    Thread moved to the correct section.

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

  15. #15
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    68

    Re: Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible dat

    Sorry, for wasted time. And thanks for helping. I wasted my time also, but now I know it can be done with Power Query. But I need this to be done in Google Sheets. OK, next time I will post to the correct section.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible dat

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to merge dynamic ranges automatically (skipping rows without any visible data)GoogleD?
    By Romkeris in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 06-21-2020, 09:19 AM
  2. Automatically import data from Google Sheets to Excel
    By samvince in forum Excel General
    Replies: 3
    Last Post: 05-12-2020, 03:53 PM
  3. RANK/automatically put my data in highest number order (google sheets)
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-28-2020, 01:19 PM
  4. [SOLVED] Problem with setting up dynamic ranges skipping blanks
    By lynnsong986 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-10-2020, 12:27 PM
  5. [SOLVED] Consolidate data from multiple sheets from specific ranges that can be in dynamic length
    By mujahidreza in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-11-2019, 04:31 AM
  6. [SOLVED] Are named ranges not automatically visible to VBA?
    By Sidewinder72 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2018, 06:20 AM
  7. [SOLVED] vba to copy active visible sheets only to new workbook but ranges are dynamic
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2017, 12:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1