Create Cluster Chart that Updates Automatically with New Data

coco5280

New Member
Joined
Jul 8, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
I am working on a sheet where we are tracking a person by the number of issues during the year and the reason. I have included an example spreadsheet. Columns A-C will change depending on what is found during the review, but Column D has three options to choose from. Once a row has been updated, it will automatically change to match the format of the previous rows and auto-populate the information in Columns I-M. These columns also update to match the format of the previous rows, alphabetize the names, and count a total of times they appear on the list and how many times each of the reasons were the cause. One issue with this is that I had to add the formulas all the way to the bottom of the sheet which causes a lag anytime the spreadsheet is updated. The number of entries on the list are unknown at this time as it depends on how many issues we run into during the year - it could be 40 or it 300, we don't know.

My goal with this is to create a cluster column chart that updates anytime columns I-M are updated. I created a pivot table with the data that I then converted to a pivot chart. While I was able to get this to work the way that I intended, it takes forever for the everything to update. I want the x-axis to contain the name of the person, y-axis to be a range of 0-20 as a count, and the different legends to be the total and the three reasons.

I am wondering if there was a way shorten the time for everything to update by writing a VBA that generates both Columns I-M and the cluster column chart.

Example.xlsx
ABCDEFGHIJKLM
1AddressID #NameReasonNameTotal # per PersonBusyNo Phone #Moved
2123 ABC Wayjohn.doeBusyjane.doe3 21
31675 N Name Stjane.doeNo Phone #john.doe11  
4543 Merry Wayjane.doeMoved    
5234 Nowhere Lnjane.doeNo Phone #    
6    
7    
8    
9    
10    
11    
12    
13    
Service Address Log
Cell Formulas
RangeFormula
I2:I3I2=SORT(UNIQUE(FILTER(C2:C1048576,LEN(C2:C1048576))))
J2:J13J2=COUNTIF($C$2:$C$1048576,I2)
K2:K13K2=COUNTIFS($C$2:$C$1048576,I2,$D$2:$D$1048576,K$1)
L2:L13L2=COUNTIFS($C$2:$C$1048576,I2,$D$2:$D$1048576,L$1)
M2:M13M2=COUNTIFS($C$2:$C$1048576,I2,$D$2:$D$1048576,M$1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I2:M1048576Expression=AND($I2<>"",MOD(ROW(),2)=0)textNO
I2:M1048576Expression=AND($I2<>"",MOD(ROW(),2)=1)textNO
A2:D1048576Expression=AND($A2<>"",MOD(ROW(),2)=0)textNO
A2:D1048576Expression=AND($A2<>"",MOD(ROW(),2)=1)textNO
Cells with Data Validation
CellAllowCriteria
D2:D13ListBusy,No Phone #,Moved
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Here is the photo of the pivot chart generated from the pivot table.
 

Attachments

  • Picture1.png
    Picture1.png
    249.2 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

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
Back
Top