-
Articoli
-
Ciao.
Utilizzo Office 2019.
Vorrei un aiuto sempre sulla gestione di alcuni dati ambientali già illustrati nel precedente post.
Si tratta di monitoraggi della acque marine costiere. Ad ogni stazione (NationalStationID) sono associate più date di campionamento nell'anno. Per ogni data di una determinata stazione (posizionata sempre nello stesso punto geografico) vengono acquisite mediante una sonda CTD alcuni parametri chimici e fisici dell’acqua di mare dalla superficie fino al fondo o fino ad una determinata profondità se il fondale in quel punto è troppo alto.
Purtroppo ho scoperto un errore che dovrei eliminare. Si tratta di dati duplicati in quanto immessi nel file due volte. Ne file allegato i dati duplicati sono presenti per la campagna del 26/9/2019 ma non per quella del 9/12/2019.
Quello che voglio è l'eliminazione delle righe duplicate dall'insieme dei dati (circa 500000 righe o record per il 2019).
Lo so che la via più semplice sarebbe quella di importare i dati in ACCESS e con una query eliminare i duplicati, però sono sicuro che per importare i dati dovrei ripulirli da numerosi errori di formattazione. Ho provato ad usare anche Power Query in Excel ma purtroppo non ci sono riuscito anche perché mi è sembrato non proprio semplice da usare.
E' possibile realizzare una macro o script in VBA che permetta di eliminare i duplicati in questo file prova e poi nel mio file dati con oltre 500000 righe ?
Se lo sviluppo dello script è complesso e richiede tempo, non vi preoccupate, vuol dire che importerò in ACCESS i dati.
Grazie
eliminato
>>>Se lo sviluppo dello script è complesso e richiede tempo
No, forse sarà il tempo d'esecuzione... Non sò se è legato all'altro post, comunque fare un ciclo FOR dove aggiungi la colonna AA con una stringa =A&B&C&Decc ecc
e dopo fare un rimuovi duplicati???Option Explicit Sub Duplicati() Dim msg As String, X As Long, Y As Long, C As Long, Ur As Long Ur = Range("A" & Rows.Count).End(xlUp).Row C = Rows("1:1" & Columns.Count).End(xlToRight).Column For X = 2 To Ur For Y = 1 To C msg = msg & Cells(X, Y) Next Y Range("AA" & X) = msg Next X ActiveSheet.Range("$A$1:$AA$"&Ur).RemoveDuplicates Columns:=27, Header:=xlNo MsgBox "Fatto" End SubGrazie Raffaele.
Ho eseguito la tua routine, ma elimina tutte le righe con Year=2019, Month=12 e Day=9 e nella colonna AA sono presenti 213 righe con la stessa stringa:
276698ITISCMSBasilicataM1_SINNI_3670201992610:58:00M1_SINNI_3_2019_09_CF_006Chlorophyll aμg/l0,190,5NAITNUT19-MSFD-00000000M1_SINNI_32019-09-26T10:58:00.000
213 sono le righe rimaste con data 26/9/2019 (Year=2019, Month=9 e Day=26).
Le righe iniziali con data 26/9/2019 sono 1430 (la metà sono duplicati), quelle con data 9/12/2019 sono 716.
Le righe duplicate sono quelle relative alla data 26/9/2019 e che hanno valori identici dei campi/colonne: X.U.FEFF.CountryCode, MRU, Region, NationalStationID, SeaDepth, Year, Month, Day, Time, SampleID, Determin_Nutrients, NutrientsSeawater_unit, LOD_LOQ_Flag, Concentration, SampleDepth, LOQ, LOD, AnalyticalMethod, Remarks, Station, Time.
Per intenderci le righe 2 e 3 sono duplicati, quindi una delle due o va eliminata o trasferita in un nuovo foglio insieme alle righe non duplicate. Per la data 26/9/2019 tutti i dati sono duplicati; quindi metà delle 1430 righe, cioè 715 righe o vanno eliminate eliminate dal Foglio1 oppure copiate in un nuovo foglio "DatiNoDuplicati" insieme al resto dei dati (in questo caso i dati del 9/12/2019 che non contengono duplicati).In sostanza la routine dovrebbe secondo me leggere ogni riga e confrontarla con la precedente (la prima riga di default va nel nuovo foglio); se sono uguali allora la scarta e passa al successivo rigo, se, invece è differente lo copia nel nuovo foglio.
Spero di essermi spiegato.
Ciao
Non conoscevo il metodo RemoveDuplicates dell'oggetto Range, non si finisce mai di imparare
Ho fatto piu' o meno la stessa cosa ma confrontando solo le colonne da D a P con quelle della riga successiva (Dando per scontato che i duplicati siamo sempre consecutivi, ma e' sbagliato come concetto) e eliminando la riga duplicata.
Allego lo stesso il file ma e' sicuramente fatto meglio quello di Raffaele.
Aldo funziona !!! ,
ma guardando lo script della tua macro non ho trovato il metodo metodo RemoveDuplicates dell'oggetto Range. Comunque conosco un pochino di VB ma non sono riuscito a capire come funziona
. Pensi che dovrebbe funzionare anche sull'intero dataset di 500000 righe senza eliminare righe per errore ?
Se le righe duplicate sono consecutive si. Quanto ci ha messo?
Ho paura su 500.000 righe......
Comunque buona norma sempre fare un backup prima
>>>Quello che voglio è l'eliminazione delle righe duplicate dall'insieme dei dati (circa 500000 righe o record per il 2019).
NOn ho letto bene, un attimo
.....EDIT
Prima di >>>For X = 2 To Ur
metti >>>Range("AA" & Ur).NumberFormat = "@"Solo per formattare in testo............, da 2146 record ad 213 record
Non credo che Tu possa "identificare" due righe uguali in colonna AASe le righe duplicate sono consecutive si. Quanto ci ha messo?
Ci ha messo 1-2 secondi. Ti faccio sapere per 500000 righe
Vorrei dare il mio contributo con questa piccola procedura che restituisce una Collection di valori, passato in argomento un range (poi dalla Collection dovrebbe essere facile recuperare i valori univoci).
Non ho idea dei tempi, non ho fatto prove soprattutto con numeri cosi' alti.`Option Explicit Function remove_dups(r As Range) As Collection Dim source As String, has_many As Boolean Dim v As Variant, k As Variant, col As Collection Set col = New Collection v = Application.Transpose(r) source = vbNullChar & Join(v, vbNullChar) & vbNullChar For Each k In v If Trim(k) <> "" Then has_many = Len(Replace(source, k, k & "*")) - Len(source) > 1 If Not has_many Then col.Add k End If Next Set remove_dups = col End Function `Sul mio povero pc questa routine ha impiegato meno di 20 secondi per trattare un range di valori ripetuti di oltre 610000 righe, e sul medesimo intervallo ha impiegato 60 secondi per tracciare dei valori molto piu' random ottenuti con la formula =CASUALE().
Questo comunque e' un buon esercizio per scossa... chissa' se si sente invogliato a provarci 🙂
Vorrei dare il mio contributo con questa piccola procedura che restituisce una Collection di valori, passato in argomento un range (poi dalla Collection dovrebbe essere facile recuperare i valori univoci).
Non ho idea dei tempi, non ho fatto prove soprattutto con numeri cosi' alti.Grazie. Domani provo anche la tua.
A me invece servirebbe che "vecchio frac" mi spieghi come avviare una Function.
Ps. Hai notato la colonna AA in formatto testo?
>>>Quello che voglio è l'eliminazione delle righe duplicate dall'insieme dei dati (circa 500000 righe o record per il 2019).
Ciao.
Ho riprovato ma eseguendo la routine mi lascia solo 213 righe delle 2146 righe iniziali, contenenti ancora i duplicati (e ci fai caso le prime due righe alla colonna L hanno entrambe "Chlorophyll a" e nella colonna O "0,19"; inoltre mancano tutte le righe con Month=12.
Nella colonna AA al secondo rigo c'è una stringa che contiene tutti i valori delle celle dalla colonna A a V della riga 2 e che si ripete nelle sottostanti 212 righe.
Ti allego il file per vedere il prodotto del tuo codice e per verificare se ho eseguito bene le tue istruzioni.
Non conoscevo il metodo RemoveDuplicates dell'oggetto Range, non si finisce mai di imparare
Buongiorno.
Allora, sull'insieme dei dati (536741) la tua routine ha rimosso ben 19584 duplicati e ha impiegato un tempo su cui purtroppo non posso essere preciso. Di sicuro meno di 30 minuti ma più di 10'.
Potresti modificare la routine per piacere in modo che i duplicati eliminati dal Foglio1 sia copiati in un nuovo foglio "Duplicati", in modo da fare qualche verifica sui duplicati ?
Potresti inserire un frame in cui si visualizza il numero delle righe processate o la percentuale dei dati elaborati in runtime in modo da vedere il progressivo lavoro svolto, sempre se è una cosa semplice, sennò lascia perdere. E' più importante copiare/trasferire i duplicati eliminati in un foglio a parte per le verifiche.
Grazie mille
Grazie
Option ExplicitFunction remove_dups(r As Range) As CollectionDim source As String, has_many As BooleanDim v As Variant, k As Variant, col As Collection Set col = New Collection v = Application.Transpose(r) source = vbNullChar & Join(v, vbNullChar) & vbNullChar For Each k In v If Trim(k) <> "" Then has_many = Len(Replace(source, k, k & "*")) - Len(source) > 1 If Not has_many Then col.Add k End If Next Set remove_dups = col End Function
Scusami ma non so come eseguire una Funcion e dove copiare il codice (in una macro ? in un modulo ?)
Due errori da parte mia (prova questo)
1) Mettevo pure ID
2) Non pulivo il MSG prima di un nuovo recordPs. Aggiungi Foglio2 (avrai i record filtrati)
`Option Explicit Sub Duplicati_2() Dim msg As String, X As Long, Y As Long, Ur As Long Sheets("Foglio2").Cells.Clear Sheets("Foglio1").Activate Ur = Range("A" & Rows.Count).End(xlUp).Row Range("A1:V" & Ur).Copy Sheets("Foglio2").Activate Range("A1").PasteSpecial Range("W1") = "MSG" Columns("W:W").NumberFormat = "@" For X = 2 To Ur msg = "" For Y = 2 To 22 'non concateno ID perchè unico msg = msg & Cells(X, Y) Next Y Range("W" & X) = msg Next X Range("$A$1:$W$2147").RemoveDuplicates Columns:=23, Header:=xlYes Range("A1").Activate MsgBox "Fatto" End Sub`Option ExplicitSub <span class="token function">Duplicati_2</span><span class="token punctuation">(</span><span class="token punctuation">)</span>Dim msg As String<span class="token punctuation">,</span> X As Long<span class="token punctuation">,</span> Y As Long<span class="token punctuation">,</span> Ur As Long<span class="token function">Sheets</span><span class="token punctuation">(</span><span class="token string">"Foglio2"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Cells<span class="token punctuation">.</span>Clear<span class="token function">Sheets</span><span class="token punctuation">(</span><span class="token string">"Foglio1"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>ActivateUr <span class="token operator">=</span> <span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A"</span> <span class="token operator">&</span> Rows<span class="token punctuation">.</span>Count<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">End</span><span class="token punctuation">(</span>xlUp<span class="token punctuation">)</span><span class="token punctuation">.</span>Row<span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A1:V"</span> <span class="token operator">&</span> Ur<span class="token punctuation">)</span><span class="token punctuation">.</span>Copy<span class="token function">Sheets</span><span class="token punctuation">(</span><span class="token string">"Foglio2"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Activate<span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A1"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>PasteSpecial<span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"W1"</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">"MSG"</span><span class="token function">Columns</span><span class="token punctuation">(</span><span class="token string">"W:W"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>NumberFormat <span class="token operator">=</span> <span class="token string">"@"</span>For X <span class="token operator">=</span> <span class="token number">2</span> To Ur msg <span class="token operator">=</span> <span class="token string">""</span> For Y <span class="token operator">=</span> <span class="token number">2</span> To <span class="token number">22</span> 'non concateno ID perchè unico msg <span class="token operator">=</span> msg <span class="token operator">&</span> <span class="token function">Cells</span><span class="token punctuation">(</span>X<span class="token punctuation">,</span> Y<span class="token punctuation">)</span> Next Y <span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"W"</span> <span class="token operator">&</span> X<span class="token punctuation">)</span> <span class="token operator">=</span> msgNext X<span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"$A$1:$W$2147"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>RemoveDuplicates Columns<span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">23</span><span class="token punctuation">,</span> Header<span class="token punctuation">:</span><span class="token operator">=</span>xlYes<span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A1"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>ActivateMsgBox <span class="token string">"Fatto"</span>End SubPurtroppo mi da errore di run-tim 1004
Potresti modificare la routine per piacere in modo che i duplicati eliminati dal Foglio1 sia copiati in un nuovo foglio "Duplicati", in modo da fare qualche verifica sui duplicati ?
Potresti inserire un frame in cui si visualizza il numero delle righe processate o la percentuale dei dati elaborati in runtime in modo da vedere il progressivo lavoro svolto
Dovrei aver fatto tutto.
Prova e fammi sapere
>>>Purtroppo mi da errore di run-tim 1004
Per quale motivo ???
Dove hai trovato quella papardella gialla sopra ???Potresti modificare la routine per piacere in modo che i duplicati eliminati dal Foglio1 sia copiati in un nuovo foglio "Duplicati", in modo da fare qualche verifica sui duplicati ?
Potresti inserire un frame in cui si visualizza il numero delle righe processate o la percentuale dei dati elaborati in runtime in modo da vedere il progressivo lavoro svolto
Ciao.
Purtroppo dopo aver premuto il pulsante il programma si impalla. Nella cella con lo 0% non avviene nulla. Strano perché prima in 1 secondo eliminava i duplicati.
Mi dispiace
Ti allego due versioni:
Una con la progress bar e una senza.
I tempi con la scrittura delle righe nel nuovo foglio sono diventati biblici, con la versione con la progress bar addirittura il tempo va misurato in ERE........
Vedi te, ma con 500000 righe la vedo improponibile...
Io meglio di così non riesco.
Ti allego due versioni:
Come mai richiede così tempo la scrittura in un nuovo foglio delle righe duplicate ed eliminate dal Foglio1 ?
Della bar posso fare a meno.
Ti allego due versioni:
Se eventualmente rendesse più veloce la routine, potresti, per ogni riga duplicata che va eliminata, inserire un valore 1 nella prima colonna vuota a destra alla riga della corrispondente coppia di duplicati che non viene eliminata.
Non so, se è chiaro
Login
Registrati
