Macro for Excel Automatically Sort When Data Changes

[keranali]

need help with a macro or formula to automatically sort data as data is changed

the project.xlsx
	G	H
1	Number Frequency
2	23	4
3	26	5
4	1	6
5	16	6
6	25	6
7	9	7
8	17	7
9	29	7
10	31	7
11	2	8
12	7	8
13	24	8
14	8	9
15	12	9
16	15	9
17	28	9
18	3	10
19	4	10
20	5	10
21	6	10
22	22	10
23	33	10
24	36	10
25	27	11
26	32	11
27	14	12
28	18	12
29	19	12
30	20	12
31	21	12
32	34	12
33	11	13
34	35	13
35	10	14
36	13	14
37	30	15
Data

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(Data!D:D,23)
H3	H3	=COUNTIF(Data!D:D,26)
H4	H4	=COUNTIF(Data!D:D,1)
H5	H5	=COUNTIF(Data!D:D,16)
H6	H6	=COUNTIF(Data!D:D,25)
H7	H7	=COUNTIF(Data!D:D,9)
H8	H8	=COUNTIF(Data!D:D,17)
H9	H9	=COUNTIF(Data!D:D,29)
H10	H10	=COUNTIF(Data!D:D,31)
H11	H11	=COUNTIF(Data!D:D,2)
H12	H12	=COUNTIF(Data!D:D,7)
H13	H13	=COUNTIF(Data!D:D,24)
H14	H14	=COUNTIF(Data!D:D,8)
H15	H15	=COUNTIF(Data!D:D,12)
H16	H16	=COUNTIF(Data!D:D,15)
H17	H17	=COUNTIF(Data!D:D,28)
H18	H18	=COUNTIF(Data!D:D,3)
H19	H19	=COUNTIF(Data!D:D,4)
H20	H20	=COUNTIF(Data!D:D,5)
H21	H21	=COUNTIF(Data!D:D,6)
H22	H22	=COUNTIF(Data!D:D,22)
H23	H23	=COUNTIF(Data!D:D,33)
H24	H24	=COUNTIF(Data!D:D,36)
H25	H25	=COUNTIF(Data!D:D,27)
H26	H26	=COUNTIF(Data!D:D,32)
H27	H27	=COUNTIF(Data!D:D,14)
H28	H28	=COUNTIF(Data!D:D,18)
H29	H29	=COUNTIF(Data!D:D,19)
H30	H30	=COUNTIF(Data!D:D,20)
H31	H31	=COUNTIF(Data!D:D,21)
H32	H32	=COUNTIF(Data!D:D,34)
H33	H33	=COUNTIF(Data!D:D,11)
H34	H34	=COUNTIF(Data!D:D,35)
H35	H35	=COUNTIF(Data!D:D,10)
H36	H36	=COUNTIF(Data!D:D,13)
H37	H37	=COUNTIF(Data!D:D,30)

 

[OaklandJim]

I believe that I was able to set up a workbook that does what is wanted. Get it HERE.

I hope that you know what the worksheet change event is? Code uses that.

'Code module for the worksheet named Data.
'Worksheet_Change event.
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim bDoSort As Boolean
    
    Dim rIntersectRange As Range
    
    Dim rDataToSortRange As Range
    
'   Anytime any cell in column D changes the sort will occur.
    Set rIntersectRange = Intersect(Target, Range("D:D"))
    
    If Not rIntersectRange Is Nothing _
     Then
        Call SortOnChange
    End If

End Sub

Option Explicit

Sub SortOnChange()

    Dim rNumberHeaderCell As Range 'Header cell containing Number for the data to sort.

    Dim rFreqHeaderCell As Range 'Header cell for the frequency column for dta to sort.

    Dim rSortRange As Range 'Range containg all data (cells) to sort.

    Dim iDataRows As Long  'Count of rows in the the range to sort
    
    Dim sMsg As String 'Used to tell user that code did not find the header
                       'cell containg the word "Number"
    
    With Worksheets("Data")
    
'       Look for the word Number in the worksheet named Data to determine where
'       the data to sort is.
        
        Set rNumberHeaderCell = .Cells.Find("Number", LookIn:=xlValues, SearchOrder:=xlByColumns)
        
        If rNumberHeaderCell Is Nothing _
         Then
         
            sMsg = "Could not find a cell containing the" _
            & Chr(10) _
            & "word Number in the worksheet that is named" _
            & Chr(10) _
            & "Data so we cannot find the data to sort!"
            
            MsgBox sMsg, vbCritical
            
        Else
        
            iDataRows = rNumberHeaderCell.Offset(100000).End(xlUp).Row - rNumberHeaderCell.Row
            
