Trying to group pivot data into weekly buckets

Please refer to tab "UHC by State"

I am trying to take the pivot data on the left and group them into Weekly buckets(ex. Week 1, 2, 3, etc). I provided an example in the tab. The start date for the range would be 1/1/2017 and end date 7/17/2017. The dates would exclude weekends for example Week 1 would be 1/2/2017 to 1/6/2017 and so on.

If there is another idea that is fine too.


Thank you.

I have removed your attachment as it contains sensitive information that should not be shared on a public forum.

I have asked a Moderator to remove your attachment. She has done so. Within a very few seconds I was able to access a list of names of real people. Not a good idea.

To your Q. I have a dislike of Pivot Tables. I prefer formulae. I have reattached the underlying raw data with ALL information except date, UHC plan, state and approval REMOVED.

One thing is obvious already, that one one day (never mind one week) MULTIPLE states can have approved / non-approved admissions.

So exactly how do you want the results presented?

can you modify the sheet that I posted to show a week of expected results. So, if there are 3 states in week 1 with approved admissions EXACTLY what do you want to see and whhere do you want to see it???

It would be really helpful if you could add a manual mock-up of the sort of thing you want to see in the file Glenn shared.

You do not need to apologise to us about the file: you just need to hope that nobody unscrupulous got to it before Glenn and I did. For what it's worth, out of respect to those named in it, I didn't open it before deleting it. Letting this sort of sensitive data into the public domain without consent could land you in a lot of hot water, so do please be very careful in future. Always remember to change or obfuscate sensitive data.

You can try filter Referral Date in you PivotTable by This week, Last Week etc... check Date filter. Also you can define your own Custom filter. (Tab UHC by State)

eg. This week

thisweek.jpg

Glenn,

Here you go.

Pigment01

That's not at all like the result structure that you had originally wanted...

Then what you had wanted was like this (but with only one stae per cell).

So please supply a result template, assuming thta this picture does NOT meet your needs.

Untitled.jpg

I got all of the data (including names, etc) from your pivot table... Do you want lost referrals as well, or just approved and not approved. BtW, to get all the states in one cell will require the use of VBA. is that OK??

So.

VBA code, pasted into a module:

Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
   
    Dim DataIndex As Variant
    Dim strResult As String
    
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        For Each DataIndex In varData
            If Len(DataIndex) > 0 Then
                 If bUnique = True Then
                      If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                       trResult = strResult & "||" & DataIndex
                    End If
                Else
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        strResult = varData
    End If
    
    ConcatAll = strResult
    
End Function

Helper column in AA, copied down:
=WEEKNUM(T2)

Array Formula in sheet2, B3, copied down:
=concatall(IF(Sheet1!$AA$2:$AA$90=Sheet2!$A3,Sheet1!$F$2:$F$90,""),", ")

Formula in sheet 2 C3, copied across and down:
=COUNTIFS(Sheet1!$W:$W,Sheet2!C$2,Sheet1!$AA:$AA,Sheet2!$A3)



Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

see sheet.

See if it does wht you want. Merely delete the columns you don't want.

AA. Yes. It returns a week number.

TbH, I didn't create the code. I shamelessly plagiarised it from another user here. I'm hopeless with VBA and use it like a drunk uses a street light: for support and not for illumination.

Regarding the original pivot table: do you know how to get the raw data (sheet1) from it?

Re the VBA. Right click on the tab name. Select View code. click on module 1. Voila...

Re the pivot. I'll be back in a mo. I'll have to find your original attachment from my Recycle Bin...

Click on any cell in the pivot table on "UHC by State" Hopefully you'll see pivot table fields on the right of your screen. if not, Go: Analyse/show/field list.

Doiwn near the bottom of the pivot table fields, drag referral dates into the Σ-values box. If it DOESN'T say count of referrals, click thedown arrow in referrals, go to value field settings and selet count of referrals.

Then... Design/subtotals/show all subtotals at bottom of group.

then click on th etiny minus sign in the box to the left of UHC on your picvot table. All being well, it will show 80 in the column count of referral dates. Double click the 80 and a new sheet will open up.

It contains all the confidential stuff. I deleted all except the bits I needed. That was what I posted way back at the start of this saga.

Clear as mud??

Originally Posted by Glenn Kennedy

I'm hopeless with VBA and use it like a drunk uses a street light: for support and not for illumination.

rotfl.gif

............................

B3:

=concatall(IF(Sheet1!$AA$2:$AA$90=Sheet2!$A3,Sheet1!$F$2:$F$90,""),", ")

Red: If the weeknumber in AA matches the value in A3
Green: return the states,
Cyan: If no matches return a blank
Orange: separate the values by a comma and a space
Black concatentate the whole lot into a single cell. This bit is the bit that uses VBA.


C3:
=COUNTIFS(Sheet1!$W:$W,Sheet2!C$2,Sheet1!$AA:$AA,Sheet2!$A3)

Red: count the values in column W
Green: that are equal to "Approved"
Cyan and where the weeknumber
Orange: Is equal to the value in A3