Converting Data to Filterable format.

[awat17]

I have a spreadsheet that is not in a usable format and need help rearranging it.

It is currently formatted like the table below but with over 300 Kids and 20 clubs. This format has a lot of empty cells and its not easy to filter.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Club1[/TD]
[TD]Club2[/TD]
[TD]Club3[/TD]
[TD]Club4[/TD]
[TD]Club5[/TD]
[/TR]
[TR]
[TD]Kid1[/TD]
[TD][/TD]
[TD]Choice 1[/TD]
[TD][/TD]
[TD]Choice2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kid2[/TD]
[TD]Choice 1[/TD]
[TD][/TD]
[TD]Choice2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kid3[/TD]
[TD]Choice 2[/TD]
[TD]Choice 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kid4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Choice 2[/TD]
[TD][/TD]
[TD]Choice 1[/TD]
[/TR]
</tbody>[/TABLE]


I would like my table to look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Choice 1[/TD]
[TD]Choice 2[/TD]
[TD]Choice 3[/TD]
[/TR]
[TR]
[TD]Kid 1[/TD]
[TD]Club 1[/TD]
[TD]Club 2[/TD]
[TD]Club3[/TD]
[/TR]
[TR]
[TD]Kid 2[/TD]
[TD]Club 4[/TD]
[TD]Club 6[/TD]
[TD]Club 1[/TD]
[/TR]
[TR]
[TD]Kid 3[/TD]
[TD]Club 8[/TD]
[TD]Club 2[/TD]
[TD]Club 7[/TD]
[/TR]
[TR]
[TD]Kid 4[/TD]
[TD]Club 1[/TD]
[TD]Club 3[/TD]
[TD]Club 8[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be appreciated.

Thanks!

 

[Zubair Muhammad]

Assuming Current Format is in Sheet1 from cells A1 to U 301 i.e. 300 Kids and 20 clubs

And you create the new format in a New Sheet starting from A1 with Choices in first row and kid names in first column.

Enter this formula in cell B2 in new sheet and drag to get the club names

=IFERROR(INDEX(Sheet1!$B$1:$U$1,,MATCH(B$1,OFFSET(Sheet1!$B$1:$U$1,MATCH($A2,Sheet1!$A$2:$A$301,0),,),0)),"")

 

[Peter_SSs]

Welcome to the MrExcel board!

Your sample result doesn't seem to relate to your sample data, and you didn't say where you wanted the results, but see if this macro helps.
It puts the results off to the right of the original data.
Test in a copy of your workbook.

Sub Rearrange()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  d(0) = Empty
  With Range("A1").CurrentRegion
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    b(1, 1) = a(1, 1)
    For i = 2 To UBound(a, 1)
      b(i, 1) = a(i, 1)
      For j = 2 To UBound(a, 2)
        s = a(i, j)
        If Len(s) > 0 Then
          If Not d.exists(s) Then
            d(s) = d.Count + 1
            ReDim Preserve b(1 To UBound(b), 1 To d.Count)
            b(1, d.Count) = s
          End If
          b(i, d(s)) = a(1, j)
        End If
      Next j
    Next i
    .Offset(, .Columns.Count + 1).Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub

The code above produced the results shown in columns H:J from the original data shown in A:F.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Club1	Club2	Club3	Club4	Club5		Name	Choice 1	Choice 2
2	Kid1		Choice 1		Choice 2			Kid1	Club2	Club4
3	Kid2	Choice 1		Choice 2				Kid2	Club1	Club3
4	Kid3	Choice 2	Choice 1					Kid3	Club2	Club1
5	Kid4			Choice 2		Choice 1		Kid4	Club5	Club3
Rearrange