Name Change that does not change class

[edscollects]

I have this document that I am inputting information on the Input tab. I am then performing different functions on each of the other tabs. On the input tab there are schools in column B and class names by year in most of the other columns. For each of the schools on the conf history I list each unique class name and the years in that class. In the Conf Output tab I list each class name and each school and year that has participated in that class. There is an occasion when the class name changes, much in the same way a woman changes their last name when they get married. I have these changes listed with the year on the Name Change tab. I need the Conf History and Conf Output to both not count those name changes as unique strings. An perfect example of this is in 2019 03IIAC changed its class name to 03AMRIV. Currently on those Conf History and Conf Output is showing both as different classes. I would like it to show only the newest class name and include all the former class name years and schools. I would greatly appreciate any help.


NCAA 5-1-23.xlsm

 

[Anthony47]

I seem that you build ConfOutput and ConfHistory starting from sheet Input.
So my suggestion is that you create a "temporary Input" where old names have been replaced by newo ones, and then you build your outputs starting from this temporary Input.

The folllowing macro
1) duplicates sheet Input (that don't get modified
2) replaces the old names according the list from sheet NameChange
3) renames this new sheet as InputZc

Sub NewInput()
Dim I As Long
Dim CLog As Range
'
On Error Resume Next
Application.DisplayAlerts = False
    Sheets("InputZc").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'Duplicate Input
Sheets("Input").Copy after:=Sheets("Input")
Set CLog = Sheets("Name Change").Range("A1").CurrentRegion
Set CLog = Sheets("Foglio5").Range("A1").CurrentRegion
'Replace Changed
For I = 2 To CLog.Rows.count
    Cells.Replace what:=CLog.Cells(I, 2), replacement:=CLog.Cells(I, 1), lookat:=xlWhole, MatchCase:=False
Next I
ActiveSheet.Name = "InputZc"
End Sub

So you may rework your macros that generate the output:
a) on top of them insert Call NewInput, to create this new sheet
b) modify your code to refer to InputZc rather than to Input (I think you have only to modify in Sub SummariseCodes_WithColours the line Set shtInput = Worksheets("Input"), but you know your code better than me)
If you wish you might add Sheets("InputZc").Delete before the end of your macro, to remove the added sheet

Try...

 

[edscollects]

What is the sheet Foglio5?

 

[Anthony47]

I used that line for testing and forgot to delete it before publishing the message
Please delete it

 

[edscollects]

Thank you it works. It takes some time to run is there any way to speed it up? Or is that not possible.

 

[edscollects]

I just ran the code and I noticed that on the InputZc sheet it randomly is changing the number prefix. Is there a reason this would happen? Take a look.NCAA 5-1-23.xlsm

 

[Anthony47]

Please give me some example, I don't know what to search

 

[edscollects]

Ok if you go to the InputZc Rows AE1132 is just one example. Any color that is the purple color should have a 05 prefix and if you look quite a few randomly changed to a 04 prefix.

 

[Anthony47]

Probably I misunderstood the meaning of the table in NameChange:

byEDSCOLLECTS_C30603_ NCAA 5-1-23.xlsm
	A	B	C
1	New	Old	Year
2	03C2CAC	03CAPAC	2022
3	03UNEC	03NEAC	2021
4	04CONTAC	05AII	2021
5	04APPAC	05APPAC	2021
6	04CCAC	05CCAC	2021
7	04CALPAC	05CALPAC	2021
8	04CASCC	05CASCC	2021
9	04CCAC	05CCAC	2021
10	04CROSL	05CROSL	2021
11	04GPAC	05GPAC	2021
12	04KCAC	05KCAC	2021
13	04NSAA	05NSAA	2021
14	04RIVSTC	05RIVSTC	2021
15	04SUNCON	05SUNCON	2021
16	04WHAC	05WHAC	2021
17	05AII	04AII	2020
18	03AMRIV	03IIAC	2019
19	05CROSL	05MCCC	2013
20	05CCAC	04CCAC	2013
21	01AMAC	01BIGEC	2014
22	05RIVSTC	05KIAC	2016
Name Change

My macro replace any "Old" listed in column B with its "New" in column A; thus any "05APPAC" became "04APPAC"

If this is wrong how that table should be interpreted??

 

[edscollects]

The table in name change is when a class changes names. So on the input tab every column to the right of Column B is a year. Now on the name change tab it shows when a class changes names and the year it changes. So for instance, in 2021 03NEAC changed to 03UNEC. This is so that in the year 2020 the names 03NEAC and in 2021 03UNEC is not a change in classification but the classification changes names. To restate a possibly simpler way. If this was a woman, 03NEAC was married in 2021 and the married name thereafter is 03UNEC.