MACRO: seek, copy and paste only the last review of a drawing

[Dampyr2345]

Hi everybody!

I work in a technical office for a construction firm. As you can imagine, we have an Excel file with thousands detailed drawings regarding the construction. Every drawing is associated to a code, which is made up of various parts. I avoid to explane each part of the code, the only important parts for the Macro that has to be created are the bold ones in the following code (e.g. a drawing in the attached file):

02 H61 KT 011 KSC B0130 214A2 00

- 214A2 is a part that is UNIVOCAL for each drawing;
- 00 is the review of the drawing: each of them started with 00, but later other reviews could have been added, such as 01, 02, 03 and so on.

The whole system is actually designed to keep track of every single drawing, including every single review. In the annexed table (specifically in "Drawing List") you can see what I mean: of course it is just a part of the whole file, it is just to explane the concept.

Now, coming to the point... someone told me "ok, it's great, but many employers only need THE LAST REVIEW of the drawing, could you do something?". Well, in order to solve this problem, I was thinking about creating a new sheet, linked to the previous one, and using a macro to select, copy and paste the rows containing ONLY THE LAST REVIEW of each drawing. In other words, I need something to do this job:
- with reference to "Drawing List", identify all the drawings with the same univocal part (e.g. 214A2);
- among them, take the one that shows the most advanced review (e.g. 04), copy and paste in the other sheet ("Desired Result") the whole row which contains that review.

It has to be a repeatable macro, in the sense that I will use it every time I update the general sheet, for example adding a new drawing or simply a new review. I don't care so much about the order in which rows will be copied and pasted: one possible desired result is shown in "Desired Result", in which rows are consecutive; in alternative, keeping the position of the original row might be good as well (for example, row number 7 pasted in row number 7 in the new sheet).

Do you think it is possible? Considering that I have never built a macro, for me it's extremely hard... HELP ME, PLEASE

I would like to thank everyone who will try to solve this problem.

Bye!

DRAWING LIST:

12 - ELABORATI GENERALI <tbody> </tbody> 17A <tbody> </tbody> Variante SUD <tbody> </tbody> Programma lavori - GANTT <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 991 <tbody> </tbody> KBA <tbody> </tbody> B0130 <tbody> </tbody> V1XE6 <tbody> </tbody> 00 <tbody> </tbody> TRASMESSO - IN ATTESA DI ISTRUTTORIA <tbody> </tbody> 00 20 - PARTE I <tbody> </tbody> 29 <tbody> </tbody> CE in tradizionale <tbody> </tbody> Geometria e vista d'insieme <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 011 <tbody> </tbody> KSC <tbody> </tbody> B0130 <tbody> </tbody> 214A2 <tbody> </tbody> <tbody> </tbody> 00 <tbody> </tbody> APPROVATO CON PRESCRIZIONI <tbody> </tbody> 00 20 - PARTE I <tbody> </tbody> 29 <tbody> </tbody> CE in tradizionale <tbody> </tbody> Geometria e vista d'insieme <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 011 <tbody> </tbody> KSC <tbody> </tbody> B0130 <tbody> </tbody> 214A2 <tbody> </tbody> <tbody> </tbody> 01 <tbody> </tbody> APPROVATO <tbody> </tbody> 01 20 - PARTE I <tbody> </tbody> 29 <tbody> </tbody> CE in tradizionale <tbody> </tbody> Geometria e vista d'insieme <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 011 <tbody> </tbody> KSC <tbody> </tbody> B0130 <tbody> </tbody> 214A2 <tbody> </tbody> <tbody> </tbody> 02 <tbody> </tbody> TRASMESSO - IN ATTESA DI ISTRUTTORIA <tbody> </tbody> 02 20 - PARTE I <tbody> </tbody> 29 <tbody> </tbody> CE in tradizionale <tbody> </tbody> Geometria e vista d'insieme <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 011 <tbody> </tbody> KSC <tbody> </tbody> B0130 <tbody> </tbody> 214A2 <tbody> </tbody> <tbody> </tbody> 03 <tbody> </tbody> APPROVATO CON PRESCRIZIONI <tbody> </tbody> 03 20 - PARTE I <tbody> </tbody> 29 <tbody> </tbody> CE in tradizionale <tbody> </tbody> Geometria e vista d'insieme <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 011 <tbody> </tbody> KSC <tbody> </tbody> B0130 <tbody> </tbody> 214A2 <tbody> </tbody> <tbody> </tbody> 04 <tbody> </tbody> TRASMESSO - IN ATTESA DI ISTRUTTORIA <tbody> </tbody> 04 20 - PARTE I <tbody> </tbody> 29 <tbody> </tbody> CE in tradizionale <tbody> </tbody> Carpenteria sella <tbody> </tbody> 02 <tbody> </tbody> H61 <tbody> </tbody> EG <tbody> </tbody> 011 <tbody> </tbody> KSC <tbody> </tbody> B0130 <tbody> </tbody> 214A3 <tbody> </tbody> <tbody> </tbody> <tbody> </tbody> 00 <tbody> </tbody> TRASMESSO - IN ATTESA DI ISTRUTTORIA <tbody> </tbody> 00 <tbody> </tbody>

<tbody>
</tbody>

DESIRED RESULT:

12 - ELABORATI GENERALI <tbody> </tbody>

17A <tbody> </tbody>

Variante SUD <tbody> </tbody>

Programma lavori - GANTT <tbody> </tbody>

02 <tbody> </tbody>

H61 <tbody> </tbody>

EG <tbody> </tbody>

991 <tbody> </tbody>

KBA <tbody> </tbody>

B0130 <tbody> </tbody>

V1XE6 <tbody> </tbody>

00 <tbody> </tbody>

TRASMESSO - IN ATTESA DI ISTRUTTORIA <tbody> </tbody>

00

20 - PARTE I 20 - PARTE I <tbody> </tbody>

29 29 <tbody> </tbody>

CE in tradizionale CE in tradizionale <tbody> </tbody>

Geometria e vista d'insieme Carpenteria sella <tbody> </tbody>

02 02 <tbody> </tbody>

H61 H61 <tbody> </tbody>

EG EG <tbody> </tbody>

011 011 <tbody> </tbody>

KSC KSC <tbody> </tbody>

B0130 B0130 <tbody> </tbody>

214A2 214A3 <tbody> </tbody> <tbody> </tbody>

04 00 <tbody> </tbody>

APPROVATO CON PRESCRIZIONI TRASMESSO - IN ATTESA DI ISTRUTTORIA <tbody> </tbody>

04

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to the board.
How about

Sub GetLatest()

   Dim Cl As Range
   Dim Itm As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("K2", Range("K" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl.Offset(, 1).Value, Cl)
         ElseIf Cl.Offset(, 1).Value > .Item(Cl.Value)(0) Then
               .Item(Cl.Value) = Array(Cl.Offset(, 1), Cl)
         End If
      Next Cl
      For Each Itm In .items
         Itm(1).EntireRow.Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
      Next Itm
   End With
            
End Sub

This assumes that the data is in the active sheet, with a header in row 1 & data starting in row 2. Also that your drawing numbers are in col K with the issue in L