'           Range object points to the data (cells) to be sorted.
            Set rSortRange = rNumberHeaderCell.Resize(iDataRows + 1, 2)
            
'           Single celled "header" above cells containing frequency data.
            Set rFreqHeaderCell = rNumberHeaderCell.Offset(0, 1)
        
            With .Sort

                .SortFields.Clear

                .SortFields.Add Key:=rFreqHeaderCell, Order:=xlAscending

                .SetRange rSortRange

                .Header = xlYes
                
                .Apply

            End With '.Sort

        End If
        
    End With

End Sub

 

[OaklandJim]

Sort which column?

 

[OaklandJim]

Your formulas look like this for example =COUNTIF(Data!D:D,30) which I do not understand. BUT, you did not provide the data in column D. So the formulas in column B always evaluate to #VALUE!

 

[keranali]

Sorry for the bad explanation please see below it sorting dynamically on the frequency column when a new entry is inputted.

the project.xlsx
	B	C	D		F	G	H
1	Date	Time	Num			Number Frequency
2	2-Jan-23	Morning	33			23	4
3	2-Jan-23	Midday	21			26	5
4	2-Jan-23	Afternoon	4			1	6
5	2-Jan-23	Evening	1			16	6
6	3-Jan-23	Morning	2			25	6
7	3-Jan-23	Midday	15			9	7
8	3-Jan-23	Afternoon	32			17	7
9	3-Jan-23	Evening	22			29	7
10	4-Jan-23	Morning	27			31	7
11	4-Jan-23	Midday	5			2	8
12	4-Jan-23	Afternoon	32			7	8
13	4-Jan-23	Evening	35			24	8
14	5-Jan-23	Morning	18			8	9
15	5-Jan-23	Midday	12			12	9
16	5-Jan-23	Afternoon	15			15	9
17	5-Jan-23	Evening	11			28	9
18	6-Jan-23	Morning	19			3	10
19	6-Jan-23	Midday	12			4	10
20	6-Jan-23	Afternoon	2			5	10
21	6-Jan-23	Evening	20			22	10
22	7-Jan-23	Morning	36			33	10
23	7-Jan-23	Midday	36			36	10
24	7-Jan-23	Afternoon	5			6	11
25	7-Jan-23	Evening	20			32	11
26	9-Jan-23	Morning	32			27	12
27	9-Jan-23	Midday	13			14	12
28	9-Jan-23	Afternoon	5			18	12
29	9-Jan-23	Evening	21			19	12
30	10-Jan-23	Morning	18			20	12
31	10-Jan-23	Midday	5			21	12
32	10-Jan-23	Afternoon	35			34	12
33	10-Jan-23	Evening	4			11	13
34	11-Jan-23	Morning	30			35	14
35	11-Jan-23	Midday	17			10	14
36	11-Jan-23	Afternoon	33			13	14
37	11-Jan-23	Evening	11			30	15
38	12-Jan-23	Morning	13
39	12-Jan-23	Midday	33
40	12-Jan-23	Afternoon	28
41	12-Jan-23	Evening	33
42	13-Jan-23	Morning	34
43	13-Jan-23	Midday	21
44	13-Jan-23	Afternoon	19
45	13-Jan-23	Evening	8
46	14-Jan-23	Morning	18
47	14-Jan-23	Midday	11
48	14-Jan-23	Afternoon	12
49	14-Jan-23	Evening	19
50	16-Jan-23	Morning	6
51	16-Jan-23	Midday	13
52	16-Jan-23	Afternoon	6
53	16-Jan-23	Evening	10
54	17-Jan-23	Morning	33
55	17-Jan-23	Midday	3
56	17-Jan-23	Afternoon	15
57	17-Jan-23	Evening	22
58	18-Jan-23	Morning	20
59	18-Jan-23	Midday	26
60	18-Jan-23	Afternoon	30
61	18-Jan-23	Evening	4
62	19-Jan-23	Morning	13
63	19-Jan-23	Midday	12
64	19-Jan-23	Afternoon	1
65	19-Jan-23	Evening	14
66	20-Jan-23	Morning	10
67	20-Jan-23	Midday	19
68	20-Jan-23	Afternoon	18
69	20-Jan-23	Evening	28
70	21-Jan-23	Morning	24
71	21-Jan-23	Midday	30
72	21-Jan-23	Afternoon	3
73	21-Jan-23	Evening	32
74	23-Jan-23	Morning	4
75	23-Jan-23	Midday	20
76	23-Jan-23	Afternoon	36
77	23-Jan-23	Evening	10
78	24-Jan-23	Morning	9
79	24-Jan-23	Midday	10
80	24-Jan-23	Afternoon	12
81	24-Jan-23	Evening	21
82	25-Jan-23	Morning	29
83	25-Jan-23	Midday	8
84	25-Jan-23	Afternoon	15
85	25-Jan-23	Evening	21
86	26-Jan-23	Morning	34
87	26-Jan-23	Midday	5
88	26-Jan-23	Afternoon	32
89	26-Jan-23	Evening	17
90	27-Jan-23	Morning	26
91	27-Jan-23	Midday	6
92	27-Jan-23	Afternoon	11
93	27-Jan-23	Evening	13
94	28-Jan-23	Morning	2
95	28-Jan-23	Midday	2
96	28-Jan-23	Afternoon	27
97	28-Jan-23	Evening	14
98	30-Jan-23	Morning	25
99	30-Jan-23	Midday	19
100	30-Jan-23	Afternoon	26
Data

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(Data!D:D,23)
H3	H3	=COUNTIF(Data!D:D,26)
H4	H4	=COUNTIF(Data!D:D,1)
H5	H5	=COUNTIF(Data!D:D,16)
H6	H6	=COUNTIF(Data!D:D,25)
H7	H7	=COUNTIF(Data!D:D,9)
H8	H8	=COUNTIF(Data!D:D,17)
H9	H9	=COUNTIF(Data!D:D,29)
H10	H10	=COUNTIF(Data!D:D,31)
H11	H11	=COUNTIF(Data!D:D,2)
H12	H12	=COUNTIF(Data!D:D,7)
H13	H13	=COUNTIF(Data!D:D,24)
H14	H14	=COUNTIF(Data!D:D,8)
H15	H15	=COUNTIF(Data!D:D,12)
H16	H16	=COUNTIF(Data!D:D,15)
H17	H17	=COUNTIF(Data!D:D,28)
H18	H18	=COUNTIF(Data!D:D,3)
H19	H19	=COUNTIF(Data!D:D,4)
H20	H20	=COUNTIF(Data!D:D,5)
H21	H21	=COUNTIF(Data!D:D,22)
H22	H22	=COUNTIF(Data!D:D,33)
H23	H23	=COUNTIF(Data!D:D,36)
H24	H24	=COUNTIF(Data!D:D,6)
H25	H25	=COUNTIF(Data!D:D,32)
H26	H26	=COUNTIF(Data!D:D,27)
H27	H27	=COUNTIF(Data!D:D,14)
H28	H28	=COUNTIF(Data!D:D,18)
H29	H29	=COUNTIF(Data!D:D,19)
H30	H30	=COUNTIF(Data!D:D,20)
H31	H31	=COUNTIF(Data!D:D,21)
H32	H32	=COUNTIF(Data!D:D,34)
H33	H33	=COUNTIF(Data!D:D,11)
H34	H34	=COUNTIF(Data!D:D,35)
H35	H35	=COUNTIF(Data!D:D,10)
H36	H36	=COUNTIF(Data!D:D,13)
H37	H37	=COUNTIF(Data!D:D,30)

 

