Hoe cellen in een kolom transponeren op basis van unieke waarden in een andere kolom?

Stel dat u een gegevensbereik heeft dat twee kolommen bevat, nu wilt u cellen in één kolom transponeren naar horizontale rijen op basis van unieke waarden in een andere kolom om het volgende resultaat te krijgen. Heeft u goede ideeën om dit probleem in Excel op te lossen?

Transponeer cellen in één kolom op basis van unieke waarden met formules

Transponeer cellen in één kolom op basis van unieke waarden met VBA-code

Transponeer cellen in één kolom op basis van unieke waarden met Kutools for Excel

Transponeer cellen in één kolom op basis van unieke waarden met formules

Met de volgende matrixformules kunt u de unieke waarden extraheren en de bijbehorende gegevens in horizontale rijen transponeren, doe dit als volgt:

1. Voer deze matrixformule in: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, AANTAL.ALS ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) in een lege cel, bijvoorbeeld D2, en druk op Shift+Ctrl+Enter toetsen samen om het juiste resultaat te krijgen, zie screenshot:

Opmerking:: In de bovenstaande formule, A2: A16 is de kolom waarvan u de unieke waarden wilt weergeven, en D1 is de cel boven deze formulecel.

2. Sleep vervolgens de vulgreep naar de cellen om alle unieke waarden te extraheren, zie screenshot:

3. En ga dan verder met het invoeren van deze formule in cel E2: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0), en vergeet niet om op te drukken Shift+Ctrl+Enter toetsen om het resultaat te krijgen, zie screenshot:

Opmerking:: In bovenstaande formule: B2: B16 zijn de kolomgegevens die u wilt transponeren, A2: A16 is de kolom waarop u de waarden wilt transponeren op basis van, en D2 bevat de unieke waarde die u in stap 1 heeft geëxtraheerd.

4. Sleep vervolgens de vulgreep naar rechts van de cellen waarvan u de getransponeerde gegevens wilt weergeven totdat 0 wordt weergegeven, zie screenshot:

5. En ga vervolgens door met het slepen van de vulgreep naar het celbereik om de getransponeerde gegevens te krijgen zoals in het volgende screenshot:

Transponeer cellen in één kolom op basis van unieke waarden met VBA-code

Misschien zijn de formules ingewikkeld om te begrijpen, hier kunt u de volgende VBA-code uitvoeren om het gewenste resultaat te krijgen dat u nodig hebt.

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: transponeer cellen in één kolom op basis van unieke waarden in een andere kolom:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Druk vervolgens op F5 toets om deze code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren het gegevensbereik te selecteren dat u wilt gebruiken, zie screenshot:

4. En klik vervolgens op OK knop, verschijnt er een ander promptvenster om u eraan te herinneren een cel te selecteren om het resultaat te plaatsen, zie screenshot:

6. Klikken OK knop, en de gegevens in kolom B zijn getransponeerd op basis van unieke waarden in kolom A, zie screenshot:

Transponeer cellen in één kolom op basis van unieke waarden met Kutools for Excel

Als je Kutools for Excel, het combineren van de Geavanceerd Combineer rijen en Gespleten cellen hulpprogramma's, kunt u deze taak snel voltooien zonder formules of code.

Na het installeren van Kutools for Excelgaat u als volgt te werk:

1. Selecteer het gegevensbereik dat u wilt gebruiken. (Als u de originele gegevens wilt behouden, kopieer en plak de gegevens dan eerst op een andere locatie.)

2. Dan klikken Kutools > Samenvoegen en splitsen > Geavanceerd Combineer rijen, zie screenshot:

3. In de Combineer rijen op basis van kolom dialoogvenster, voer dan de volgende bewerkingen uit:

(1.) Klik op de kolomnaam waarop u gegevens wilt transponeren op basis van en selecteer Hoofdsleutel;

(2.) Klik op een andere kolom die u wilt transponeren, en klik Combineren Kies vervolgens een scheidingsteken om de gecombineerde gegevens te scheiden, zoals spatie, komma, puntkomma.

4. Dan klikken Ok knop, zijn de gegevens in kolom B gecombineerd in één cel op basis van kolom A, zie screenshot:

5. En selecteer vervolgens de gecombineerde cellen en klik Kutools > Samenvoegen en splitsen > Gespleten cellen, zie screenshot:

6. In de Gespleten cellen dialoogvenster, selecteer Splitsen in kolommen onder de Type optie en kies vervolgens het scheidingsteken dat uw gecombineerde gegevens scheidt, zie screenshot:

7. Dan klikken Ok knop en selecteer een cel om het gesplitste resultaat in het uitgeklapte dialoogvenster te plaatsen, zie screenshot:

8. Klikken OK, en je krijgt het resultaat zoals je nodig hebt. Zie screenshot:

Download en gratis proefversie Kutools for Excel Nu!

Demo: transponeer cellen in één kolom op basis van unieke waarden met Kutools for Excel

---

De beste tools voor kantoorproductiviteit

Kutools for Excel Lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

• visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
• Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
• Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
• Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
• Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
• Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
• Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
• Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
• Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2021 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functies Gratis proefperiode van 30 dagen. 60 dagen geld-terug-garantie.

Lees verder... Gratis download... Aankoop... 

---

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

• Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
• Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
• Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!

Lees verder... Gratis download... Aankoop...