Not FOO above FOO above FOO, but FOO beside FOO beside FOO

April 29, 2019 1:16 PM

I need to find it create an Excel macro that will take data from a table in regularly repeating groups of three rows and convert it into three side-by-side columns. How do I do that?

Essentially what I have is this:

Rye
Ham
Mustard

Ham
Turkey
Mayo

Wheat
Veggies
Hummus

And I need it to become:
Rye | Ham | Mustard
French | Turkey | Mayo
Wheat | Veggies | Hummus

It doesn't seem that complicated but I can't figure out what to even search for. What should I be searching for?

posted by DirtyOldTown to Computers & Internet (10 answers total)

If you highlight the data, choose copy and then paste special -> transpose, I think it'll get you what you want. I'm not sure if you can record that as a macro.
posted by Jacob G at 1:21 PM on April 29, 2019

You can do it with formulas, if the data is in Sheet 1, column A, you set three B1,C1,D1 to each of these in turn, and then pull them down to fill in those columns:

=OFFSET(Sheet1!$A$1,(ROW()-1)*3,0)

=OFFSET(Sheet1!$A$1,(ROW()-1)*3+1,0)

=OFFSET(Sheet1!$A$1,(ROW()-1)*3+2,0)
posted by BungaDunga at 1:26 PM on April 29, 2019

I hadn't thought of OFFSET. That might be just t/he way to go. That code sample you gave me produced an error though, so I am not sure I grok how to execute that.
posted by DirtyOldTown at 1:36 PM on April 29, 2019

Is this basically the same question as this other one from a few days ago? Some great ideas in there.
posted by brainmouse at 1:47 PM on April 29, 2019

That one is very nearly the exact opposite. They want to collapse columns into stacked rows. I want to separate stacked rows into columns. That said, it's so perfectly opposite that some of the tricks explained there might work. And I had not seen that one, so I will read it through. Thanks for pointing it out.
posted by DirtyOldTown at 2:50 PM on April 29, 2019

I don't think you need to use the offset formula. You can just put =A2 in cell B1, =A3 in cell C1 and then =A4 in cell D1. Now copy that down. If you can easily filter on just the first item in each group, then do that. If you can't, then you need another column that is A, B, C, A, B, C (again you should be able to paste it down the whole sheet) and just filter for the As. Of course, if you have a space between each, you need to include D in the list.
posted by soelo at 3:02 PM on April 29, 2019

Just a tip, a macro probably isn't best for this, often a second sheet with the formulas correct is more efficient. Or, a bunch of sells with the references correct you can just paste in.

So I'd probably get the first few correct and try to paste down the rest! Then copy the whole column from the first time you do it, and paste into future worksheets. It's not pretty, but it's often much faster.
posted by bbqturtle at 3:29 PM on April 29, 2019

Your data tab should be named "ColTab"; Data starts in row 1, repeats every 3
Results tab should be named "RowTab", Results start in row 1
Runs until it hits an empty cell in col 1


Sub ColToRows()
ColTabRow = 1
RowTabRow = 1
While Sheets("ColTab").Cells(ColTabRow, 1).Value <> ""
Sheets("RowTab").Cells(RowTabRow, 1).Value = Cells(ColTabRow, 1).Value
Sheets("RowTab").Cells(RowTabRow, 2).Value = Cells(ColTabRow + 1, 1).Value
Sheets("RowTab").Cells(RowTabRow, 3).Value = Cells(ColTabRow + 2, 1).Value
RowTabRow = RowTabRow + 1
ColTabRow = ColTabRow + 3 'add one or more if there are spacer rows in your data
Wend
End Sub

posted by achrise at 4:55 PM on April 29, 2019

Assuming your data is always in continuous blocks with one row blank between each block, and assuming one blank row above the first entry (i.e. Rye is in cell A2) then:

Cell B2:
=IF(A1=0, A2, "")

Cell C2:
=IF(A1=0, A3, "")

Cell D2:
=IF(A1=0, A4, "")

You can then handle drag down this first line of formulae and it'll give you your desired format next to the first entry of each block.

Then copy columns B, C and D into a new sheet and sort / filter to remove blank space.

(If blocks have a variable number of items going above 3, you could extend into column E and beyond, but you'd then need to nest the above inside another IF which would check columns to the left for a zero (returned by the blank row) and return zero if TRUE, the above IF formula setup if false. This would then leave you with a pattern of shorter block rows having several columns of zeros to the right, which could easily be cleaned up after copying / sorting or filtering).

On preview realised that this last was a very long winded way of explaining a simple change, so adding an example formula here; example would go in cell F2 if you needed to deal with blocks of up to 5 items: =IF(E2=0,0,(IF(A1=0,A6,"")))
posted by protorp at 11:57 AM on April 30, 2019

I'd do this using 'unpivot' in Power Query in Excel. The marvellous thing about Power Query is that you can save queries and refresh/rerun them. If this walkthough doesnt make sense then search for "unpivot power query" or 'excel power query' for introductions to the tool.

Power Query also crops up in Microsoft Power BI so you may come across walkthroughs for that but it is essentially the same tool baked into (...well hidden in) Excel 2016.
posted by Ness at 5:47 AM on May 3, 2019