whereswaller
New Member
- Joined
- Aug 27, 2013
- Messages
- 12
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 and is filtered by the contents of an adjacent range (in this case an entire column, B:B, where the cell = "Open").
[TABLE="class: cms_table_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]
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
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 and is filtered by the contents of an adjacent range (in this case an entire column, B:B, where the cell = "Open").
[TABLE="class: cms_table_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]
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