Pivot Charts - Having trouble splitting up words in cells to make pivot charts

See if this gets you close to what you are looking for. Open the attatched file, Enable macros, make your selection of values for column A in sheet "Tags Problem"
Run the macro with {ctrl} + t
You do not need the final table in sheet 2 to create your pivot table - but it shows what the values should be.

What the macro does:
-Clears old values in sheets 1&2
-Copies selected values to sheet1
-Creates an array of all tags in column B
-Copies those values to sheet2 and removes duplicates to create unique list
-Places unique list as column headers in Sheet1
-Searches for matches to the values in those column headers against columnB
-Places matched values in relevant column
-Places count of each tag in sheet2
(Uses CountIf to arrive at count for each tag -matching sheet1 values and unique values in sheet2)

Please Login or Register  to view this content.

Hi Kevin! thanks for your complex solution! It works a treat and was amazed you figured how to do that. I didnt realise this problem required a VBA solution O_O

I will attempt to implement this into my master list and will let you know how I go when i finally get more time to work on it!

Cheers have a great weekend Kevin

A formula solution:

Put this in cell C2 and copy down and across
=IF(ISNUMBER(SEARCH(C$1,$B2)), C$1, "")
For this to work you need all the headings in row1
see sheet formula solution

The vba solution does everything including building an index of unique Headings automatically, and results in everything being included when a new tag is added.