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!

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!

maybe something like this?
check black table

Originally Posted by davesexcel

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.

Originally Posted by sandy666

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!

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)