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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]State
[/TD]
[TD="align: center"]Customer
[/TD]
[TD="align: center"]Address
[/TD]
[TD="align: center"]City
[/TD]
[TD="align: center"]ZIP
[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Steve[/TD]
[TD]505 Main[/TD]
[TD]Sacramento[/TD]
[TD]94208[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Jenny[/TD]
[TD]9811 Lincoln[/TD]
[TD]San Francisco[/TD]
[TD]94119[/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD]Carl[/TD]
[TD]3000 Sandy[/TD]
[TD]Portland[/TD]
[TD]97216[/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD]Susan[/TD]
[TD]65 Center[/TD]
[TD]Salem[/TD]
[TD]97301[/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD]Larry[/TD]
[TD]1010 State[/TD]
[TD]Eugene[/TD]
[TD]97404[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Edward[/TD]
[TD]777 Bridgeway[/TD]
[TD]Seattle[/TD]
[TD]98139[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Anna[/TD]
[TD]789 Bridgeway[/TD]
[TD]Seattle[/TD]
[TD]98139[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]State
[/TD]
[TD="align: center"]Customer 1
[/TD]
[TD="align: center"]Address 1
[/TD]
[TD="align: center"]City 1
[/TD]
[TD="align: center"]ZIP 1
[/TD]
[TD="align: center"]Customer 2
[/TD]
[TD="align: center"]Address 2
[/TD]
[TD="align: center"]City 2
[/TD]
[TD="align: center"]ZIP 2
[/TD]
[TD="align: center"]Customer 3
[/TD]
[TD="align: center"]Address 3
[/TD]
[TD="align: center"]City 3
[/TD]
[TD="align: center"]ZIP 3
[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Steve[/TD]
[TD]505 Main[/TD]
[TD]Sacramento[/TD]
[TD]94208[/TD]
[TD]Jenny[/TD]
[TD]9811 Lincoln[/TD]
[TD]San Francisco[/TD]
[TD]94119[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD]Carl[/TD]
[TD]3000 Sandy[/TD]
[TD]Portland[/TD]
[TD]97216[/TD]
[TD]Susan[/TD]
[TD]65 Center[/TD]
[TD]Salem[/TD]
[TD]97301[/TD]
[TD]Larry[/TD]
[TD]1010 State[/TD]
[TD]Eugene[/TD]
[TD]97404[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Edward[/TD]
[TD]777 Bridgeway[/TD]
[TD]Seattle[/TD]
[TD]98139[/TD]
[TD]Anna[/TD]
[TD]778 Bridgeway[/TD]
[TD]Seattle[/TD]
[TD]98139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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