Ricerca righe per data

Hai un problema con Excel?

Nuova discussione

Ricerca righe per data
di av (utente non iscritto) data: 27/03/2014 15:03:46
Ho un foglio con circa 10000 righe così strutturato:
CODICECLIENTE DATACONTATTO
BCDFGH01A02A100G 02/01/2013
BCDFGH01A02A100G 28/01/2013
BCDFGH01A02A100G 04/03/2013
Vorrei contare i clienti che hanno avuto un contatto entro 30 giorni dal precedente.
Un saluto a tutti.
30 giorni
di canapone (utente non iscritto) data: 27/03/2014 15:55:30

Ciao,

non sono sicuro di aver capito

Nella colonna A (a2:a1000) i codici clienti in ordine crescente di data contratto (b2:b1000)

Potresti usare una colonna di servizio (la C) e scrivere in B2 e sotto

=--(SE.ERRORE(CERCA.VERT(A2;A3:$B$1000;2;0)-B2;0)>=30)

La somma della colonna C dovrebbe restituire i contratti distanziati di 30 giorni relativi allo stesso cliente.

Spero sia d'aiuto
di ninai (utente non iscritto) data: 27/03/2014 15:55:47
Ciao
la richiesta lascia dei dubbi per la risposta, sarebbe stato meglio avere più dati di esempio, magari attraverso un file.
Prova:
=MATR.SOMMA.PRODOTTO((A2:A10000=A1:A9999)*((B2:B10000-B1:B9999)>=30))

in A i codici
In B le date
di ninai (utente non iscritto) data: 27/03/2014 16:12:43
Canapone
Ci siamo accavallati per 17''

rimangono dei dubbi interpretativi ma credo che la tua risposta sia meglio mirata...., evita do conteggiare più volte lo stesso codice.

Ma forse il criterio deve essere <30 o <=30
>30
di canapone (utente non iscritto) data: 27/03/2014 16:27:04
Ciao Ninai,

ho usato il cerca.vert pensando ad database ordinato in ordine cronologico (solo per data).

E' più probabile che sia sufficiente >30 nella formula.

Saluti
<30
di canapone (utente non iscritto) data: 27/03/2014 16:29:32

Ciao di nuovo,

ho scritto di nuovo maggiore > 30 - lapsus calami- intendevo il contrario

=--(SE.ERRORE(CERCA.VERT(A2;A3:$B$1000;2;0)-B2;0)<30)

Saluti
di av (utente non iscritto) data: 27/03/2014 18:01:08
Buonasera
Ringrazio molto per le risposte fornite. Ho preparato un file di esempio di 124 righe estratto dall'originale di circa 10000 righe sul quale ho applicato le soluzioni proposte. Quella di ninai mi restituisce un totale di 26 (su 124) contatti ripetuti entro 30 giorni, mentre contati manualmente risultano essere 58; invece la soluzione di canapone mi fornisce una serie di 0 e 1 che non so come interpretare.
Come posso postare il file di esempio preparato?
Grazie ancora per la vostra pazienza........
di av (utente non iscritto) data: 27/03/2014 18:49:27
Ciao ancora,
ho rianalizzato le due soluzioni applicate;
quella di canapone (col quale mi scuso per non aver letto la parte finale del post, in effetti la somma degli 0 e 1 totalizza i contatti ripetuti) è interessante in quanto evidenzia nella colonna C, apponendo 1, i contatti ripetuti che possono essere così verificati. C'è però un piccolo problema: considera vero la riga sottostante tenendo conto solo della data; in realtà la riga sottostante potrebbe appartenere ad un altro cliente, per cui dovrebbe confrontare anche il codice cliente delle due righe e non solo la data. Il risultato è una sovrastima: infatti ottengo 97 su 124 anziché 58 su 124.
La soluzione di ninai è comoda in quanto fornisce il dato cercato in una sola cella; così come proposta fornisce 26 su 124, mentre sostituendo > con < a fine formula fornisce 50 su 125 anziché 58 su 124.
Come posso ovviare?
Grazie e saluti
CERCA.VERT
di canapone data: 27/03/2014 19:58:57
Ciao,

