cerca.vert con più corrispondenze

Question

Salve,

ho una colonna con delle sigle (sono geni), in un'altra tabella ho queste sigle associate a diversi dati (nomi di malattie)

l'annoso problema è che per ogni gene possono corrispondere più malattie e cerca.vert mi fornisce solo il primo risultato.

Come posso trovare anche gli altri risultati? (eventualmente mi piacerebbe concatenare i vari risultati)

Grazie

Answer 1

Ciao Filippi,

Grazie, perfetto, poi me la studio che mi piacerebbe impararla.

Mi fa piacere che tu abbia risolto il problema e ti ringrazio per il cortese riscontro.

Per chiudere questo thread, vorrei chiederti gentilmente di contrassegnare la mia risposta come Risposta. In questo modo, tu aiuterai anche coloro che potessero cercare soluzioni ai problemi simili negli archivi della Community.

Dato che c'ero ho esteso la ricerca ad altre parametri oltre alle malattie.

Mi è servita per circa 7000 righe e 20 colonne

In tal caso, se fosse per me, adotterei la funzione utente!

Alla prossima.

===

Regards,

Norman

Answer 2

Ciao Filippo,

ho una colonna con delle sigle (sono geni), in un'altra tabella ho queste sigle associate a diversi dati (nomi di malattie)

l'annoso problema è che per ogni gene possono corrispondere più malattie e cerca.vert mi fornisce solo il primo risultato.

Come posso trovare anche gli altri risultati? (eventualmente mi piacerebbe concatenare i vari risultati)

Poniamo che la prima colonna della tabella delle geni e malattie sia denominata Geni e che la seconda colonna sia denominata Malattie.

Poniamo anche che i geni di interesse siano immessi nell'intervallo D2:Dn.

Nella cella E2, immetti la seguente formula:

=SE.ERRORE(INDICE(Malattie;PICCOLO(SE(Geni=$D2;RIF.RIGA(Malattie)-MIN(RIF.RIGA(Malattie))+1);COLONNE($D$2:D2)));"")

Questa è una formula matriciale che deve esere confermata con Ctrl+Maisc+Invio.

Trascina la formula in basso quanto necessario e trascinarla a destra finchè le celle delle successive colonne diventino vuote.

Per ottenere i risultato in forma concatenata, nella cella J2, immetti una formula del genere:

=E2&"   " &F2&"   "&G2&"   "&H2&"   "&I2

Eventualmente nasconda le colonne E:I.

Se vorresti ottenere i risultati in forma concatenata, senza l'uso delle colonne intermedie, e con una sola semplice formula del tipo =Malattie(D2), posterò una funzione utente (UDF) che può essere utilizzata come una funzione nativa di Excel.

Potresti scaricare il mio file di prova Filippo20170824.xlsx

===

Regards,

Norman

Answer 3

Ciao Filippo,

Se vorresti ottenere i risultati in forma concatenata, senza l'uso delle colonne intermedie, e con una sola semplice formula del tipo =Malattie(D2), posterò una funzione utente (UDF) che può essere utilizzata come una funzione nativa di Excel.

In attesa della tua risposta, non potevo resistere creare la funzione utente (UDF) ElencareMalattie!

Quindi, ricominciando da capo:

• crea un nome definito (TabellaMalattie) per la tua tabella
• Alt+F11 per aprire l'editor di VBA
• Alt+IM per inserire un nuovo modulo di codice
• Nel nuovo modulo vuoto, incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Public Function ElencareMalattie(sStr, TabellaGeni) As Variant

    Dim oDic As Object

    Dim arrTabella As Variant

    Dim arrKeys As Variant, arrItems As Variant

    Dim aStr As String, bStr As String

    Dim Res As Variant

    Dim i As Long, iItems As Long

    Const sSeperatore As String = " / " '<<=== Modifica

    Set oDic = Nothing

    Set oDic = CreateObject("Scripting.Dictionary")

    arrTabella = TabellaGeni.Value

    With oDic

        .CompareMode = vbTextCompare

        For i = 1 To UBound(arrTabella)

            aStr = arrTabella(i, 1)

            bStr = arrTabella(i, 2)

            If Not .exists(aStr) Then

                .Add Key:=aStr, Item:=bStr

            Else

                .Item(aStr) = .Item(aStr) & sSeperatore & bStr

            End If

        Next i

        arrKeys = .keys

        arrItems = .items

        iItems = .Count

    End With

    Res = Application.Match(sStr, arrKeys)

    If Not IsError(Res) Then

        ElencareMalattie = arrItems(Res - 1)

    Else

        ElencareMalattie = CVErr(xlErrNA)

    End If

End Function

'<<========= 

• Alt+Q per chiudere l'editor di VBA e tornare a Excel
• Salva il file con l’estensione xlsm

La funzione sarebbe confermata con un semplice Invio e sarebbe utilizzata così:

=ElencareMalattie(D2,TabellaMalattie)

Potresti scaricare il mio nuovo file di prova aggiornato Filippo20170824.xlsm

In questo file, ho dimostrato sia la soluzione originale con le formule native di Excel che la soluzione corrente con la UDF (funzione utente). Inoltre, Per rendere dinamicamente possibile l'espansione o la contrazione della tabella, la tabella TabellaMalattie è una tabella di Excel.

===

Regards,

Norman

Answer 4

Grazie, perfetto, poi me la studio che mi piacerebbe impararla.

Dato che c'ero ho esteso la ricerca ad altre parametri oltre alle malattie.

Mi è servita per circa 7000 righe e 20 colonne