Seleziona il record più recente e crea una nuova tabella di valori unici in Excel

Ho una tabella di Excel che registra i progressi dei veicoli attraverso le postazioni di lavoro all’interno di un’azienda. Una determinata stazione di lavoro può essere visitata più di una volta.

Il numero di targa del veicolo (Reg No) viene utilizzato per identificare il veicolo e l’utente aggiunge una nuova riga al foglio di calcolo quando il veicolo arriva alla successiva nuova stazione di lavoro. Oltre al numero di registro, ogni riga ha anche un ID record.

Questo stream di lavoro significa che verranno create più righe di record per un determinato numero di registro.

Quando tutto il lavoro su un veicolo è completo, il record viene tagliato e archiviato su un altro foglio di lavoro.

Tabella dei veicoli attuale

Quello che voglio creare è una tabella riassuntiva su un’altra scheda del foglio di lavoro, che mostra tutte le righe per i veicoli in corso. Quando un veicolo ha attualmente un solo record, voglio estrarre quella riga del record e, laddove un veicolo ha più record, voglio estrarre solo l’ultima (più recente) riga del record.

Voglio che il riassunto sia un riflesso “vivo” della scheda tecnica sottostante.

Dalle ricerche ho trovato esempi di formule per ignorare duplicati e creare un nuovo elenco di valori unici in Excel, ma questi selezionano il primo valore duplicato per impostazione predefinita, non l’ultimo. I risultati della ricerca su “ricerca ultima corrispondenza” o “ritorno ultimo valore” hanno in comune che l’utente deve definire l’articolo che sta cercando.

Penso di aver bisogno di qualcosa di diverso perché la mia lista di Reg No non è statica, è continuamente aggiornata da Reg No che viene aggiunta e rimossa (archiviata).

Riconoscere e comprendere Excel non è un database, ma se stavo pensando in termini di database / SQL, la mia query (noob) potrebbe essere qualcosa di simile: SELEZIONA la riga WHERE Reg No è ​​univoco E ID record è il più grande

Sei a conoscenza di un modo per ottenere il risultato che cerco, in Excel?

È ansible utilizzare una tabella pivot e una formula di ricerca per ottenere ciò. Di seguito sono riportati alcuni dati semplificati in una tabella di Excel (nota anche come ListObject), e sotto è una tabella pivot con una formula di ricerca adeguata in basso a destra.

inserisci la descrizione dell'immagine qui

La tabella pivot contiene il numero di registro e il campo ID record nell’area di valori (aggregazione), impostato su “Max”. Quindi in pratica visualizza il valore massimo RecordID per ogni Reg No, e quindi nella colonna a destra c’è una formula INDEX / MATCH che cerca nuovamente il RecordID nella tabella di inserimento dati e restituisce lo Stage associato.

Non è abbastanza attivo poiché sarà necessario aggiornare la tabella pivot e sarà necessario assicurarsi di aver copiato la formula di ricerca abbastanza in profondità per gestire la dimensione della tabella pivot.

È ansible automatizzare facilmente l’aggiornamento semplicemente inserendo un gestore di eventi Worksheet_Activate nel foglio di lavoro. Qualcosa di simile: Private Sub Worksheet_Activate () Activesheet.PivotTables (“Tabella pivot”). PivotCache.Refresh End Sub

Dato che ora abbiamo coinvolto VBA, potresti avere un codice che copia la formula in basso sulla quantità richiesta di righe accanto alla tabella pivot. Sbatterò qualcosa a tempo debito e pubblicherò qui.

AGGIORNAMENTO: ho scritto del codice per eseguire il slave di una tabella su una tabella pivot, in modo che qualsiasi modifica delle dimensioni o del posizionamento della tabella pivot si rifletta nelle dimensioni e nel posizionamento della tabella di ombreggiatura. Ciò ci consente in modo efficace di aggiungere un campo calcolato a una tabella pivot che può riferirsi a qualcosa al di fuori di quella tabella pivot, come stiamo facendo qui con la ricerca INDICE / MATCH. Chiamiamo tale funzionalità come tabella calcasting .

Se la tabella pivot cresce, la tabella calcasting crescerà. Se la tabella pivot si riduce, la tabella calcasting si ridurrà e tutte le formule ridondanti in essa contenute verranno eliminate. Ecco come cerca il tuo esempio: la tabella in alto proviene dal foglio di input e la tabella pivot e la tabella calcasting sotto sono del foglio Risultati .

inserisci la descrizione dell'immagine qui

Se si passa al foglio di input e si aggiungono altri dati, quando si passa nuovamente al foglio Risultati, la tabella pivot viene aggiornata automaticamente con i nuovi dati e la tabella calcasting si espande automaticamente per contenere le righe aggiuntive: inserisci la descrizione dell'immagine qui

Ed ecco il codice che uso per automatizzare questo:

Option Explicit Private Sub Worksheet_Activate() ActiveSheet.PivotTables("Report").PivotCache.Refresh End Sub Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) If Target.Name = "Report" Then _ PT_SyncTable Target, ActiveSheet.ListObjects("SyncedTable") End Sub Sub PT_SyncTable(oPT As PivotTable, _ oLO As ListObject, _ Optional bIncludeTotal As Boolean = False) Dim lLO As Long Dim lPT As Long 'Make sure oLO is in same row If oLO.Range.Cells(1).Row <> oPT.RowRange.Cells(1).Row Then oLO.Range.Cut Intersect(oPT.RowRange.EntireRow, oLO.Range.EntireColumn).Cells(1, 1) End If 'Resize oLO if required lLO = oLO.Range.Rows.Count lPT = oPT.RowRange.Rows.Count If Not bIncludeTotal And oPT.ColumnGrand Then lPT = lPT - 1 If lLO <> lPT Then oLO.Resize oLO.Range.Resize(lPT) 'Clear any old data outside of oLO if it has shrunk If lLO > lPT Then oLO.Range.Offset(oLO.Range.Rows.Count).Resize(lLO - lPT).ClearContents End Sub 

La cosa interessante è che il codice ridimensiona automaticamente la tabella calcasting ogni volta che la tabella pivot viene aggiornata e tali aggiornamenti vengono triggersti ​​anche dal filtraggio sulla tabella pivot. Quindi se filtri solo un paio di numeri rego, ecco quello che vedi: inserisci la descrizione dell'immagine qui

Hai menzionato SQL nella tua domanda, quindi ho pensato che potresti essere interessato a una soluzione VBA usando SQL:

 'Assumes your data is on a sheet called "DataSheet", and you want the answers stored starting in cell A2 of a sheet called "Results") Sub test() Dim objConnection As ADODB.Connection Dim objRecordset As ADODB.Recordset Set objConnection = New ADODB.Connection Set objRecordset = New ADODB.Recordset objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.FullName & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" objConnection.Open sqlcommand = "SELECT LAST([Record ID]), " & _ "[Reg No], " & _ "LAST([Priority Level]), " & _ "LAST([Make]), " & _ "LAST([Current Stage]) " & _ "FROM [DataSheet$] GROUP BY [Reg No]" objRecordset.Open sqlcommand, objConnection, adOpenStatic, adLockOptimistic, adCmdText Sheets("Results").Range("A2").CopyFromRecordset objRecordset End Sub 

Per utilizzarlo, è necessario includere riferimenti nella “Libreria Microsoft ActiveX Data Objects 6.1” e “Libreria Microsoft ActiveX Data Objects Recordset 6.0” nel progetto VBA. (Almeno, sono quelli che seleziono.)