il cerca.vert cerca il primo codice cliente corrispondente e ne restituisce la data per confrontarla con la data subito sulla sinistra della formula stessa. Se la differenza e' inferiore a 30, la formula restituisce 1.

Esempio: formula in C2, cerca nel range A3:$B$1000 (A3 libero, $B$1000 bloccato) la prima corrispondenza con A2 (potrebbe trovarla in qualsiasi celle in A3 A100) per restituire la data. Se non trova il cliente restituisce 0.
La ricerca parte sempre dalla prima riga sotto sino alla riga 1000.

Per allegare il file trovi un tasto in alto a sinistra.

Spero sia d'aiuto
di av (utente non iscritto) data: 27/03/2014 22:57:23
Ciao,
allego il file esempio. Sono corretti gli uno da c2 a c6, ma non capisco gli 1 da c8 a c15.
Nella colonna E ho calcolato, salvo errori, i contatti entro 30 giorni per ciascun cliente.
Grazie ancora.
RICERCA DATA
di Canapone (utente non iscritto) data: 28/03/2014 04:26:54
Ciao,

ho allegato il file exempioDATEORDINATE.

Ho modificato le formule

=SE.ERRORE(--(CERCA.VERT(A2;A3:$B$124;2;0)-B2<30);0)

Le date della colonna C devono essere in ordine crescente: b2 sottratto a cerca.vert non deve produrre un risultato negativo, che sarebbe considerato come buono (1) dalla formula.

Spero di ragionare bene.
DATEORDINATE2
di canapone data: 28/03/2014 06:51:25
Ciao,

ho riallegato il file: nell'ordinare la colonna B non avevo ottenuto quanto mi aspettavo.

Saluti
di ninai (utente non iscritto) data: 28/03/2014 07:53:14
Non certo di aver capito, do la mia interpretazione:
ordinando prima per codice e data crescente:
in C2: =(A2=A3)*(B3-B2)

trascinata in basso, restituisce la durata in giorni dei contratti 8con la formattazione condizionale si possono evidenziare quelli inferiori a 300 gg)

in D2:
=SE(A3=A2;"";MATR.SOMMA.PRODOTTO(($A$2:A2=A2)*($C$2:C2<30)*($C$2:C2>0)))
al cambio codice , restituisce la somma dei contratti inferirori a 30 gg.

allego file contratti conta
di av (utente non iscritto) data: 28/03/2014 21:59:09
Buonasera,
anche se in ritardo ringrazio entrambi per le soluzioni proposte e i file di esempio proposti. Sebbene non precisissime (la prima fornisce sul set di esempio un risultato di 33, la seconda di 30) permettono comunque di eseguire il calcolo con una stima approssimativa del 2-3%. Vorrei chiedere ancora una cosa: a Canapone come ha ordinato per data crescente e perchè ha avuto necessità di trasferire le date in un'altra colonna. A ninai come ordinare per codice e data crescente.
Così posso provare a fare l'elaborazione sul set completo che contiene circa 10.000 righe.
Grazie ancora e un
ciao o voi.............
clienti differenza date <30 giorni
di canapone data: 29/03/2014 05:23:35
Ciao,

grazie del riscontro - e saluto subito Ninai.

Prova ad ordinare in ordine crescente la colonna delle date (colonna B): i risultati non sono quelli attesi.
Un sistema veloce per capire se Excel sta lavorando su delle date è usare la combinazione control+maiusc+!.
Seleziona la colonna B e premi control+maiusc+!: se stiamo lavorando sulle stesso file poche "date" si trasformano nel numero corrispondente.
Un sistema per poterci lavorare potrebbe essere aggiungere un paio di colonne vuote alla destra della colonna B, selezionare la colonna B, selezionare dal menù File "Testo in colonne". Si aprirà la finestra di conversione, seleziona larghezza fissa, clicca su avanti, imposta la larghezza dei campi su 10 e clicca su fine.
Formatta la colonna B come date.
Riprova i dati ordina.

Per capire perchè le formule facciano una stima e non il proprio lavoro avrei bisogno di qualche esempio.

