+ Reply to Thread
Results 1 to 9 of 9

Merging several rows into one filling empty spaces

  1. #1
    Registered User
    Join Date
    12-20-2017
    Location
    Ciudad del Carmen, Campeche, México
    MS-Off Ver
    2010
    Posts
    4

    Merging several rows into one filling empty spaces

    Hi guys

    Is the following possible?


    Imagine I have the following table:

    NAME JAN18 FEB18 MAR18 APR18
    John 5 (empty cell) (empty cell) (empty cell)
    John (empty cell) 3 (empty cell) (empty cell)
    John (empty cell) (empty cell) 7 (empty cell)
    John (empty cell) (empty cell) (empty cell) 10

    And I want to ge this:


    NAME JAN18 FEB18 MAR18 APR18
    John 5 3 7 10

    I want to be able to do this not using the cut-paste method because my database is ridiculously large. I'm attaching a screenshot to make things clearer.

    Thank you very much for the support!
    Attached Images Attached Images

  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
    47,610

    Re: Merging several rows into one filling empty spaces

    So will there be one value per person in each column and will these values always be on different rows, exactly as illustrated? Somehow I think it's not going to be quite as simple as your example suggests ...

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    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
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,879

    Re: Merging several rows into one filling empty spaces

    Here's a practice.
    Select all the cells in the range.
    Hit F5 key and select "Special...."
    select the Blanks option and click OK
    Right click on one of the selected blank cells and select "Delete" then "Shift Cells Up"

  4. #4
    Registered User
    Join Date
    12-20-2017
    Location
    Ciudad del Carmen, Campeche, México
    MS-Off Ver
    2010
    Posts
    4

    Re: Merging several rows into one filling empty spaces

    Hi AlicGW and davesexcel

    Thank your very much for your input.

    1. I'll be attaching a representative spreadsheet, as AlicGW suggested.
    2. The pattern that I see is that each person (e.g. John or Katy) has N rows. Each of his/her rows has one single value only. These values will always have different columns.

    3. If I do what davesexcel suggested I end up with an odd-looking table as there are other people below John (e.g. Maria, Katy and Mark) and shifting cells up will affect their rows too.

    thank you very much for your patience and willingness to help.
    Regards from Mexico!
    Attached Files Attached Files

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,879

    Re: Merging several rows into one filling empty spaces

    Well of course, your original sample only show one name

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Merging several rows into one filling empty spaces

    maybe something like this?
    check black table
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Registered User
    Join Date
    12-20-2017
    Location
    Ciudad del Carmen, Campeche, México
    MS-Off Ver
    2010
    Posts
    4

    Re: Merging several rows into one filling empty spaces

    Quote Originally Posted by davesexcel View Post
    Well of course, your original sample only show one name
    Hi davesexcel

    Yes, buddy. My mistake. I forgot to mention there are other names in the table. I hope there is a way to achieve it.

    Sorry again and thank you for your valuable contribution.

  8. #8
    Registered User
    Join Date
    12-20-2017
    Location
    Ciudad del Carmen, Campeche, México
    MS-Off Ver
    2010
    Posts
    4

    Re: Merging several rows into one filling empty spaces

    Quote Originally Posted by sandy666 View Post
    maybe something like this?
    check black table
    Hi sandy666

    I went through your attached file but couldn't understand exactly what and how you did it.
    To clarify things, the excel file I originally uploaded contains 3 tabs. The "what I have" and "what I wanna get" tabs show the point I'm trying to make in this thread. Please feel free to submit your ideas. Your questions are welcome too.

    Regards!

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Merging several rows into one filling empty spaces

    I did it via PowerQuery (free add-in from MS site for Ex 2010/2013 - Ex2016 and above - PowerQuery is built-in)
    whole action is in background so if you want to see how it works and see steps download and install this add-in.
    there is no any formulas or VBA, just defined user action(s) like eg. this:
    Please Login or Register  to view this content.
    here it was: Group by... and Sum, nothing more.

    here is example for mentioned two sheets
    (orange table)

+ 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. Black filling of cells when merging
    By Maggeddie in forum Excel General
    Replies: 2
    Last Post: 10-29-2016, 09:33 PM
  2. [SOLVED] filling in blank spaces
    By chr1stoper1 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-25-2015, 05:41 AM
  3. Replies: 1
    Last Post: 05-26-2014, 01:31 PM
  4. Filling a word blank spaces as i enter in excel sheet
    By mohamedazmeen75 in forum Excel General
    Replies: 1
    Last Post: 12-22-2013, 01:01 AM
  5. Replies: 2
    Last Post: 01-06-2013, 05:50 AM
  6. VB script for conditional merging & filling of some cells in a row
    By rprasad68 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2009, 01:14 PM
  7. Filling in names into spaces below
    By eonizuka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2009, 01:28 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