Counting the number of Diagnosis codes by context ,Specialty and provider

Hi Team,

Hope you are doing well!... I am trying to count the number of diagnosis codes by context ,Specialty and provider..Can you please help me here..Attached the Input and the output required sheets....

Note :The data is arranged in such a way that for each visitbill/contextid combination there are multiple diagnosis codes separated by commas ...

Appreciate your help!



Thanks,
Arun

Are you wanting to create the complete table of columns A:E or are columns A:D already present and you just want the totals in column E?

As you are using a version of Excel that includes Get & Transform:
1. Convert the range on the Input sheet into an Excel table (Ctrl + t)
2. From the Data tab select From Table/Range
3. Paste the following into the Advanced Editor code:

Please Login or Register  to view this content.

Note that the output table is modeled on the Output sheet.
Let us know if you have any questions.

Results are in Sheet1.
In A2 then copied across and down up to column D.

Please Login or Register  to view this content.

In E2 then copy down.

Please Login or Register  to view this content.

In sheet Input helper column G is used. In G2 then copy down.

Please Login or Register  to view this content.

Somewhat, however it could be modified to accommodate more.
In the sample file the most diagnosis codes that are given in column E on the Input sheet is six, so the query is set to split column E into six columns.
If there are going to be more than six codes in say cell E5 then the #split column by line of code would need to be modified as follows (scroll to right):

Please Login or Register  to view this content.

Once the code has been updated select Done and then Close & Load.
If you are adding a code to a cell that has less than six codes then all you will need to do is select any cell in the green/white table on the Output sheet and Refresh (from either the Table Tools or Query Tools tab).
Let us know if you have any questions.