Saluti
di Raffaele_53 (utente non iscritto) data: 29/03/2014 12:31:15
Ciao a tutti
>>> Per capire perchè le formule facciano una stima e non il proprio lavoro avrei bisogno di qualche esempio.

Nel files Esempiodateordinate2 le formula in C3 è C124 sono errate
Ps. stranissimo il conportamento della cella C123 ricopiata in C124
da =SE.ERRORE(--(CERCA.VERT(A123;A124:$B$124;2;0)-B123<30);0)
in =SE.ERRORE(--(CERCA.VERT(A124;A$124:$B125;2;0)-B124<30);0)
di av (utente non iscritto) data: 29/03/2014 23:23:23
Ciao ancora,
in effetti il file dateordinate2 allegato mostra degli errori nelle formule; probabilmente sono scaturiti durante la manipolazione dello stesso un po' laboriosa per quanto riguarda l'ordinamento per data. Utilizzando la procedura descritta da Canapone "testo in colonne" si riesce a trasformare le stringhe in date e poi ordinarle. Oltre agli errori segnalati da Raffaele_53 segnalo un errore anche nella formula al rigo 84. Per cui la differenza di 3 ottenuta a prima vista con le due procedure era probabilmente dovuta a ciò.
Ad onor del vero ho applicato la procedura (ordinamento per data crescente ed applicazione formula) sul mio primo file che avevo postato ed il risultato ottenuto (e verificato riga per riga) è analogo a quello ottenuto con l'altra soluzione proposta da ninai ossia 30 con entrambe le procedure.
Questo mi lascia piu' tranquillo nell'applicare le soluzioni al file originale di circa 10.000 righe.
Ancora un grazie a tutti e
alla prossima..............
di av (utente non iscritto) data: 29/03/2014 23:48:27
Ho allegato EsempioBis con l'appicazione delle formule.............

di Raffaele_53 (utente non iscritto) data: 30/03/2014 05:41:19
Complimenti per le formule, per farlo un pò piu leggero ho fatto una macro
Quando sei nel foglio premi ALT + F11 e incollalo sulla parte destra.
Ora se vuoi puoi fargli un Bottone e assegnarli il codice oppure avviandolo col triangolo verde il alto.

Ps. Come da esempio, Il foglio si deve chiamare Foglio1 e si parte dalla seconda riga.

