VLOOKUP



  • VLOOKUP di SouthWind data: 12/12/2016 16:01:55

    Ciao.
    Domanda.
    Ma la VLOOKUP posso utilizzarla solo avendo il range di ricerca in prima e seconda colonna?
    Se ho la mia chiave di ricerca in terza colonna e il dato che mi occorre prelevare in quarta colonna, il comando non sembra funzionare....
    Grazie 1000.


     
    ho la chiave di ricerca (cioè il dato con cui comparare CODICE) in prima colonna e il dato che devo estrarre in colonna 2, con Lookup_Range = RANGE("A":"B") --> FUNZIONA.
    PIPPO = Application.WorksheetFunction.VLookup(CODICE, Lookup_Range, 2, False)
    
    ho la chiave di ricerca (cioè il dato con cui comparare CODICE) in terza colonna e il dato che devo estrarre in colonna 4, con Lookup_Range = RANGE("C":"D") --> NON FUNZIONA.
    PIPPO = Application.WorksheetFunction.VLookup(CODICE, Lookup_Range, 4, False)


  • di alfrimpa data: 12/12/2016 16:09:00

    No il VLookup è il CERCA.VERT lato VBA per cui la colonna di ricerca, se vuoi usare questa funzione, deve essere sempre la prima.

    Per il tuo scopo credo ti convenga usare il metodo Find.

    Magari se alleghi un file di esempio spiegando quel che vuoi fare potrò essere più preciso.

    Alfredo



  • di Luca73 data: 12/12/2016 17:33:22

    Ciao a tutti
    qualcuno tempo fa mi insegno il trucchetto sotto riportato per usare il cerca.vert con colonne non vicine e non in serie.
    Chiedo perdono ma non ricordo che e non posso pertanto adeguatamente ringraziarlo.

    Praticamente si genera una matrice con scegli le parentesi graffe che creano un vettore.
    L'esempio qui sotto riportato utilizza F:F come prima colonna (quella doce cercare) e D:D come seconda da cui prende i risultati.
    la formula è matriciale e pertanto va confermata con CTRL+SHIFT+Enter
    =CERCA.VERT(H9;SCEGLI({1,2};F:F;D:D);2;0)
    in VBA la stessa si presenta come:

    Ciao
    Luca 
     
    Selection.FormulaArray = "=VLOOKUP(RC[-1],CHOOSE({1,2},C[-3],C[-5]),2,0)"
    
    Selection.FormulaArray = "=VLOOKUP(H9,CHOOSE({1,2},F:F,D:D),2,0)"


  • di alfrimpa data: 12/12/2016 17:53:24

    Grazie Luca

    Io con le formule sono una capra per cui mi sono permesso di consigliare il
    Find.

    Alfredo


  • di SouthWind (utente non iscritto) data: 12/12/2016 19:10:35

    grazie Luca ed Alfredo.
    Domattina proverò entrambe le soluzioni.
    La valutazione nasce anche dai tempi di risposta.
    Ho un foglio con 30.000 righe e un foglio in cui cercare i dati di curca 290 righe.
    Ovviamente la soluzione che non utilizzerò sarà conservata come caso scuola.
    Grazie ancor e buona serata.


  • di alfrimpa data: 12/12/2016 19:16:11

    Ciao SouthWind ed un caro saluto a Luca.

    Secondo me, nel tuo caso, sarebbe meglio utilizzare il metodo Find ben più versatile e potente rispetto alla soluzione con formule.

    Ma su questo vorrei sapere anche il parere di Luca (sicuramente più esperto di me).

    Alfredo


  • di Luca73 data: 13/12/2016 08:25:57

    Ciao SouthWind a ciao Alfredo.
    Premesso che non è vero che sono più esperto di Alfredo, comunque concordo che se usi VBA stai sul lato VBA (ovvero io eviterei di usare una formula di Excel) che ti dà sicuramente più flessibilità.

    Io personalmente userei un find o (essendo meno esperto di Alfredo) un banalissimo ciclo For Each...Next
    Ciao
    Luca


  • di alfrimpa data: 13/12/2016 10:38:41

    Ciao Luca

    Cit.: "Premesso che non è vero che sono più esperto di Alfredo"

    No è proprio vero e lo si vede dalle soluzioni che proponi .

    Si penso anch'io che con un ciclo For...Each si possa ugualmente risolvere ma concordo che il Find è più potente e versatile (anche se io non ho molta dimenstichezza con questa istruzione e devo ancora "metabolizzarla" bene).

    Comunque, per poter individuare la migliore soluzione, occorrerebbe vedere un file di esempio di Southwind.

    Alfredo


  • di SouthWind data: 13/12/2016 18:27:37

    Eccomi.
    Premesso che il PIPPONE sono io, allego il file scremato da dati significativi.

    Il Foglio INPUT è quello che guida nel senso che è a partire da lui che va generato il contenuto del foglio RISULTATO.

    Il Foglio RISULTATO va valorizzato come indicato in seconda, terza e quarta riga del foglio stesso.

    Il Foglio ANAGRAFICA contiene le decodifiche.

    Da scartare il ciclo for next fino ad ora usato per scarse performance.

    La vlookup è una bomba ma non mi riesce di utilizzarla per la decodifica del Documento nel foglio RISULTATO.

    Mi pare sia tutto.

    Una preghiera: se postate del codice, per favore commentatelo il più possibile affinchè possa capire ed imparare e non copiare e basta.

    Grazie mille!

    Alberto.


  • di alfrimpa data: 14/12/2016 10:30:39

    Ciao Alberto

    Perdonami ma continuo a non capire.

    Tu dici: "Il Foglio INPUT è quello che guida nel senso che è a partire da lui che va generato il contenuto del foglio RISULTATO."

    Ma in che modo il foglio INPUT deve fare da guida? Non vedo nessun nesso tra il foglio INPUT ed il foglio ANAGRAFICHE.

    Puoi spiegare meglio?


  • di SouthWind data: 14/12/2016 10:59:10

    Ciao Alfredo.
    Sicuramente mi sono espresso male.

    Il foglio INPUT guida, nel senso che bisogna leggerlo sequenzialmente dalla prima all'ultima riga.
    Esso contiene in colonna A il tipo Documento (in realtà a me interessa estrarre e decodificare solo i caratteri presenti fino al primo "_") ed in colonna B il codice Società.

    Al termine della procedura, il foglio RISULTATO sarà popolato con lo stesso numero di righe presenti nel foglio INPUT ma con in più la Denominazione Societaria (in colonna A) e la descrizione del tipo Documento (in colonna C).

    Denominazione Societaria e Documento li desumo dal foglio ANAGRAFICA (dove si trovano rispettivamente in colonna B e D) attraverso la relazione che c'è con il codice della Società ed il tipo Documento prelevati dal foglio INPUT.

    Procedura:

    1) leggo sequenzialmente INPUT
    2) per ogni riga letta, cerco DENOMINAZIONE SOCIETA e DOCUMENTO (coll. B e D) nel foglio ANAGRAFICA mettendo in relazione il codice SOCIETA e il tipo DOCUMENTO (coll. B e A) del foglio INPUT con il CODICE SOCIETA ed il DOCUMENTO (coll. A e C - attenzione! qui ho solo i primi caratteri del tipo Documento, ma non mi pare sia un grosso problema).
    3) alimento il foglio RISULTATO come segue:
    Colonna A: da Colonna B del foglio ANAGRAFICA con la regola descritta al punto 2
    Colonna B: indifferentemente da Colonna B del foglio INPUT o da Colonna A del foglio ANAGRAFICA
    Colonna C: da Colonna D del foglio ANAGRAFICA con la regola del punto 2
    4) torno al punto 1 ed eseguo gli stessi passi fino alla fine delle righe del foglio INPUT.

    Spero di non aver confuso ulteriormente le idee.

    Ripeto che escludo il ciclo "for next", oggi in uso, per le scarse prestazioni offerte.

    Grazie mille.
    Alberto.


  • di alfrimpa data: 14/12/2016 14:07:08

    Alberto immaginiamo che sul foglio INPUT ci sia solo un record questo

    dp__73578_000.zip 1

    Tu dici di voler cercare le prime due lettere (ossia dp) nel foglio ANAGRAFICA.

    Ma dp su ANAGRAFICA non c'è proprio

    Puoi fare un esempio con 2/3 record su INPUT e su RISULTATO inserisci manualmente quello che dovrebbe venir fuori?

    Alfredo


  • di SouthWind data: 14/12/2016 14:50:50

    Hai ragione Alfredo.

    Nel tentativo di semplificare il foglio INPUT non ho bilanciato correttamente le informazioni.
    Ora dovrebbe essere tutto ok.
    Puoi tener conto del nuovo foglio RISULTATO ATTESO per avere un'idea della mia esigenza.

    Scusa e grazie ancora.
    Alberto.


  • di alfrimpa data: 14/12/2016 14:55:29

    Non c'è un nuovo file allegato

    Alfredo


  • di SouthWind data: 14/12/2016 15:14:37

    Ora c'è (Cartel3)
    In effetti prima avevo aggiornato il file lasciando lo stesso nome e poi l'ho allegato.
    Forse non riusciva a sovrascrivere l'allegato.
    Grazie.


  • di alfrimpa data: 14/12/2016 15:51:52

    Ok Alberto ora ci provo ma se vuoi scandire tutta la colonna A record per record credo che un ciclo For Each sia inevitabile.

    Dammi un po' di tempo.

    Alfredo


  • di alfrimpa data: 14/12/2016 17:44:43

    Ciao Alberto

    Comincio col proporti un primo file (Alberto.xlsm) dove ho inserito la macro che vedi sotto.

    In questa fase ho ancora utilizzato il CERCA.VERT() lato VBA che opera ovviamente su diverse tabelle di ricerca.

    Osservazione importante: affinchè il tutto funzioni è necessario che vi sia assoluta identità tra i codici "ID NOME FILE" presenti su "ANAGRAFICA" e le prime 4 lettere delle stringhe presenti in ogni cella della colonna A del foglio "INPUT" tant'è che ho dovuto fare qualche aggiustamento per rendere i due elenchi assolutamente uguali.

    Ho inserito un nuovo foglio "RISULTATO ATTESO_BIS" per fare le mie prove e dove ho inserito un pulsante che lancia la macro.

    Vedrai che il risultato che restituisce la macro è identico a quello che tu hai messo nel foglio "RISULTATO ATTESO".

    Dopo aver visto come funziona il file, inserisci nel foglio INPUT i dati reali e assicurati, come detto prima, la assoluta identità tra le prime 4 lettere della colonna A ed i codici presenti su ANAGRAFICA.

    Se questa assoluta identità c'è la macro dovrebbe restituire il risultato corretto ed in tal modo si potrà avere una stima anche sui tempi di esecuzione e valuteremo poi se lasciare le cose come stanno oppure passare all'utilizzo del Find (che come ho detto neanch'io conosco molto bene).

    Prova un po' e fammi sapere.

    Alfredo


     
    Sub EstraiDati()
    Dim ur As Long
    Dim lr As Long
    Dim fr As Long
    Dim rng As Range
    Dim cel As Range
    Dim tabDenSoc As Range
    Dim tabDocum As Range
    Application.ScreenUpdating = False
    ur = Worksheets("INPUT").Cells(Rows.Count, 1).End(xlUp).Row
    fr = Worksheets("ANAGRAFICA").Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Worksheets("INPUT").Range("B2:B" & ur)
    Worksheets("RISULTATO ATTESO_BIS").Range("a2:C50000").ClearContents
    Set tabDenSoc = Worksheets("ANAGRAFICA").Range("a2:b" & fr)
    Set tabDocum = Worksheets("ANAGRAFICA").Range("c2:d" & fr)
    For Each cel In rng
        lr = Worksheets("RISULTATO ATTESO_BIS").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 1).Value = Application.WorksheetFunction.VLookup(cel.Value, tabDenSoc, 2, False)
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 2).Value = cel.Value
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 3).Value = Application.WorksheetFunction.VLookup(Left(cel.Offset(0, -1).Value, 4), tabDocum, 2, False)
    Next cel
    Application.ScreenUpdating = True
    End Sub
    


  • di SouthWind data: 14/12/2016 18:08:37

    Ehhhhh... Alfredo, caffè pagatissimo fino a Natale!
    Ho lanciato la tua macro sul file di prova (quello allegato da te) e tutto fila a meraviglia.
    Non sono al lavoro e quindi rimando a domattina l'offerta anche del cappuccino nel caso (molto probabile) che funzioni anche con il file originale.
    Non sono certo di poter garantire l'uguaglianza che chiedi fino al quarto carattere incluso.
    Sui primi tre assolutamente si.
    In effetti è questa la prova di domattina.
    La preghiera è quella di commentare le righe di codce così che io possa capire ed imparare e non copiare bovinamente.
    Grazie ancora e a domani per le news.
    Alberto.
    PS. non c'è una possbilità "quasi risolta"?


  • di alfrimpa data: 14/12/2016 19:16:44

    Ciao Alberto

    Cit. "Ehhhhh... Alfredo, caffè pagatissimo fino a Natale!"

    Se sei nelle vicinanze di Napoli si potrebbe anche fare

    Cit. "Non sono certo di poter garantire l'uguaglianza che chiedi fino al quarto carattere incluso.
    Sui primi tre assolutamente si."

    Nel file che ti ho allegato (Alberto1.xlsm) ho modificato la macro affinché vengano presi in considerazione i primi tre caratteri; ho ovviamente modificato i codici del campo "ID NOME FILE" del foglio "ANAGRAFICA".

    Cit. "La preghiera è quella di commentare le righe di codce così che io possa capire ed imparare e non copiare bovinamente."

    Domani cerco di farlo.

    Se il tuo file "originale" completo non contiene dati sensibili sul foglio INPUT potresti allegarlo attraverso Filedropper o simili così posso testarlo anch'io.

    Alfredo


  • di alfrimpa data: 15/12/2016 11:01:47

    Ciao Alberto

    Con l'ultimo file ho lanciato la macro su circa 40.000 su tre colonne ed il tempo di esecuzione è stato (sul mio pc che è vecchiotto) di circa 20 secondi quindi direi - in questa situazione - più che accettabile.

    Se facessimo la stessa cosa in Access la farebbe in mezzo secondo.

    Alfredo


  • di alfrimpa data: 15/12/2016 12:08:19

    E Come Volevasi Dimostrare

    Ho ricreato il tutto con Access e per creare l'elenco (su una base di oltre 40.000 record nella tabella INPUT) ci ha messo meno di un secondo.

    Se vuoi potrei anche allegarti il file.

    Alfredo


  • di SouthWind data: 15/12/2016 12:47:40

    Grazie Alfredo.
    In realtà sono spesso in giro ma sono nato a Caserta...
    Non ho ancora avuto modo di sperimentare il codice sul file completo.
    Purtroppo il file contiene dati riservati, non sensibili, e quindi non posso condividerlo.
    La decodifica che mi hai inviato è uno degli step che prevede il processo dell'intero file.
    Non avevo dubbi che con un db relazionale le prestazioni sarebbero state super efficienti... ma chest'é!
    Nel pomeriggio provo a "calare" il tuo codice sull'intero file e vediamo cosa succede.
    Grazie mille.
    Alberto.


  • di alfrimpa data: 19/12/2016 23:20:59

    Alberto novita?

    Alfredo


  • di SouthWind data: 20/12/2016 10:27:17

    Ciao Alfredo.
    Sono giorni "caldi", siamo in piena chiusura amministrativa del 2016 e devo anteporre altre attività a quelle che riguardano la macro in discussione.
    In realtà, applicandola al file originale, mi restituisce un errore:
    "impossibile trovare la propreità VLookup per la classe WorksheetFunction"
    ed evidenzia l'istruzione:
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 1).Value = Application.WorksheetFunction.VLookup(cel.Value, tabDenSoc, 2, False).
    Ho allegato una copia del file originale; la sequenza delle colonne è leggermente diversa poichè ho aggiunto alcune colonne che non hanno alcun legame con la macro in esame.
    Non credo di aver corrotto il codice che mi hai suggerito... sta di fatto che non va.
    Il file in allegato è alberto2.xlsm e contiene già la tua macro.
    Che ne pensi?
    Grazie e scusa il ritardo della risposta.
    Alberto.


  • di alfrimpa data: 20/12/2016 10:56:38

    Ciao Alberto non intendevo assolutamente metterti fretta .

    L'errore che ricevi deriva dal fatto che il VLookup non riesce a trovare il dato e restituisce un messaggio di errore causato dal fatto che hai modificato la struttura del foglio e quindi il codice si trova a dover "lavorare" su intervalli diversi.

    E' del tutto normale che se si cambia la truttura del foglio va modificata, di conseguenza, anche la macro.

    Alfredo


  • di SouthWind data: 20/12/2016 11:44:37

    Grazie Alfredo.
    Pur avendo aggiunto colonne, non mi pare di aver modificato i riferimenti agli intervalli previsti dalla macro... potrei sbagliarmi anche perchè ho "intuito" il significato dei comandi in essa contenuti.
    Boh?
    Grazie di nuovo.


  • di alfrimpa data: 20/12/2016 18:13:19

    Ciao Alberto

    Prova con questo ultimo file allegato AlbertoUltimo1.xlsm.

    Ho avuto un grosso problema con le date in colonna E del foglio INPUT che date non erano bensì testo.

    Prova un po'.

    Alfredo
     
     
    Sub EstraiDati()
    Dim ur As Long
    Dim lr As Long
    Dim fr As Long
    Dim rng As Range
    Dim cel As Range
    Dim tabDenSoc As Range
    Dim tabDocum As Range
    Dim tabGiorni As Range
    Application.ScreenUpdating = False
    Worksheets("INPUT").Select
    Call TestoIndate
    Worksheets("RISULTATO ATTESO_BIS").Activate
    ur = Worksheets("INPUT").Cells(Rows.Count, 1).End(xlUp).Row
    fr = Worksheets("ANAGRAFICA").Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Worksheets("INPUT").Range("d2:d" & ur)
    Worksheets("RISULTATO ATTESO_BIS").Range("a2:C50000").ClearContents
    Set tabDenSoc = Worksheets("ANAGRAFICA").Range("a2:b" & fr)
    Set tabGiorni = Worksheets("Mesi").Range("a1:b7")
    Set tabDocum = Worksheets("ANAGRAFICA").Range("c2:d" & fr)
    For Each cel In rng
        lr = Worksheets("RISULTATO ATTESO_BIS").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 1).Value = Format(cel.Offset(0, 1).Value, "D mmmm")
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 2).Value = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.Text(Application.WorksheetFunction.Weekday(cel.Offset(0, 1).Value) + 1, "ddd"), tabGiorni, 2, False)
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 3).Value = Application.WorksheetFunction.VLookup(cel.Value, tabDenSoc, 2, False)
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 4).Value = cel.Value
        Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 5).Value = Application.WorksheetFunction.VLookup(Left(cel.Offset(0, -2).Value, 3), tabDocum, 2, False)
    Next cel
    Application.ScreenUpdating = True
    End Sub
    
    
    Sub TestoIndate()
    Dim ur As Long
    Dim cel As Range
    ur = Worksheets("INPUT").Cells(Rows.Count, "E").End(xlUp).Row
    For Each cel In Range("E2:e" & ur)
    cel.Value = CDate(cel.Value)
    Next cel
    End Sub
    


  • di SouthWind data: 04/01/2017 10:57:20

    Grazie Alfredo.
    Nel "marasma" di questi giorni ho dimenticato di comunicare che il tuo codice funziona perfettamente, le prestazioni sono ottime, sono molto soddisfatto!
    Colgo l'occasione per augurarti buon 2017 e occhio che sto per postare una nuova richiesta.
    Grazie ancora.
    Alberto.


  • di alfrimpa data: 04/01/2017 11:09:23

    Ciao Alberto

    Mi ero completamente dimenticato di questa discussione.

    Comunque sono lieto che tutto funzioni bene e che abbia risolto.

    Allora restiamo in attesa della tua nuova richiesta nella speranza di poterla risolvere come questa.

    Alfredo