Return all billing codes billed per customer visit

[JamesonMH]

Hi folks,

I need a formula column to aggregate the codes billed per customer visit (solution would look like E2:E10 below). Then later I can include the results in a PivotTable to see the combo frequency of codes billed at this store.

There is a large list of customer transactions. Each row includes a specific 4 character billing code that the customer was charged. Some customers may have many rows for a single visit (e.g. 6 or 7 distinct billing codes). Other customers may have just 1 row.

I've tried variations with FREQUENCY, SIGN and nested IF's with no luck. I'm sure there's a VBA solution using a For each loop, but I'm not there yet to figure out.

I've already prepped the data; it's filtered by customer name and a helper column is included to mark a "1" when it's a distinct customer visit (i.e. subsequent rows immediately below would be blank if it's the same customer/same day).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer[/TD]
[TD]Date[/TD]
[TD]Distinct customer visit? (yes = 1)[/TD]
[TD]Code billed[/TD]
[TD]Codes billed per customer visit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]Sep 01[/TD]
[TD]1[/TD]
[TD]499J[/TD]
[TD]499J, 550M[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]Sep 01[/TD]
[TD][/TD]
[TD]550M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DEF[/TD]
[TD]Sep 02[/TD]
[TD]1[/TD]
[TD]425H[/TD]
[TD]425H[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]DEF[/TD]
[TD]Sep 05[/TD]
[TD]1[/TD]
[TD]690B[/TD]
[TD]690B, 701T, 499J, 880Q[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]DEF[/TD]
[TD]Sep 05[/TD]
[TD][/TD]
[TD]701T[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]DEF[/TD]
[TD]Sep 05[/TD]
[TD][/TD]
[TD]499J[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]DEF[/TD]
[TD]Sep 05[/TD]
[TD][/TD]
[TD]880Q[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]GHH[/TD]
[TD]Sep 08[/TD]
[TD]1[/TD]
[TD]701T[/TD]
[TD]701T, 499J[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]GHH[/TD]
[TD]Sep 08[/TD]
[TD][/TD]
[TD]499J[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Many thanks for reading through and your time!

James

 

[DRSteele]

You will need Excel 365 for function TEXTJOIN to work here. Put this in E2 and copy down.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=TEXTJOIN(", ",,IF(C2,IF((A2=$A$2:$A$10)*($B$2:$B$10=B2),$D$2:$D$10,""),""))[/FONT]

 

[JamesonMH]

You will need Excel 365 for function TEXTJOIN to work here. Put this in E2 and copy down.

=TEXTJOIN(", ",,IF(C2,IF((A2=$A$2:$A$10)*($B$2:$B$10=B2),$D$2:$D$10,""),""))

Thanks DRSteele for your reply! I have Office 365 at work (not an insider though) so will try this out in the AM and post back.

If you know of an alternative solution via VBA or other formula I'd welcome also (just worried about colleagues who use the file that aren't on 365).

Cheers,
James

 

[JamesonMH]

You will need Excel 365 for function TEXTJOIN to work here. Put this in E2 and copy down.

=TEXTJOIN(", ",,IF(C2,IF((A2=$A$2:$A$10)*($B$2:$B$10=B2),$D$2:$D$10,""),""))

Hi DRSteele. I just tried this formula out but it's not working.

For those rows containing a "1" in column C, the formula's returning all the codes below to the very bottom of the data range.

It should only pull the codes up to (but not including) the row containing the next "1", since that marks a separate customer/visit.

 

[Eric W]

Try this in E2 and drag down:

=IF(A2<>"",IF(OR(A2<>A1,C2=1),TEXTJOIN(",",TRUE,D2:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(D3:D12)/SIGN((A3:A12<>A2)+(C3:C12=1)),1),ROW(A3))-1)),""),"")

No CSE necessary. Also note the ranges in red. I search the next 10 rows after the current row for either a new customer or a 1. If a distinct customer visit could exceed 10 rows, you might need to increase the 12 to a larger number.

 

[JamesonMH]

Try this in E2 and drag down:

=IF(A2<>"",IF(OR(A2<>A1,C2=1),TEXTJOIN(",",TRUE,D2:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(D3:D12)/SIGN((A3:A12<>A2)+(C3:C12=1)),1),ROW(A3))-1)),""),"")

No CSE necessary. Also note the ranges in red. I search the next 10 rows after the current row for either a new customer or a 1. If a distinct customer visit could exceed 10 rows, you might need to increase the 12 to a larger number.

Works perfect and populated all 77K table rows in a flash, thanks Eric! I haven't used this new TEXTJOIN function beyond here. If it weren't for TEXTJOIN would you have had to resort to VBA for a solution?

And just out of curiosity how long did it take you to create this solution? I ask b/c I'd like to have a gauge for myself as to where I'm at when I write my own long (albeit less complex) formulas.

 

