1. Paste your data into the Sheet 1 of a new Excel table (that is, the worksheet 1) (since there is no data for you, I will directly use ABC as the prefix).
2. press and hold Alt and F 1 1.
3. Click Insert and Module in the new window that appears.
4. Enter the following code in the area where the cursor is located:
Sub-search ()
Dim temp in string form
Mark I as an integer
Dim j is an integer
Mark r as an integer
Set the maximum value to an integer.
j = 1
Dim rngA As range
Dim rngC As range
Set rngA = Sheet 1。 Scope ("A:A")
Set rngC = Sheet 1. Scope ("C:C")
temp = rngA(2)
Max = rngC(2)
r = 2
For i = 2 to Sheet 1. UsedRange.Rows.Count
If (temp = rngA(i)), then
If (rngC(i)>Max) and then
Max = rngC(i)
r = i
If ... it will be over.
other
Sheet 1。 Number of rows (r). Copy paper 2. Number of rows (j)
j = j + 1
temp = rngA(i)
Max = rngC(i)
r = i
If ... it will be over.
Next, I
End joint
5. Click the green triangle on this window (please make sure that you have Sheet2 in your workbook before clicking).
6. You can find the results of the above search in Sheet2.
Before writing the above code, please make sure that the Excel has a Sheet2 worksheet. If not, please create a new one first (in general, the new serial number will automatically be Sheet2). If it can't be solved, please slightly modify the above code as follows:
Sub-search ()
Dim temp in string form
Mark I as an integer
Dim j is an integer
Mark r as an integer
Set the maximum value to an integer.
Dim sheets
Set SheetMY = ThisWorkbook. Worksheet. add
j = 1
Dim rngA As range
Dim rngC As range
Set rngA = Sheet 1。 Scope ("A:A")
Set rngC = Sheet 1. Scope ("C:C")
temp = rngA(2)
Max = rngC(2)
r = 2
For i = 2 to Sheet 1. UsedRange.Rows.Count
If (temp = rngA(i)), then
If (rngC(i)>Max) and then
Max = rngC(i)
r = i
If ... it will be over.
other
Sheet 1。 Number of rows (r). Copy paper. Number of rows (j)
j = j + 1
temp = rngA(i)
Max = rngC(i)
r = i
If ... it will be over.
Next, I
End joint
In this way, the program will automatically create a new worksheet and summarize the results that meet the requirements in the new worksheet. Of course, if you are using WPS or an incomplete version of OFFICE, please install VBA or the full version of Office first. ...