Return all billing codes billed per customer visit

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
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).

ABCDE
1CustomerDateDistinct customer visit? (yes = 1)Code billedCodes billed per customer visit
2ABCSep 011499J499J, 550M
3ABCSep 01550M
4DEFSep 021425H425H
5DEFSep 051690B690B, 701T, 499J, 880Q
6DEFSep 05701T
7DEFSep 05499J
8DEFSep 05880Q
9GHHSep 081701T701T, 499J
10GHHSep 08499J

<tbody>
</tbody>

Many thanks for reading through and your time!

James
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
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

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
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.
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770

ADVERTISEMENT

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

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770

ADVERTISEMENT

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! :biggrin:
 

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
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! :biggrin:

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?
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770
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:

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try the following, process all the records without leaving formulas on your sheet. I hope it helps you.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,920
Messages
5,627,627
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top