Tuesday, August 6, 2013

Reverse (Column/Vertical) Fill

The Problem

Last weekend's email from Allen included a question from a reader about how to do a "reverse fill".  He wanted to put in a formula, referring to the bottom cell in a vertical (column) range, then drag/auto-fill, as he went down he wanted the reference to move up.
  • A1=M150
  • A2=M149
  • A3=M148
  • etc
Unfortunately, I didn't get my email out of the draft stage in time to be included, so all information is here.

NOTE:  The following Answers all go back to the original question, regarding referencing from one column to another, using relative rows to transfer the information.  The same principals apply to issues when transferring information from one set of columns to another set of columns or from a row to a column or from a column to a row.

Procedural Answer

If someone had to do this once and not worry about extending the data, then the data or formulas can be populated with the following steps

  1. Copy the data from the source, in this case, from M150 and up
  2. At A1, PasteSpecial…Values  if just the values are need, Paste Special...Link if the reference needs to be maintained.
  3. In B1 enter “=ROW()” the copy-down for all needed rows (or he can drag for the series)
  4. Copy B1:Bn and  PasteSpecial…Values at B1 (skip if the ROW() function was not used in step 3)
  5. Sort A1:Bn using B as the sort key
  6. Delete the data from B:B

Formula Answers

The Basic Formula

Many people prefer using formulas they can drag and drop or re-use in some fashion.  The key to this formula is to use the INDIRECT function and sync the offsets from top of the target and the bottom of the source.  My first cut at this was this, because I think more in the A1-mode than R1C1, then simply copied it down the target column
A1=INDIRECT("M"&150-(ROW()-ROW(A$1)))

NOTE ADDED:  Noticed in the published answer the "ROW(A$1)" was not included.  In this case, or any when the target starts at row 1 (or if adapting this to columns, the target starts in column A), the anchor is not needed.  However, in my opinion, it's a good habit to always include the anchor, just in case the target range changes thanks to a row/column insert or delete.

Greater Re-usability

Thinking further about re-usability of the function, a few iterations later I came up with the following, using R1C1 style:
  • Name the last cell of the source "BottomSourceCell"
  • Name the first target cell "FirstTargetCell"
  • FirstTargetCell=INDIRECT("R"&ROW(BottomSourceCell)-(ROW()-ROW(FirstTargetCell))&"C"&COLUMN(BottomSourceCell),FALSE)

Multiple Sources and Targets

Thinking a little more about it, it is possible that a single workbook would need this multiple times using different lists for each.  Suppose  a workbook had several lists of information (People, Organizations, Activities, Months, and Days) and the lists needed to be reversed for each.  There are a couple of options for solving this issue

Separate Lists, Different Sheets

This approach assumes each list should be on a separate worksheet.  This makes sense when there is additonal data along the row.
  • Put the lists on separate worksheets
  • Put the "BottomSourceCall" Name on on the bottom cell of each list, scoping it to the Worksheet.
  • Assuming that each list will be using in a separate worksheet, the formula looks like this
    =INDIRECT("People!R"&ROW(People!BottomSourceCell)-(ROW()-ROW(TopTargetRow))& "C"&COLUMN(People!BottomSourceCell),FALSE)
The first "People" reference makes certain you are pulling from the right list.  The other two incidents make certain the right bottom row is reference.

One Worksheet, All Lists

This approach assumes little additional data per row, or if there is additional data it is limited, or that the lists won't grow.  For example, the list may be the names Months of the years and additional data might be the abbreviation.  This is far different than the number of columns that might be appropriate for People, and that the list might grow or shrink.  Therefore, some lists might be housed on the same worksheet, perhaps Month information in the first 3 columns and Days of the Week in columns to the right of the Months.

  • Put the lists on a single worksheet, naming it something like "Refs" for reference lists.
  • Put the "BottomSourceCall" Name at the bottom cell of each list, with a meaning suffix, e.g. ."BottomSourceCall_Month"
  • The formula looks like this
    =INDIRECT("Refs!R"&ROW(BottomSourceCall_Month-(ROW()-ROW(TopTargetRow))& "C"&COLUMN(BottomSourceCall_Month),FALSE)
"Refs", like the first "People" above, makes certain the right worksheet is referenced but because their are two lists on this sheet, COLUMN( BottomSourceCall_Month)  makes certain the right list is referenced.

VBA Answers

Sub ReverseFill()
    Const DefaultSourceRangeName As String = "ReverseFillSource"
    Dim srcNm As Name
    Dim Start As Range
        Set Start = ActiveCell
    Dim Srce As Range
    Dim BottomCell As Range
    Dim i As Double
    Dim MsgReply As Integer
    
    For Each srcNm In ActiveWorkbook.Names
        If LCase(srcNm.Name) = LCase(DefaultSourceRangeName) Then
            'Set Srce = ThisWorkbook.Names(DefaultSourceRangeName).RefersToRange
            Set Srce = Range(ThisWorkbook.Names(DefaultSourceRangeName).RefersToLocal)
            Exit For
        End If
    Next srcNm
    If Srce Is Nothing Then
        Do
            InputRange = InputBox("Enter the source range by name or in A1 format")
            If StrPtr(InputRange) = 0 Then GoTo Exit_ReverseFill ' Checks for Cancel button
            For Each srcNm In ActiveWorkbook.Names
                If LCase(srcNm.Name) = LCase(InputRange) Then
'                    Set Srce = ThisWorkbook.Names(InputRange).RefersToLocal
                    Set Srce = Range(ThisWorkbook.Names(InputRange).RefersToLocal)
                    Exit For
                End If
            Next srcNm
            If Srce Is Nothing Then
                On Error Resume Next
                'Set Srce = InputRange.Parent.Name & "!" & ActiveSheet.Range(InputRange)
                Set Srce = Range(InputRange)
                On Error GoTo 0
            End If
            If Srce Is Nothing Then
                MsgReply = MsgBox("That is not a valid range in A1 form or a valid Named range in the workbook.  Try again?", vbYesNo)
                InputRange = ""
                If MsgReply = vbNo Then GoTo Exit_ReverseFill
            End If
        Loop Until Not Srce Is Nothing Or StrPtr(InputRange) = 0
    End If
    If Not Srce Is Nothing Then
        Set BottomCell = Srce.Cells(Srce.Rows.Count)
'        Set BottomCell = ActiveSheet.Cells(Srce.Rows.Count + Srce.Row - 1, Srce.Column)
        For i = 0 To Srce.Rows.Count - 1
            Start.Offset(i, 0).Formula = "=" & IIf(ActiveSheet.Name <> Srce.Parent.Name, Srce.Parent.Name & "!", "") & "R" & BottomCell.Row - i & "C" & Srce.Column
            'Start.Offset(i, 0) = BottomCell.Offset(-i, 0)
        Next i
    End If
Exit_ReverseFill:
    On Error GoTo 0

End Sub