0

Please, Let me explain my question starting off with an example, for don't know how else to put this.

I have a set of data like this

Subject    Measure1
----------
Person1    1
Person1    2
Person1    3
----------
Person2    1
Person2    2
----------
Person3    2

And I need to fill some data so it looks like this:

Subject    Measure1
----------
Person1    1
Person1    2
Person1    3
----------
Person2    1
Person2    2
Person2    <null>
----------
Person3    <null>
Person3    2
Person3    <null>

Where null is just an empty cell, and the inserted data should keep the present records in the proper order it would be if the other rows where there (like in person 3 example) In other words, I need to have always 3 lines per Subject even if the Measure column is empty. I cannot do this manually due to a very large amount of data that is to be treated.

How do I do this in Excel? I tried a lot of fill missing data approaches, but it works for existing cells with no data. What I need is to insert rows following a rule.

Edit: formatting

Daniel Gotz
  • 1
  • 1
  • I would write some VBA code to do it. Go ahead and try it yourself and if you run into trouble come back to edit your question and include your own effort along with any error messages you have encountered. –  Sep 16 '17 at 21:03
  • Thanks for your comment. Sadly, I don't know any VBA. Any suggestions where could I begin with? – Daniel Gotz Sep 16 '17 at 21:05
  • if you have Power Query, you can Pivot and then Unpivot the data http://excelunplugged.com/2014/09/30/the-pivot-option-in-power-query/. There are other ways to Pivot an Unpivot without Power Query, but it's more complicated https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal/20543651#20543651 – Slai Sep 16 '17 at 21:35
  • @Slai, thanks, I'll try this when I get a Windows OS. I'm on a Mac and we don't have Power Query... :( – Daniel Gotz Sep 16 '17 at 21:42
  • you can generate the first column with a combination of copy, Remove Duplicates, copy below x 2, and sort. Then the second column with vlookup(), row(), and mod() functions – Slai Sep 16 '17 at 22:26
  • @Slai, I got the vlookup, but didn't understand how to use row and mod with it. I managed to distribute the data in the pivot table and got something like this: https://www.dropbox.com/s/h3z4mh7m5vdxzvd/Screen%20Shot%202017-09-16%20at%2021.54.21.png?dl=0 Is it easier to go from here? – Daniel Gotz Sep 17 '17 at 00:56
  • looks great. Easiest is to copy paste the person column 3 times, and then copy paste each of the 3 columns below each other (unless you can unpivot on Mac https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal/20543651#20543651) – Slai Sep 17 '17 at 01:00
  • 1
    Yes, I managed to unpivot, in my wife's mac that's running Office 2011... My Office 2016 doesn't seem to have a pivot table wizard! Thanks for the directions. – Daniel Gotz Sep 17 '17 at 01:29

1 Answers1

0

I have followed the directions Slai commented on, and managed to reorder my data. I had a little bit of manual work because my MS Office version wasn't compatible with the suggested tools.

Daniel Gotz
  • 1
  • 1