How can I programmatically put my data into grid in excel?

Question

I have a set of data in an excel file, for instance:

RIH2329
SCC272
CRT041
WS13-14
WS13-16
RIH2602
VT001
CRT035
RIH2874
RIH2876
CRT067
RIH2744
RIH2750
RIH2368
RIH2374
RIH2378
RIH2376
RIH2373...etc

I want to put this data into a 9x9 grid. Like this:

RIH2329 SCC272 CRT041 WS13-14 WS13-16 RIH2602 VT001 CRT035 RIH2874
RIH2876 CRT067 RIH2744 RIH2750 RIH2368 RIH2374 RIH2378 RIH2376 RIH2373...etc

I have lots of data so its impossible to do by hand. I've tried using =offset to get the data where I want, but this is untenable because I would need to alter each line of code. Is there an easy way to do accomplish what I want without editing every line?

Answer 1

You can achieve that with this VBA macro code.

Sub makeDataGrid()

Range("A1").Activate 'asumes data starts at this cel
'Put all data into memory down to the bottom assumes there are no blank cells in between
data = Range(Selection, Selection.End(xlDown)).Value
'Delete column containing all data
Range(Selection, Selection.End(xlDown)).Delete

colOffset = 0
rowOffset = 0
For i = 1 To UBound(data)
    ActiveCell.Offset(rowOffset, colOffset).Value = data(i, 1)
    colOffset = colOffset + 1
    If colOffset = 9 Then
        colOffset = 0
        rowOffset = rowOffset + 1
    End If
Next i

End Sub

The macro will remove the column containing all the data after it puts it in an array and then create the grid.

Answer 2

With RIH2329 in A1, put this in B1:

=OFFSET($A$1, MOD(COLUMN(A:A)-1, 9)+(ROW(1:1)-1)*9, 0)

Fill right to J1 then fill B1:J1 down to B9:J9.