Értékek kinyerése az ismétlődések (pl. duplikációk) eltávolításával

Az értékek kilistázása az ismétlődések eltávolításával nem mindig egyszerű és gyors folyamat. Az alábbiakban erre a problémára kínálok különféle módszereket:

1. Emlékszem, mikor még csak tanulgattam az Excelt, először szűrőt raktam az oszlopra és a szűrőlistából néztem meg az értékeket (angolul “visual check” ami sokkal jobban hangzik :-)):

2. Majd jött a Kimutatás (Pivot table): itt a C oszlopban látható az eredmény

3. Később megismertem a Tömbképletet (Array Formula). Egy példa az ismétlődések kiszűrésére:

Angol verzió:

=IFERROR(INDEX($A$1:$A$7,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$7),0)),"")

1	=IFERROR(INDEX($A$1:$A$7,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$7),0)),"")

Magyar verzió:

=HAHIBA(INDEX($A$1:$A$7,HOL.VAN(0,DARABTELI($C$1:C1,$A$1:$A$7),0)),"")

1	=HAHIBA(INDEX($A$1:$A$7,HOL.VAN(0,DARABTELI($C$1:C1,$A$1:$A$7),0)),"")

A tömbképleteknél Enter helyett CTRL+SHIFT+ENTER-t kell nyomnunk miután hozzáadtuk a formulát az adott cellához:

4. Következő megoldás az Adatok menüből az “Ismétlődések eltávolítása” (Data – Remove Duplicates):

5. Szintén egy lehetőség az Adatok menüből az Irányított szűrő (Data – Advanced):

Látható, hogy a fejléc meglétét nem lehet beállítani, így az első sornál lévő szöveg megmarad, vagyis egy duplikáció azért van.

6. Soron következő alternatíva – inkább érdekességképpen – a Kezdőlap menüben található Feltételes formázás (Home – Conditional Formatting):

Ez is teszi a dolgát, azonban az egyedi értékeket vagy ismétlődéseket jelöli ki. Ez azonban nem pont az, amire szükségem van, hiszen a végső listában a “hatékonyság” valamint az “energia” szavak nincsenek kiemelve (ami a Feltételes Formázás logikája szerint teljesen rendben van). Ám ha ki is lennének a megfelelő értékek emelve, akkor még szűrnöm kellene az oszlopot szín szerint majd kimásolni az értékeket.

7. Microsoft 365 (korábbi nevén Office 365) előfizetőkenk: EGYEDI (UNIQUE), ami egy tömbképlet, vagyis a képlet tömböt (ha úgy tetszik, itt tartományt) ad vissza. Bővebb infó itt. Ízelítő:

8. Végül de korántsem utolsó sorban a saját VBA megoldásom:

Az adott munkafüzetbe szúrjunk be egy modult a Visual Basic szerkesztőjében és az alábbi kódot adjuk hozzá:

Sub IsmetlodesekEltavolitasa()

Dim D As Object, Jelol As Range, i As Long, X, Szovegsor As String, Cella As Range
Dim Dataobj As Object

'Készítette: XLMotyo (https://xlmotyo.hu/)

'Early Binding:
    '- deklarálás: "Dim Dataobj As DataObject" (kitörölni: "Dim Dataobj As Object"-et)
    '- "DataObj"-het beállítani: Tools- References- "Microsoft Forms 2.0 Object Library" elõtt pipát betenni.
'Late Binding: ez a kód

'választható tartomány vagy teljes sor/oszlop illetve ezek kombinációja (CTRL billentyûvel)

'a kód az értékeket hagyja meg (mint a Kimutatásnál azaz Pivot Table-nél csak az üres értékek nélkül),
'az ismétlések kiszûrésével, majd az eredményt a vágólapra másolja ahonnan beilleszthetjük a kívánt helyre

'Late Binding:a kód hozzáadja a DataObject-et, így nem kell manuálisan hozzáadnunk a "Microsoft Forms 2.0 Object Library"-t
Set Dataobj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

Set D = CreateObject("Scripting.Dictionary")

'értékeket tartalamzó tartomány kijelölése
On Error GoTo Vege
Set Jelol = Application.InputBox("Jelöld ki a tartományt, ahol az ismétléseket el akarod távolítani!", , , , , , , 8)
On Error GoTo 0

Application.ScreenUpdating = False

'egyedi értékek beolvasása tömbbe
For Each Cella In Jelol.Cells
    If Cella <> "" Then D(CStr(Cella.Value)) = ""
Next Cella

X = D.keys
'egyedi értékek összefûzése
For i = 0 To D.Count - 1
    Szovegsor = Szovegsor & vbNewLine & X(i)
Next i
Szovegsor = Mid(Szovegsor, 3)

'összefûzött szöveg kimásolása vágólapra
With Dataobj
    .SetText Szovegsor
    .PutInClipboard
End With

MsgBox D.Count & " egyedi érték lett a vágólapra másolva", vbInformation, ""

Application.ScreenUpdating = True

Vege:
End Sub

Sub IsmetlodesekEltavolitasa()

Dim D As Object, Jelol As Range, i As Long, X, Szovegsor As String, Cella As Range

Dim Dataobj As Object

'Készítette: XLMotyo (https://xlmotyo.hu/)

'Early Binding:

'- deklarálás: "Dim Dataobj As DataObject" (kitörölni: "Dim Dataobj As Object"-et)

'- "DataObj"-het beállítani: Tools- References- "Microsoft Forms 2.0 Object Library" elõtt pipát betenni.

'Late Binding: ez a kód

'választható tartomány vagy teljes sor/oszlop illetve ezek kombinációja (CTRL billentyûvel)

'a kód az értékeket hagyja meg (mint a Kimutatásnál azaz Pivot Table-nél csak az üres értékek nélkül),

'az ismétlések kiszûrésével, majd az eredményt a vágólapra másolja ahonnan beilleszthetjük a kívánt helyre

'Late Binding:a kód hozzáadja a DataObject-et, így nem kell manuálisan hozzáadnunk a "Microsoft Forms 2.0 Object Library"-t

Set Dataobj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

Set D = CreateObject("Scripting.Dictionary")

'értékeket tartalamzó tartomány kijelölése

On Error GoTo Vege

Set Jelol = Application.InputBox("Jelöld ki a tartományt, ahol az ismétléseket el akarod távolítani!", , , , , , , 8)

On Error GoTo 0

Application.ScreenUpdating = False

'egyedi értékek beolvasása tömbbe

For Each Cella In Jelol.Cells

If Cella <> "" Then D(CStr(Cella.Value)) = ""

Next Cella

X = D.keys

'egyedi értékek összefûzése

For i = 0 To D.Count - 1

Szovegsor = Szovegsor & vbNewLine & X(i)

Next i

Szovegsor = Mid(Szovegsor, 3)

'összefûzött szöveg kimásolása vágólapra

With Dataobj

.SetText Szovegsor

.PutInClipboard

End With

MsgBox D.Count & " egyedi érték lett a vágólapra másolva", vbInformation, ""

Application.ScreenUpdating = True

Vege:

End Sub

Fenti makró jellegzetességei:

az értékeket hagyja meg az ismétlések kiszűrésével, majd az eredményt a vágólapra másolja, ahonnan beilleszthetjük a kívánt helyre
választható tartomány vagy teljes sor/oszlop illetve ezek kombinációja (CTRL billentyűvel)
itt a tartományt és/vagy a sort/oszlopot a kód futtatásakor kell kijelölni, nem előtte

Szerinted melyik opciók a leghatékonyabbak?
Szólj hozzá lent vagy dobj egy emailt: xlmotyo@gmail.com