Rearrange data in a column

Question

Is there any way to automatically arrange this data

Into this

Using excel/google sheets/etc. Basically I have a huge list of files (second column) that I need to map to it's respective folder (first column ID).

What I need, is to copy column A data down, but only to the blank cells immediately below, and then do it again for the new folder id, and so on.

Exactly @MrSmith, I need to replicate that data into the cells below, but only to the empty ones, since there will be another folder ID — Jose, Oct 09 '15 at 19:30

score 1 · Accepted Answer · answered Oct 09 '15 at 18:59

I happen to have a macro that prompts the user which column to copy data down. See the below (Note you may need to tweak as necessary):

Sub GEN_USE_Copy_Data_Down()
Dim screenRefresh$, runAgain$
Dim lastRow&, newLastRow&
Dim c       As Range
Dim LastRowCounter$
Dim columnArray() As String

screenRefresh = MsgBox("Turn OFF screen updating while macro runs?", vbYesNo)
If screenRefresh = vbYes Then
    Application.ScreenUpdating = False
Else
    Application.ScreenUpdating = True
End If


Dim EffectiveDateCol As Integer
LastRowCounter = InputBox("What column has the most data (this info will be used to find the last used row")

CopyAgain:
With ActiveSheet
    lastRow = .UsedRange.Rows.Count
End With


' THIS WILL ASK THE USER TO SELECT THE COLUMN TO COPY DATA DOWN
MsgBox ("Now, you will choose a column, and that column's data will be pasted in the range" & vbCrLf & "below the current cell, to the next full cell")
Dim Column2Copy As String
Column2Copy = InputBox("What columns (A,B,C, etc.) would you like to copy the data of?  Use SPACES, to separate columns")
columnArray() = Split(Column2Copy)

Dim startCell As Range


For i = LBound(columnArray) To UBound(columnArray)
    Debug.Print i
    Column2Copy = columnArray(i)

    Set startCell = Cells(1, Column2Copy).End(xlDown)
    Do While startCell.row < lastRow
        If startCell.End(xlDown).Offset(-1, 0).row > lastRow Then
            newLastRow = lastRow
        Else
            newLastRow = startCell.End(xlDown).Offset(-1, 0).row
        End If
        Set CopyFrom = startCell
        Range(Cells(startCell.row, Column2Copy), Cells(newLastRow, Column2Copy)).Value = CopyFrom.Value
        Set startCell = startCell.End(xlDown)
    Loop
Next i

If screenRefresh = vbYes Then
    Application.ScreenUpdating = True
Else
    Application.ScreenUpdating = True
End If


End Sub

I wrote it a while ago, so it might be able to have lines removed/combined, but it should work (assuming you're trying to just copy data down column A).

Hi BruceWayne, thanks for the reply, sorry to bother you. I just updated the question, I let some important information out and wanted to double check if your answer still applies (I haven't got the chance to test it yet). — Jose, Oct 09 '15 at 20:11

score 0 · Answer 2 · answered Oct 09 '15 at 22:15

0

In Excel, select the left-hand column, HOME > Editing, Find & Select, Go to Special..., check Blanks (only), OK, then select one of the chosen cells, =, Up, Ctl+Enter.

answered Oct 09 '15 at 22:15

pnuts

58,317
11
87
139

Rearrange data in a column

2 Answers2