Using If with Offset in a Formula

[HawaiianShirts]

I've got a report that produces a rather nice table with data categorized by the first column. However, to get it to work with another process as required by my employer, I need to get the data for each category into a single row. For example, if it was addresses categorized by state, the source data might look something like this:

State	Customer	Address	City	ZIP
California	Steve	505 Main	Sacramento	94208
California	Jenny	9811 Lincoln	San Francisco	94119
Oregon	Carl	3000 Sandy	Portland	97216
Oregon	Susan	65 Center	Salem	97301
Oregon	Larry	1010 State	Eugene	97404
Washington	Edward	777 Bridgeway	Seattle	98139
Washington	Anna	789 Bridgeway	Seattle	98139

<tbody>
</tbody>

When I try to get a separate worksheet to populate with this data in the required format, I would need three rows that look something like this:

State

Customer 1

Address 1

City 1

ZIP 1

Customer 2

Address 2

City 2

ZIP 2

Customer 3

Address 3

City 3

ZIP 3

California

Steve

505 Main

Sacramento

94208

Jenny

9811 Lincoln

San Francisco

94119

Oregon

Carl

3000 Sandy

Portland

97216

Susan

65 Center

Salem

97301

Larry

1010 State

Eugene

97404

Washington

Edward

777 Bridgeway

Seattle

98139

Anna

778 Bridgeway

Seattle

98139

<tbody>
</tbody>

The State column will change each time I need to collect this data, and each row will have a different state. If I have to, it would not pose a problem to have blank cells. For example, "Oregon" would still appear under the State column, but Carl, Susan, and Larry would appear under Customer 3, Customer 4, and Customer 5, and then "Washington" would also still appear under the State column, but Edward and Anna would appear under Customer 6 and Customer 7.

After referring to some other forum stuff and online guides, I managed to get an OFFSET formula to work, even if I don't totally understand it. Then I added an IF statement to the beginning of my OFFSET formula. This is the actual formula and does not refer to the example tables above.

=IF($A2=Sheet4!$A:$A,(OFFSET(Sheet4!$A$1,((ROW()-1)*1)+(FLOOR(COLUMN()-1,7)/7),(COLUMN()-1)-(FLOOR(COLUMN()-1,7)))),"")

The problem is that the IF part doesn't seem to work. The whole row populates with data from the source sheet no matter what's in the State column. If "State" shows Oregon, I get all the Oregon addresses, but I also get the Washington and California addresses.

What am I doing wrong? Is there an easier way?

 

[HawaiianShirts]

Well, I figured out what I'm doing wrong with the IF statement. By starting with it, I'm giving Excel an all-or-nothing kind of command. So, "If this cell shows this value, then show me the results of this whole block of instructions; if not, then don't show me anything."

I think I've found a horribly convoluted way to get what I want using three worksheets and cross-sheet references. Does anybody know of an easier way?

 

[Joe4]

I would probably opt to use VBA and loop through the data. Is that an option you are willing to consider?

 

[Joe4]

Here is the VBA code that I came up with. Note that you may need to change the SrcWs and DestWs variable assignments to match what your sheets are named.

Sub MyMacro()

    Dim SrcWs As Worksheet
    Dim DestWs As Worksheet
    Dim MyLastRow As Long
    Dim MyRow As Long
    Dim MyDestRow As Long
    Dim MyRecNum As Long
    Dim MyMaxRecNum As Long
    Dim MyDestCol As Long
    Dim MyHeaders As Long
    
'   Specify source worksheet
    Set SrcWs = Sheets("Sheet1")

'   Specify destination worksheet
    Set DestWs = Sheets("Sheet2")
    
    Application.ScreenUpdating = False
        
'   Find last row with data on source worksheet
    MyLastRow = SrcWs.Cells(Rows.Count, "A").End(xlUp).Row
    
    MyDestRow = 1
'   Loop through all data on source sheet
    For MyRow = 2 To MyLastRow
        SrcWs.Activate
'       Check for new state, and if not, increase MyRecNum count...
        If SrcWs.Cells(MyRow, "A") = SrcWs.Cells(MyRow - 1, "A") Then
            MyRecNum = MyRecNum + 1
'       ...otherwise increment MyDestRow, reset MyRecNum, and populate state on Dest sheet
        Else
            MyDestRow = MyDestRow + 1
            MyRecNum = 1
            DestWs.Cells(MyDestRow, "A") = SrcWs.Cells(MyRow, "A")
        End If
'       Determine destination column to paste to
        MyDestCol = (MyRecNum * 4) - 2
'       Copy data to Source
        SrcWs.Range(Cells(MyRow, "B"), Cells(MyRow, "E")).Copy
        DestWs.Activate
        DestWs.Cells(MyDestRow, MyDestCol).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Check for maximum record number
        If MyRecNum > MyMaxRecNum Then MyMaxRecNum = MyRecNum
    Next MyRow
    
'   Add headers for Dest sheet
    DestWs.Range("A1") = "State"
    For MyHeaders = 1 To MyMaxRecNum
        DestWs.Cells(1, (MyHeaders * 4) - 2) = "Customer " & MyHeaders
        DestWs.Cells(1, (MyHeaders * 4) - 1) = "Address " & MyHeaders
        DestWs.Cells(1, (MyHeaders * 4)) = "City " & MyHeaders
        DestWs.Cells(1, (MyHeaders * 4) + 1) = "Zip " & MyHeaders
    Next MyHeaders
        
    Application.ScreenUpdating = True
    
    MsgBox "Process complete!"
    
End Sub