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

Tiger Woods / Greg Norman

Greg Norman

Tiger Woods / Ernie Els

Ernie Els / Greg Norman / Tiger Woods

<tbody>
</tbody>

Example desired output

Greg Norman	3
Tiger Woods	3
Ernie Els	2

<tbody>
</tbody>

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

Column1		Column1	Count
Tiger Woods / Greg Norman		Tiger Woods	3​
Greg Norman		Greg Norman	3​
Tiger Woods / Ernie Els		Ernie Els	2​
Ernie Els / Greg Norman / Tiger Woods

[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?

Example dataset
Tiger Woods / Greg Norman	Open
Greg Norman	Open
Tiger Woods / Ernie Els	Closed
Ernie Els / Greg Norman / Tiger Woods	Open
Example desired output
Greg Norman	3
Tiger Woods	2
Ernie Els	1

<tbody>
</tbody>

 

[whereswaller]

Bumped.