Sottrarre due valori su una colonna quando viene ripetuta l'occorrenza di un codice in un'altra

Question

Ciao a tutti,

non so se è possibile fare quello che mi sono proposto attraverso una formula. Ci ho provato usando le funzioni FILTRO, SE, CONTA.SE, ecc. ma purtroppo devo alzare bandiera bianca. Vi spiego:

Ho la tabella A:S.

Nella colonna B sono presenti dei codici seriali identificativi.

Nella colonna C dei nickname (non dovrebbero essere utili ai fini del calcolo).

Nella colonna I ho dei timestamp formattati così: gg/mm/yyyy oo:mm. (anche se nella barra delle formule sono contenuti anche i secondi, importantissimi ai fini del calcolo che mi serve).

Ora, mi servirebbe calcolare (nelle righe di una nuova colonna) ogni volta che c'è un doppione sulla colonna B di un codice seriale che si ripete per due (può capitare) o tre volte (raro), quanto è l'intervallo di tempo che passa tra il successivo timestamp e quello precedente (espresso nel formato mm:ss): quindi per esempio, se il primo codice si trova in B39 e quello successivo che è identico in B40, bisogna fare I40 meno I39. Se il codice seriale non si ripete mai nella colonna B allora va bene inserire testo vuoto "".

(ulteriore dettaglio che non dovrebbe servire ma lo dico: quando un seriale è doppiato cambia sempre anche il nickname).

Piccolo problema: a volte i codici seriali non si trovano uno dopo l'altro sulle righe. Ma potrebbe essere che tra i due doppioni ci sia uno o più codici seriali diversi.

Se non sono stato chiaro su quello che mi serve chiedetemi pure i dettagli che possono servire.

Grazie mille

Answer 1

Ciao, per quanto riguarda la mia formula originaria, prima del chiarimento, lo sfasamento, come ti ha anticipato gianfranco55-Office365, dipende dal fatto che hai fatto partire la formula dalla riga 2 e non da riga 3 (questo perché in realtà il primo valore, per come era pensato l'inserimento, non poteva dare mai un risultato.

Dopo l'ulteriore specificazione ho riscritto la formula che segue e che mi pare corrisponda al risultato atteso (e a quello fornito dalla ingegneristica formula di Eleuterio :-) ):

=SE(RIGHE($B$2:B2)=RIGHE($B$2:$B$27);"";SE.ERRORE(SCARTO(I2;CONFRONTA(B2;B3:$B$27;0);0)-I2;""))

Fatta questa precisazione alla fin fine ne sei venuto a capo ed è quello che conta :)

Ciao e buona giornata.

Answer 2

Aggiungo una cosa, se chiaramente possibile (e mi scuso se non lo ho detto prima).

Mi sarebbe utile anche su un'altra colonna una formula in cui per ogni seriale unico viene inserita la lettera "Y" (che sta per yes), mentre per quelli duplicati, la "Y" deve trovarsi solo in corrispondenza dell'ultimo (in ordine di tempo) mentre in quelli precedenti vorrei che comparisse una "N" (no).

Sostanzialmente chiedo perché non so come far capire ad Excel con una formula che se mi trova qualcosa nelle righe successive, DOPO, allora deve mettermi un valore ("N").

Grazie per la pazienza

Alternativa solo in Q2

=LET(p;PERRIGA(B2:I27;LAMBDA(r;LET(s;SCEGLI.COL(r;1);d;SCEGLI.COL(r;-1);SE(CONFRONTA(s;B1:B27;0)=RIF.RIGA(r);"";d-MAX((I2:I27<d)*(B2:B27=s)*I2:I27)))));STACK.ORIZ(p;SE((CONTA.SE(B2:B27;B2:B27)=1)+(PERRIGA(B2:B27;LAMBDA(r;CONTA.SE(B2:r;r)=CONTA.SE(B2:B27;r))));"Y";"N")))

Visualizza nella colonna R le Y e le N.

Un saluto a chi mi ha preceduto,

ciao.

edit

ho visto dopo la modifica in corsa dell'OP, la rifaccio perché anch'io avevo capito di metterla nella riga successiva

=LET(p;PERRIGA(B2:B27;LAMBDA(r;SE(CONTA.SE(r:B27;r)-1;CERCA.VERT(r;SCARTO(r:I27;1;);8;0)-SCARTO(r;;7);"")));STACK.ORIZ(p;SE((CONTA.SE(B2:B27;B2:B27)=1)+(PERRIGA(B2:B27;LAMBDA(r;CONTA.SE(B2:r;r)=CONTA.SE(B2:B27;r))));"Y";"N")))

Answer 3

Ciao gianfranco55-Office365 siamo in due a non capire bene.

Comunque, una differenza ci sarebbe tra la mia formula e la tua.

Per l'ultima occorrenza io calcolo 46 secondi andando a prendere la data/ora del duplicato immediatamente precedente (partendo dal fondo).

Tu calcoli 1m22" poiché prendi, partendo dall'alto, il primo, che nell'esempio, andando a ritroso, è il secondo precedente.

Qual è il risultato desiderato però può dircelo solo l'OP.

Nel frattempo io ho provato a modificare così la formula da indicare da Q2 fino a Q27:

=SE(RIGHE($B$2:B2)=RIGHE($B$2:$B$27);"";SE.ERRORE(SCARTO(I2;CONFRONTA(B2;B3:$B$27;0);0)-I2;""))

ma non sono certo che sia quanto desiderato.

Answer 4

Ciao per avere i differenziali di tempo io avrei pensato ad una formula del genere da inserire dalla cella Q3 e copiare in basso:

=SE.ERRORE(I3-SCARTO(I3;-(RIGHE($B$2:B3)-CONFRONTA.X(B3;SCARTO(B3;-RIGHE($B$2:B2);0;RIGHE($B$2:B2));0;-1)););"")

Answer 5

ciao

vediamo se ho capito

in Q2 da tirare in basso

=SE(CONTA.SE($B$2:$B2;B2)>1;CERCA.X(B2;$B$2:$B2;$I$2:$I2;"";0;-1)-CERCA.X(B2;$B$2:$B2;$I$2:$I2;"";0;1);"")

dove vuoi

=SE(CONTA.SE($B$2:$B2;$B2)=MAX(CONTA.SE($B$2:$B$30;$B2));"Y";"N")

Answer 6

Se non sono stato chiaro su quello che mi serve chiedetemi pure i dettagli che possono servire.

Tutto chiaro, ma serve il file per poterci lavorare, condividilo con OneDrive.

Viste le funzioni citate usi 2021 o 365, si può fare con le formule o con Power Query (oltre che VBA, ma non mi sembra necessario), scegli la metodologia, nel secondo caso i dati devono essere in tabelle e non intervalli semplici.

Ciao.