Sorting a set of values according to date correspondents

michaeltsmith93

Board Regular
Joined
Sep 29, 2016
Messages
83
Table 1 features my source data, and I'm looping through it and counting the total # of X and Z visits. That output looks like Table 2. What you'll notice is that since I'm looping through by rows, Ringo's visit (13-May) is counted first, increasing the counter by 1 and therefore putting a 1 in my Count Z column. Unfortunately, Paul's visit is noticed by the loop after Ringo's, but it is the second visit that the counter has seen, so it is counted as the second visit despite the fact that Paul's visit obviously took place first. This becomes problematic because my line graph of table 2 has the count go down between 11-May (where the count is 2) and 13-May (where the count is 1).

Any thoughts on the fastest way to redistribute the counts? I can't switch the dates, but I can swap the counts themselves (i.e., the 2 and the 1 in the Count Z row could be swapped with no issue since Excel will properly order the data points on the chart). Speed is paramount because this chart update is part of a presentation-style sheet in which groups of patients are discussed, and I'm already loading a ton of other data, so the run-time is a good 2-3 seconds.

Edit: Can I do a bubble sort of a 2d array such that I'm sorting the dates and the counts get moved around? What would that look like?

Table 1
PatientX Visit DateZ Visit Date
John10-May
Ringo13-May
George12-May
Paul11-May
Eric14-May
Table 2

Date10-May13-May12-May11-May14-May
Count X12
Count Z123
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,223,103
Messages
6,170,123
Members
452,303
Latest member
c4cstore

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