Option Explicit
Sub calcola()
Dim X As Long, Y As Long, Uriga As Long
Uriga = Range("A" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add Key:=Range("A2:A" & Uriga _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Foglio1").Sort
        .SetRange Range("A1:B" & Uriga)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For X = 2 To Uriga
            For Y = X + 1 To Uriga
                If Cells(X, 1) = Cells(Y, 1) Then
                    If Cells(X, 2) > (Cells(Y, 2) - 30) Then
                        Cells(X, 3) = 1
                    End If
                Else
                    Exit For
                End If
            Next Y
    Next X
    Cells(1, 3) = "Totale = " & Application.WorksheetFunction.CountIf(Range("C2:C" & Uriga), 1)
    MsgBox ("fatto")
End Sub

di av (utente non iscritto) data: 30/03/2014 23:58:48
Ciao Raffaele_53.
Grazie per la soluzione proposta che funziona molto bene se applicata all'ultimo file postato già ordinato in crescendo per codice e data.
Funziona male invece se applicata sul file Esempio in quanto restituisce dapprima un errore di Tipo non corrispondente in quanto non tutte le stringhe della colonna B vengono riconosciute come date; questo si risolve facilmente con il consiglio di Canapone ("Testo in colonna") che trasforma le stringhe in date. Applicando ora la macro restituisce un valore errato di 57 anzichè 30 in quanto le date non vengono ordinate in crescendo per cliente.
Volevo chiedere se fosse possibile costruire un ciclo for next che partendo dalla prima riga incorpori X=codice e Y=data in due variabili che vengano poi confrontate con le righe sottostanti e se X=X e Y=Y<30 scriva 1 nella colonna C; dopochè passa alla seconda riga, incorpora X e Y le confronta con le righe sottostanti, così fino alla penultima riga in quanto l'ultima riga confrontata con se stessa darebbe sempre 1.
In tal modo il risultato dovrebbe essere sempre garantito indipendentemente dal preventivo ordinamento della matrice per data crescente, elemento comune ed imprescindibile (ed in qualche modo limitante) a tutte le soluzioni proposte (formule e macro).
Saluti.

di Raffaele_53 (utente non iscritto) data: 31/03/2014 12:52:27
Uso anche le colonne C-D-E e mi sembra che desideri l'ordine precedente.
Una volta avviato il codice, si ferma senza rimettere in ordine (in modo che tu possa esaminare il tutto), se un domani va bene puoi togliere la spunta alla parte del "riordino".Però per la prima volta dovrai chiudere il files senza salvarlo e riaprirlo (i dati non sono ordinati, ho messo una riga che te lo vieta, se desideri fai un riordino manuale tramite la colonna "E",cancellando poi le colonne C-D-E).

Ps. Come fai a contare i "Ripetuti per cliente"?

Option Explicit
Sub calcola()
Dim X As Long, Y As Long, R As Long, Uriga As Long, DD As Date, Codice As String
If Cells(2, 5) <> "" Then Exit Sub 'finche non metti il riordino dei record
Dim sh1 As Worksheet: Set sh1 = Worksheets("Foglio1") ' da cambiare casomai
Uriga = sh1.Range("A" & Rows.Count).End(xlUp).Row
sh1.Range("C1:E" & Uriga).ClearContents
sh1.Columns("C:E").NumberFormat = "General"
    sh1.Cells(1, 4) = "Ripetuti per cliente"
    sh1.Cells(1, 5) = "Progressivo"
    For X = 2 To Uriga
        DD = Left(sh1.Cells(X, 2), 10)
        sh1.Cells(X, 2) = DD
        sh1.Cells(X, 5) = X
    Next X
    sh1.Sort.SortFields.Clear
    sh1.Sort.SortFields.Add Key:=Range("A2:A" & Uriga), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    sh1.Sort.SortFields.Add Key:=Range("B2:B" & Uriga), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With sh1.Sort
        .SetRange Range("A1:E" & Uriga)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For X = 2 To Uriga
    R = 0
    Codice = sh1.Cells(X, 1)
            For Y = X + 1 To Uriga
                If sh1.Cells(Y, 1) = Codice Then
                    If sh1.Cells(X, 2) > sh1.Cells(Y, 2) - 30 Then
                        sh1.Cells(X, 3) = 1
                        R = R + 1
                     End If
                Else
                    If sh1.Cells(Y, 1) <> Codice Then If R <> 0 Then sh1.Cells(X, 4) = sh1.Cells(X, 4) + R
                    Exit For
                End If
            Next Y
    Next X
        sh1.Cells(1, 3) = "Totale = " & Application.WorksheetFunction.CountIf(Range("C2:C" & Uriga), 1)
        '--------------------Riordino, eliminare la4 riga in alto
        'sh1.Sort.SortFields.Clear
        'sh1.Sort.SortFields.Add Key:=Range("E1:E" & Uriga), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'With sh1.Sort
        '.SetRange Range("A1:E" & Uriga)
        '.Header = xlYes
        '.MatchCase = False
        '.Orientation = xlTopToBottom
        '.SortMethod = xlPinYin
        '.Apply
    'End With
    MsgBox ("fatto")
Set sh1 = Nothing
End Sub

codice
di av (utente non iscritto) data: 31/03/2014 17:08:03
Ciao ancora Raffaele,
ringrazio per il tempo dedicato. Ho applicato il codice ultimo che mi hai postato e va benissimo. Lavora anche senza un preventivo riordino della matrice per codice cliente e data crescenti e ora mi fornisce anche troppe informazioni. Le colonne D ed E sono superflue. Quello che mi interessa è avere un conteggio dei contatti ripetuti (per cui va benissimo la cella C1 con il totale che restituisce 30 in accordo con tutte le altre formule applicate). Può anche andare bene l'apposizione di 1 nella colonna C in corrispondenza dei contatti ripetuti in modo che, qualora fosse richiesta una verifica, si possa presentare. Però, ripeto, lo scopo iniziale era il semplice conteggio dei contatti ripetuti entro 30 giorni. Sul conteggio dei ripetuti per cliente preciso che nel primo file (Esempio.rar) che ho postato li avevo conteggiati manualmente contando 2 il ripetuto entro 30 giorni. Se vedi il file che allego (EsempioVB) il cliente 1 (così come ora ordinato) ha due contatti (il 30 e 31-12-2013). Nel primo file li avevo conteggiati 2, mentre le procedure proposte li conteggiano 1 conteggiando il primo come tempo 0 e ripetuto solo il secondo; può andare bene anche così, basta intendersi. Il cliente 2 ha tre contatti il 24-04, il 24-09 e il 22-10. Io li contavo 2 ripetuti (quelli del 24-09 e del 22-10) mentre le procedure li contano 1 (quello del 22-10 anche se 1 viene apposto su quello del 24-09, così come sul primo cliente viene apposto sul contatto del 30-12 mentre quello ripetuto è quello del 31-12 ma va bene uguale, concettualmente è identico).
Infine, senza voler abusare della tua pazienza e del tuo tempo, ti vorrei chiedere di semplificare l'ultimo codice che hai spedito lasciando solo il totale in C1 e l'apposizione degli 1 nella colonna C in corrispondenza dei contatti ripetuti entro 30 giorni. Il riordino della matrice non mi serve nè prima nè dopo l'applicazione del codice, al limite lo faccio io manualmente se volessi fare una verifica dopo l'applicazione del codice. Tieni conto che il file Esempio è solo un piccolo estratto, quello vero che debbo elaborare è di circa 10.000 righe per cui non è che posso conteggiare uno per uno tutti i contatti ripetuti, mi fido del codice che appare a questo punto comodo ed affidabile. Al limite, se ti va, puoi scriverne un altro con le stesse caratteristiche che ho esposto sopra, ma che conteggi 2 i contatti ripetuti del primo cliente (entrambi), 2 i contatti ripetuti del secondo cliente (il secondo e il terzo) e così via discorrendo.....
Ancora tante grazie e tanti saluti.

di Raffaele_53 (utente non iscritto) data: 31/03/2014 23:38:53
Dovrebbe essere

Option Explicit
Sub calcola()
Dim X As Long, Y As Long, R As Long, Uriga As Long, DD As Date, Codice As String
Dim sh1 As Worksheet: Set sh1 = Worksheets("Foglio1") ' da cambiare casomai
Uriga = sh1.Range("A" & Rows.Count).End(xlUp).Row
sh1.Range("C1:C" & Uriga).ClearContents
sh1.Columns("C:C").NumberFormat = "General"
    For X = 2 To Uriga
        DD = Left(sh1.Cells(X, 2), 10)
        sh1.Cells(X, 2) = DD
    Next X
    sh1.Sort.SortFields.Clear
    sh1.Sort.SortFields.Add Key:=Range("A2:A" & Uriga), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    sh1.Sort.SortFields.Add Key:=Range("B2:B" & Uriga), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With sh1.Sort
        .SetRange Range("A1:C" & Uriga)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For X = 2 To Uriga
    Codice = sh1.Cells(X, 1)
            For Y = X + 1 To Uriga
                If sh1.Cells(Y, 1) = Codice Then
                    If sh1.Cells(X, 2) > sh1.Cells(Y, 2) - 30 Then
                        sh1.Cells(X, 3) = 1
                      End If
                Else
                    Exit For
                End If
            Next Y
    Next X
        sh1.Cells(1, 3) = "Totale = " & Application.WorksheetFunction.CountIf(Range("C2:C" & Uriga), 1)
    MsgBox ("fatto")
Set sh1 = Nothing
End Sub

di av (utente non iscritto) data: 01/04/2014 22:06:45
Buonasera,
obiettivo centrato.
Grazie a tutti per la collaborazione.
Alla prossima.

Vuoi Approfondire?