Rearrange Data to show Duplicates?

[SpreadsheetNoob]

Hi All,

I need to rearrange data so that it shows duplicates rather than only unique values. For example, data that shows qualifications:

Category	Subcategory	John	Bob	Rich
Microsoft	Excel	X		X
Microsoft	Word		X	X

I want to change it to:

Category	Subcategory	Name
Microsoft	Excel	John
Microsoft	Word	Bob
Microsoft	Excel	Rich
Microsoft	Word	Rich

Not sure if this is possible, I tried using transpose, but it obviously doesn't move the way I want. Thanks for any help everyone!

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Category"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] = "X" then null else [Value]),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "Subcategory")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Name"}, {"Custom", "Subcategory"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Category", "Subcategory", "Name"})
in
    #"Reordered Columns"

Book1
	A	B	C	D	E
1	Category	Subcategory	John	Bob	Rich
2	Microsoft	Excel	X		X
3	Microsoft	Word		X	X
4
5	Category	Subcategory	Name
6	Microsoft	Excel	John
7	Microsoft	Excel	Rich
8	Microsoft	Word	Bob
9	Microsoft	Word	Rich
Sheet1

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is a macro that you could also try with a copy of your workbook.

Sub Qualifications()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a) * (UBound(a, 2) - 2), 1 To 3)
  For j = 3 To UBound(a, 2)
    For i = 2 To UBound(a)
      If Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(1, j)
      End If
    Next i
  Next j
  With Cells(1, Columns.Count).End(xlToLeft).Offset(, 2)
    .Offset(1).Resize(k, 3).Value = b
    .Resize(, 3).Value = Array("Category", "Subcategory", "Name")
    .CurrentRegion.Columns.AutoFit
  End With
End Sub

My sample data and results of the above code.

SpreadsheetNoob.xlsm
	A	B	C	D	E	F	G	H	I
1	Category	Subcategory	John	Bob	Rich		Category	Subcategory	Name
2	Microsoft	Excel	X		X		Microsoft	Excel	John
3	Microsoft	Word		X	X		Microsoft	Word	Bob
4							Microsoft	Excel	Rich
5							Microsoft	Word	Rich
6
Sheet1

 

[kokano90]

Try it

Option Explicit
Sub ABC()
    Dim sArr(), Res(), i&, j&, K&
    With Sheet1
        sArr = .Range("A1").CurrentRegion.Value
    End With
    ReDim Res(1 To (UBound(sArr, 1) - 1) * (UBound(sArr, 2) - 2) + 1, 1 To 3)
    K = 1
    For i = 2 To UBound(sArr, 1)
        For j = 3 To UBound(sArr, 2)
            If UCase(sArr(i, j)) = "X" Then
                K = K + 1
                Res(K, 1) = sArr(i, 1)
                Res(K, 2) = sArr(i, 2)
                Res(K, 3) = sArr(1, j)
            End If
        Next
    Next
    If K Then
        Res(1, 1) = "Category": Res(1, 2) = "Subcategory": Res(1, 3) = "Name"
        Sheet2.UsedRange.ClearContents
        Sheet2.Range("A1").Resize(K + 1, 3).Value = Res
    End If
End Sub

 

[SpreadsheetNoob]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Category"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] = "X" then null else [Value]),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "Subcategory")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Name"}, {"Custom", "Subcategory"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Category", "Subcategory", "Name"})
in
    #"Reordered Columns"

Book1
	A	B	C	D	E
1	Category	Subcategory	John	Bob	Rich
2	Microsoft	Excel	X		X
3	Microsoft	Word		X	X
4
5	Category	Subcategory	Name
6	Microsoft	Excel	John
7	Microsoft	Excel	Rich
8	Microsoft	Word	Bob
9	Microsoft	Word	Rich
Sheet1

This was exactly what I needed! Thanks a lot!