Convalida con filtro
Paolo
il problema che vorrei risolvere è il seguente.
Da A1 ad A100 c’è un elenco di nomi che ogni tanto si ripetono anche più di
una volta.
In B1 “mi piacerebbe” avere una cella a discesa, tipo convalida dati, con
l’elenco dei nomi del range A1:A100 riportati una sola volta.
Esempio:
A
1 pippo
2 pluto
3 paperino
4 pluto
5 pluto
In B1 elenco di solo 3 nomi in quanto pluto è ripetuto più di una volta.
xls2003 sp3.
Grazie
Roberto
Sicuramente esisterà una soluzione più brillante, ma intanto vedi se
questa soluzione può esserti utile:
1) con filtro avanzato, spuntando "copia unica dei record", ottieni
l'elenco dei nomi ripetuto solo una volta; questo elenco deve essere
posizionato in un'altra parte del foglio di lavoro;
2) in B1: convalida dati>elenco, indicando la zona del foglio dove
compare l'elenco già filtrato.
Roberto
MAUROAP
"Paolo" ha scritto:
> In B1 elenco di solo 3 nomi in quanto pluto è ripetuto più di una volta.
> xls2003 sp3.
>
Ciao paolo ovviamente l'elenco globale non si puo ripulire dai doppioni
magari appoggiandolo su una tabella a parte ... vero?
Paolo
vi ringrazio per le risposte.
Per Roberto,
ho già fatto tante altre volte quello che mi hai suggerito, ma erano file a
mio uso eslusivo. Ora invece devo darlo ad altre persone a cui chiedere di
fare una somma con xls è come chiedere di progettare lo shuttle e si
perderebbero dopo l'inserimento dell'= della funzione somma().
Per MAUROAP,
la risposta è simile a quella data a Roberto se per appoggiarmi su una
tabella a parte intendi una soluzione simile a quella da lui proposta. Se
invece è qualcosa di diverso e gestibile dinamicamente possiamo parlarne.
Ovviamente il range a1:a100 è solo un esempio, è molto più esteso.
Inoltre al range ho dato un nome e l'estensione del nome di tale elenco
avviene automaticamente avendo inserito la seguente formula nella definizione
= scarto(a1;0;0;conta.numeri($a:$a);1)
Non vi chiedo, se esiste, una soluzione o tutta VBA o mista VBA+formule in
quanto dovrebbe funzionare tutto in automatico e chi riceve il file potrebbe
non accettare l'esecuzione di macro.
Grazie ancora e fatemi sapere se avete qualche idea.
Paolo
"Roberto" ha scritto:
> .
>
eliano
"Paolo" ha scritto:
> ho già fatto tante altre volte quello che mi hai suggerito, ma erano file a
> mio uso eslusivo. Ora invece devo darlo ad altre persone a cui chiedere di
> fare una somma con xls è come chiedere di progettare lo shuttle e si
> perderebbero dopo l'inserimento dell'= della funzione somma().
>
> Non vi chiedo, se esiste, una soluzione o tutta VBA o mista VBA+formule in
> quanto dovrebbe funzionare tutto in automatico e chi riceve il file potrebbe
> non accettare l'esecuzione di macro.
>
Ciao Paolo.
Mi sembra strano che prima tu parli di utenti talmente utonti da ingripparsi
su una funzione Somma() e poi dici che dovrebbe funzionare tutto in
automatico.
Probailmente non ho afferrato la situazione, comunque un modo per costruirsi
con modica spesa :-)) un elenco ordinato da usare in una convalida potrebbe
essere questo.
In un file di prova, contenente in colonna A qualche centinaio di
nominativi, copia questa artigianale in un Modulo standard, ad esempio
Modulo1:
Public Sub copiaunivociUNO()
'copia univoci da colonna A a colonna B
'uso improprio dell'errore 1004 da vlookup
Dim Y As Range, Wks As Object
Dim Z, W
Dim ruga As Long, riga As Long, UR As Long
Set Wks = Worksheets("Foglio1")
UR = Wks.Range("A" & Rows.Count).End(xlUp).Row
Set Y = Wks.Range("b1:b" & UR)
Y = ""
Application.ScreenUpdating = False
With Wks
For ruga = 1 To UR
If .Cells(ruga, 1) <> "" Then
Z = .Cells(ruga, 1)
On Error Resume Next
W = WorksheetFunction.VLookup(Z, Y, 1, False)
If Err.Number = 1004 Then
riga = riga + 1
.Cells(riga, 2) = Z
End If
End If
Next
End With
Y.Sort Key1:=Y(1), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Y(1).Select
End Sub
Lancia la macro, verifica il funzionamento e facci sapere.
Se funziona secondo le tue esigenze, considera anche la possibilità, in caso
di utenti un po' "svogliati", di lanciare quella macro in maniera trasparente
(si fa per dire) usando un qualsiasi Evento del file, magari un evento Change
nel caso ad esempio di aggiunta di nuovi nominativi all'elenco.
Saluti
Eliano
Paolo
la macro scrive giustamente 1 volta sola in colonna B i nominativi presenti
anche più volte in colonna A e va bene; il range di colonna B lo definisco
dinamicamente con la funzione Scarto($B$1;0;0;conta.numeri($b:$b);1) e
successivamente il nome del range lo utilizzo nella convalida.
Avevo già pensato di eseguire la macro su un evento Change come mi hai
suggerito.
Mi sono spiegato sicuramente male e quando dicevo che mi sarebbe piaciuto
che funzionasse in "automatico" intendevo solo tramite funzioni xls già
pronte in modo che l'utente non dovesse accettare l'esecuzione di macro.
Comunque va bene ugualmente ed ho imparato un nuovo modo per determinare
l'ultima riga.
Di solito uso un modo equivalente di farlo e cioè
UR = Wks.Range("A65536").End(xlUp).Row
anziché
UR = Wks.Range("A" & Rows.Count).End(xlUp).Row
ma il tuo metodo Eliano è più flessibile e lo preferisco.
Ciao a tutti e alla prossima.
"eliano" ha scritto:
Nur
"Paolo" <Pa...@discussions.microsoft.com> ha scritto nel messaggio
news:FE7AF794-0A77-4372...@microsoft.com...
>
> Non vi chiedo, se esiste, una soluzione o tutta VBA o mista VBA+formule in
> quanto dovrebbe funzionare tutto in automatico e chi riceve il file
> potrebbe
> non accettare l'esecuzione di macro.
>
Ti propongo, in alternativa a quanto gi� proposto, una soluzione *senza* vb.
Chiamiamo per comodit� rng il tuo range di origine.
Scegli la prima cella del range dove vorrai avere l'elenco di univoci (anche
in un altro foglio), adesso supponiamo che sia C1.
Le formule che indicher� estraggono i valori univoci e li ordinano.
Per�, a seconda della presenza o meno in rng di valori numerici, testo o
entrambi contemporaneamente, le formule sono pi� o meno complesse.
Sono tutte matriciali, da inserire con ctrl+maisuc+invio in C1 e trascinare
in basso all'occorrenza (ad esempio per tante righe quante ce ne sono in
rng)
1) se rng contiene sia valori di testo che numerici:
=INDICE(rng;CONFRONTA(PICCOLO(SE(CONFRONTA(rng;rng;)=RIF.RIGA(INDIRETTO("1:"&RIGHE(rng)));CONTA.SE(rng;"<"&rng)+RIGHE(rng)*VAL.TESTO(rng));RIF.RIGA(A1));CONTA.SE(rng;"<"&rng)+RIGHE(rng)*VAL.TESTO(rng);))
2) se rng contiente *solo testo* (o solo numeri):
=INDICE(rng;CONFRONTA(PICCOLO(SE(CONFRONTA(rng;rng;)=RIF.RIGA(INDIRETTO("1:"&RIGHE(rng)));CONTA.SE(rng;"<="&rng));RIF.RIGA(A1));CONTA.SE(rng;"<="&rng);))
3) se rng contiene *solo numeri*:
=PICCOLO(SE(FREQUENZA(rng;rng);rng);RIF.RIGA(A1))
Scelta la formula, a seconda della tua situazione, poi definisci un nome, ad
esempio 'unici', con:
=SCARTO($C$1;;;CONTA.VALORI($C:$C)-CONTA.SE($C:$C;#NUM!))
Alla fine, nella convalida, userai come Origine: =unici
[E' probabile che alcune delle formule (le prime due...) potrebbero essere
migliorabili.]
Nessuna delle formule indicate funziona se in rng ci sono celle vuote.
Nel caso questa fosse un'evenutalit� possibile, va gestita (variandole).
Ciao, Nur
Paolo
belle anche queste tue proposte.
Adesso proverò 1 a capirle e 2 a provare a fare tutto senza una colonna
d'appoggio, se possibile, ed andare direttamente in convalida con un bel
formulone.
Ciao e alla prossima.
"Nur" ha scritto:
>
> "Paolo" <Pa...@discussions.microsoft.com> ha scritto nel messaggio
> news:FE7AF794-0A77-4372...@microsoft.com...
> >
> > Non vi chiedo, se esiste, una soluzione o tutta VBA o mista VBA+formule in
> > quanto dovrebbe funzionare tutto in automatico e chi riceve il file
> > potrebbe
> > non accettare l'esecuzione di macro.
> >
>
> Ti propongo, in alternativa a quanto già proposto, una soluzione *senza* vb.
> Chiamiamo per comodità rng il tuo range di origine.
>
> Scegli la prima cella del range dove vorrai avere l'elenco di univoci (anche
> in un altro foglio), adesso supponiamo che sia C1.
>
> Le formule che indicherò estraggono i valori univoci e li ordinano.
> Però, a seconda della presenza o meno in rng di valori numerici, testo o
> entrambi contemporaneamente, le formule sono più o meno complesse.
> Sono tutte matriciali, da inserire con ctrl+maisuc+invio in C1 e trascinare
> in basso all'occorrenza (ad esempio per tante righe quante ce ne sono in
> rng)
>
> 1) se rng contiene sia valori di testo che numerici:
> =INDICE(rng;CONFRONTA(PICCOLO(SE(CONFRONTA(rng;rng;)=RIF.RIGA(INDIRETTO("1:"&RIGHE(rng)));CONTA.SE(rng;"<"&rng)+RIGHE(rng)*VAL.TESTO(rng));RIF.RIGA(A1));CONTA.SE(rng;"<"&rng)+RIGHE(rng)*VAL.TESTO(rng);))
>
> 2) se rng contiente *solo testo* (o solo numeri):
> =INDICE(rng;CONFRONTA(PICCOLO(SE(CONFRONTA(rng;rng;)=RIF.RIGA(INDIRETTO("1:"&RIGHE(rng)));CONTA.SE(rng;"<="&rng));RIF.RIGA(A1));CONTA.SE(rng;"<="&rng);))
>
> 3) se rng contiene *solo numeri*:
> =PICCOLO(SE(FREQUENZA(rng;rng);rng);RIF.RIGA(A1))
>
> Scelta la formula, a seconda della tua situazione, poi definisci un nome, ad
> esempio 'unici', con:
> =SCARTO($C$1;;;CONTA.VALORI($C:$C)-CONTA.SE($C:$C;#NUM!))
>
> Alla fine, nella convalida, userai come Origine: =unici
>
> [E' probabile che alcune delle formule (le prime due...) potrebbero essere
> migliorabili.]
>
> Nessuna delle formule indicate funziona se in rng ci sono celle vuote.
> Nel caso questa fosse un'evenutalità possibile, va gestita (variandole).
>
> Ciao, Nur
>
>
>
>
>
>
>
>
> .
>
Tiziano Marmiroli
>Ciao Nur,
>
>belle anche queste tue proposte.
A dir poco!
>Adesso prover� 1 a capirle e 2 a provare a fare tutto senza una colonna
>d'appoggio, se possibile, ed andare direttamente in convalida con un bel
>formulone.
Pu� darsi sia possibile ma IMHO � di una difficolt� 'impossibile'! :-)
--
Tiziano Marmiroli
Microsoft MVP - Office System
http://www.riolab.org
Paolo
quando dici "Puó darsi sia possibile ma IMHO é di una difficoltá
'impossibile'! :-) "
a cosa ti riferisci a provare a capirle oppure provare ad andare
direttamente in convalida con formulone?
Scherzo..., ho capito perfettamente che ti riferisci alla seconda possibilità.
Ciao a tutti.
"Tiziano Marmiroli" ha scritto:
> Il Fri, 13 Nov 2009 00:41:02 -0800, Paolo ha scritto:
>
> >Ciao Nur,
> >
> >belle anche queste tue proposte.
>
> A dir poco!
>
> >Adesso proverò 1 a capirle e 2 a provare a fare tutto senza una colonna
> >d'appoggio, se possibile, ed andare direttamente in convalida con un bel
> >formulone.
>
> Puó darsi sia possibile ma IMHO é di una difficoltá 'impossibile'! :-)
> --
> Tiziano Marmiroli
> Microsoft MVP - Office System
> http://www.riolab.org
> .
>