[OaklandJim]

You definitely left out some detail. When you say "when data changes" which data? Is sorting done only on cols G & H (based on frequency)? Any other detail that someone helping should know about?

 

[keranali]

My apologies sorting will only be done on column H from lowest to Highest Data will be inputed 4 times per day on columns C & D. I can sort it manually however it will have to sort everytime an entry is inputted wanted it to dynamically sort.

 

[OaklandJim]

Attempting to understand the need, I it seems that if user changes something in column D then the sort triggers. Presumably, changes occur to data one row in cols B then C then D but until the value in col D is input the entry (B & C & D) is not complete. Should data sort if a row is deleted from a row of data in cols B through D?

 

[keranali]

You are correct and yes if data is deleted it should update the sort, col B and C is not too important for the data sort which will take place on col H

 

[OaklandJim]

I made one change to the sort code. This was done sothat the Number values are sorted after the Frequency data so that the Number values are in order. Example. If Number values for frequency of 1 are 23, 13, 15, 24 they will now be in ascending order: 13, 15, 23, 24.

Replace this one line of code

                .SortFields.Add Key:=rFreqHeaderCell, Order:=xlAscending

with these TWO lines of code.

                .SortFields.Add Key:=rFreqHeaderCell, Order:=xlAscending
                
                .SortFields.Add Key:=rNumberHeaderCell, Order:=xlAscending '<= added key to sort.