Separate Column Inputting Pivot Table Rows Based On "/" Delimiter

[whereswaller]

Hi,
I am trying to create a table that summarises the number of times a name appears in a range (in this case an entire column, A:A) that has delimiter (in this case a forward slash) structured data.


Example dataset

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Tiger Woods / Greg Norman[/TD]
[/TR]
[TR]
[TD]Greg Norman[/TD]
[/TR]
[TR]
[TD]Tiger Woods / Ernie Els[/TD]
[/TR]
[TR]
[TD]Ernie Els / Greg Norman / Tiger Woods[/TD]
[/TR]
</tbody>[/TABLE]

Example desired output
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Greg Norman[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Tiger Woods[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Ernie Els[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Is there any way I can achieve this?

Note, the names in the dataset can change so I would like to avoid hardcoding the names and simply using a COUNTIF function to achieve the output.

Many thanks for any help you can provide,
James

 

[sandy666]

if you are able to use PowerQuery (Get&Transform) you can try

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Tiger Woods / Greg Norman[/td][td][/td][td=bgcolor:#E2EFDA]Tiger Woods[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Greg Norman[/td][td][/td][td]Greg Norman[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Tiger Woods / Ernie Els[/td][td][/td][td=bgcolor:#E2EFDA]Ernie Els[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ernie Els / Greg Norman / Tiger Woods[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column1"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[Rick Rothstein]

Here is a macro that should do what you want (source assumed to start at cell A1, output goes to Columns B and C)...

[table="width: 500"]
[tr]
	[td]Sub NameCount()
  Dim X As Long, Data As Variant
  Data = Split(Replace(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), "/"), " / ", "/"), "/")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Data)
      .Item(Data(X)) = .Item(Data(X)) + 1
    Next
    Range("B1").Resize(.Count) = Application.Transpose(.Keys)
    Range("C1").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub[/td]
[/tr]
[/table]

 

[whereswaller]

Here is a macro that should do what you want (source assumed to start at cell A1, output goes to Columns B and C)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub NameCount()
  Dim X As Long, Data As Variant
  Data = Split(Replace(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), "/"), " / ", "/"), "/")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Data)
      .Item(Data(X)) = .Item(Data(X)) + 1
    Next
    Range("B1").Resize(.Count) = Application.Transpose(.Keys)
    Range("C1").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much for your help, Rick. How would I modify this script so that it only counts items that have the word "Open" written in cell B1?

[TABLE="class: grid, width: 282"]
<tbody>[TR]
[TD]Example dataset[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tiger Woods / Greg Norman[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]Greg Norman[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]Tiger Woods / Ernie Els[/TD]
[TD]Closed[/TD]
[/TR]
[TR]
[TD]Ernie Els / Greg Norman / Tiger Woods[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Example desired output[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg Norman[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Tiger Woods[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ernie Els[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[whereswaller]

Bumped.