[Eric W]

Yes, without TEXTJOIN this would require VBA. Or possibly, depending on your sheet layout, and assuming no more than 10 rows per visit, we could add 10 helper columns and do it that way. Pretty cumbersome though.

The basic idea of the formula I've seen/used many times before. It took a minute or two to come up with the idea for the D2:INDEX(... construct. It probably took 10-15 minutes to debug the AGGREGATE portion of it. I was getting some screwy results for a while. I finally recognized the need for the SIGN function, as well as the IFERROR function.

And after I get it working, I sometimes consciously/unconsciously think about it some more, and sometimes come up with a better option, like:

=IF(A2<>"",IF(OR(A2<>A1,C2=1),TEXTJOIN(",",TRUE,D2:INDEX(D:D,IFERROR(MATCH(1,C3:C12,0),10)+ROW()-1)),""),"")

Shorter, and this gets rid of the AGGREGATE all together, and I think it's a bit easier to understand.

Hope this helps!

 

[JamesonMH]

Yes, without TEXTJOIN this would require VBA. Or possibly, depending on your sheet layout, and assuming no more than 10 rows per visit, we could add 10 helper columns and do it that way. Pretty cumbersome though.

The basic idea of the formula I've seen/used many times before. It took a minute or two to come up with the idea for the D2:INDEX(... construct. It probably took 10-15 minutes to debug the AGGREGATE portion of it. I was getting some screwy results for a while. I finally recognized the need for the SIGN function, as well as the IFERROR function.

And after I get it working, I sometimes consciously/unconsciously think about it some more, and sometimes come up with a better option, like:

=IF(A2<>"",IF(OR(A2<>A1,C2=1),TEXTJOIN(",",TRUE,D2:INDEX(D:D,IFERROR(MATCH(1,C3:C12,0),10)+ROW()-1)),""),"")

Shorter, and this gets rid of the AGGREGATE all together, and I think it's a bit easier to understand.

Hope this helps!

Yes, the revision is definitely easier to understand without Aggregate, thanks Eric!

However I just ran into an unexpected issue. Because my data is large, many of my returned values in column E are the same, but in different orders, which of course show as distinct items in the PivotTable (e.g. 701T, 499J in 1 cell, 499J, 701T in a separate cell further down).

I assume there's no formula (or even simple VBA) that can parse and alphabetize these billing codes so that 701T, 499J is always returned as 701T, 499J and not in reverse?

If not, I guess I can just do a Sort with a few levels to the full data set. Not ideal but I'm not confident there's a simple way ....

Thoughts?

 

[Eric W]

Well, simple VBA can do this. For example, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM. Paste the following code into the window that opened:

Public Function GetCodes(ByVal MyData As Range) As String
Dim MyStuff As Variant, i As Long, j As Long, c As Long, ary(1 To 20), w As Variant


    MyStuff = MyData.Value
    If MyStuff(1, 3) <> 1 Then Exit Function
    MyStuff(1, 3) = 0
    For i = 1 To UBound(MyStuff)
        If MyStuff(i, 3) = 1 Then Exit For
        If MyStuff(i, 4) = "" Then Exit For
        c = c + 1
        ary(i) = MyStuff(i, 4)
    Next i
    For i = 1 To c
        For j = 1 To c - i
            If ary(j) > ary(j + 1) Then
                w = ary(j)
                ary(j) = ary(j + 1)
                ary(j + 1) = w
            End If
        Next j
    Next i
            
    For i = 1 To c
        GetCodes = GetCodes & ary(i) & IIf(i < c, ",", "")
    Next i
        
End Function

Press Alt-Q. Now put this formula in E2:

=GetCodes(A2:D10)

Copy down the column as needed. I have no idea about performance though. With 77K lines, running a mini-sort on each line could take a bit. If you try it, I'd be curious to see how long it takes. There are ways to improve on this, depending on whether you have some .NET components installed on your PC.

However, it might be possible in the near future to do this with native formulas again. About a year ago, Microsoft announced some new functions, including a SORT function. They are available now to people on the Insiders program. They promise to release them to Excel 365 users "soon", once they've tested them better. This formula would be pretty simple using those.

 

[DanteAmor]

Try the following, process all the records without leaving formulas on your sheet. I hope it helps you.

Sub billing_codes()
  Dim i As Long, j As Long, a, b
  Application.ScreenUpdating = False
  j = 2
  a = Range("C2:D" & Range("A" & Rows.Count).End(xlUp).Row)
  ReDim b(UBound(a))
  For i = 1 To UBound(a)
    If a(i, 1) = 1 Then
      j = i - 1
      b(j) = a(i, 2)
    Else
      b(j) = b(j) & ", " & a(i, 2)
    End If
  Next
  Range("E2").Resize(UBound(b)).Value = Application.Transpose(b)
End Sub