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 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 am not sure on how to interpret this request:
a) do you mean that from the year shown in column C on the “old name” should be replaced (in found on sheet Input) to the “new name”, without any change on the years before that one; OR...
b)...the change should affect only the year in column C, again without any change on the years before that one; OR...
c) ...or ??? (given that replacing globally old names with the new names is wrong)

 

[edscollects]

On the name change sheet column C shows the year on Input that the name changes. So if you look on the Input tab row 741. Column U shows 03NEAC and Column T shows 03UNEC. It is the same classification, the classification name just changed so thereafter it is called 03UNEC and 03NEAC was before 2021. The Name change sheet was designed originally for another macro in the workbook that shows how many changes in classifications, this is not a change in classification, it is just a name change.

 

[edscollects]

The name change does not affect the years before that, it is really just to show that on that year there was a name change that 03NEAC and 03UNEC are the same item, the name was just updated and for now on will be 03UNEC. Before 2021 it will show 03NEAC.

 

[Anthony47]

I don't understand your terminology, sorry.
Talking in Excel language, if we replace in sheet Input the terms listed in NameChange col B with the terms listed in col A, starting on the year listed in col C and any subsequent year (row 1 in sheet Input), would that be the right base for obtaing your reports?

 

[edscollects]

Yes, exactly.

 

[Anthony47]

I can't test it with my files, but I bet this new macro should do the necessary job:

Sub NewInput22()
Dim I As Long, myTim As Single
Dim CLog As Range, myMatch, yCurr As Long, yChange As Long
'
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
myTim = Timer
'
'Replace Changed
Debug.Print ">>>>"
yCurr = Year(Date)
myMatch = Application.Match(Format(yCurr, "0"), Rows(1), False)
If Not IsError(myMatch) Then
    For I = 2 To CLog.Rows.count
        yChange = CLog.Cells(I, 3)
        Debug.Print Columns(myMatch).Resize(, yCurr - yChange + 1).Address(0, 0), "Old=" & Application.WorksheetFunction.CountIf(Columns(myMatch).Resize(, yCurr - yChange + 1), CLog.Cells(I, 2)), CLog.Cells(I, 2)
        Debug.Print Columns(myMatch).Resize(, yCurr - yChange + 1).Address(0, 0), "New=" & Application.WorksheetFunction.CountIf(Columns(myMatch).Resize(, yCurr - yChange + 1), CLog.Cells(I, 1)), CLog.Cells(I, 1)
'
        Columns(myMatch).Resize(, yCurr - yChange + 1).Replace what:=CLog.Cells(I, 2), replacement:=CLog.Cells(I, 1), lookat:=xlWhole, MatchCase:=False
'
        Debug.Print Columns(myMatch).Resize(, yCurr - yChange + 1).Address(0, 0), "Old=" & Application.WorksheetFunction.CountIf(Columns(myMatch).Resize(, yCurr - yChange + 1), CLog.Cells(I, 2)), CLog.Cells(I, 2)
        Debug.Print Columns(myMatch).Resize(, yCurr - yChange + 1).Address(0, 0), "New=" & Application.WorksheetFunction.CountIf(Columns(myMatch).Resize(, yCurr - yChange + 1), CLog.Cells(I, 1)), CLog.Cells(I, 1)
    Next I
End If
ActiveSheet.Name = "InputZc"
Debug.Print Format(Timer - myTim, "0.0")
End Sub

Try...

 

[edscollects]

I tried it no error came about, but it still shows the multiple names for the same classification. I thank you for all your effort. Is it possible that it could be done via a Power Query such as this? I do not know how to use them so the reason I am asking your opinion.s666-PQ-Replace Maiden Name with Married Name (8).xlsx

 

[Anthony47]

I don't understand what "it still shows the multiple names for the same classification" means (other than "my problem was not fixed"). My effort was about replacing in sheet Input the terms listed in NameChange col B with the terms listed in col A, starting on the year listed in col C and any subsequent year (row 1 in sheet Input); was this done in your Input sheet? The file On-line don't have any "old" name in it, I could not run in dept testing with my macro
I have no experience on Power Query
Will examine later the new file; is it linked to any discussion here on the forum?

 

[edscollects]

Sorry to explain the worksheet better. It is listing of athletic teams and the conferences they compete in. I am comparing the different conferences which is the items in the input and namechange sheets. The purpose of the name change sheet is there are times organizations change their names. I want to count the as one as opposed to 2. An example of this would be if you look on the input tab at cells AC239 and AD239. This is an example. The classification was 01PAC10C but changed it name to 01PAC12C. These show as two separate names. I would like to only show it as the Arizona 01PAC12C 2007-2023 on the conf history tab.

NCAA 5-1-23.xlsm

 

[Anthony47]

Your last message suggests that the work to do in sheet Input is replacing the old names with the new ones for the past years, so that (for example) in row 329 the old "01PAC10C" become "01PAC12C"
That is the job my first macro, Sub NewInput, was doing; but that was not correct for you...