Pivot Table Comma Delimited Values

ninjasushi

New Member
Joined
Mar 16, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have a table with tags separated with commas for each row like below:

DateItem NoTag
1/3/2022
1​
Tag1, Tag2, Tag3
1/5/2022
2​
Tag2, Tag4
1/8/2022
3​
Tag5
1/3/2022
4​
Tag1, Tag2, Tag5
1/6/2022
5​
Tag7, Tag11
1/3/2022
6​
Tag1, Tag11
1/5/2022
7​
Tag2, Tag8
1/4/2022
8​
Tag1, Tag2, Tag6, Tag9
1/10/2022
9​
Tag3, Tag4
1/8/2022
10​
Tag3, Tag7, Tag12​

I'm trying to create a pivot table that will filter the items based on the date and will count the tags for the filtered items. In example; On 1/3/2022 only item nos. 1, 4 and 6 were active and these three item nos had a total of three tag1, two tag2, one tag3, one tag5 and one tag11. By this way i can see which tag was the most used for the given specific date. Once have this as a pivot table then want to create a chart to express the same. My goal here is to find the most used tag within the given date.

Been trying to find a way to do this and couldn't manage yet. Hope someone can help me out. Thank you for your time in advance!

Cheers!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi!

So I think to get to your end goal, we'll need to do a little data transformation. What I would do is load that table into Power Query, split up the tag column by delimiter, and then unpivot the split tag columns back into one. The query output is a three column table that you can then throw into a pivot chart or table and aggregate how you want.
1647628606043.png
 
Upvote 0
Hi!

So I think to get to your end goal, we'll need to do a little data transformation. What I would do is load that table into Power Query, split up the tag column by delimiter, and then unpivot the split tag columns back into one. The query output is a three column table that you can then throw into a pivot chart or table and aggregate how you want.
View attachment 60411

Hi there!

Thanks for your kind reply and time here. It's a learning process for me and wondering if the Power Query and the pivot table will update automatically when I add more dates to my main table?

Thanks again!
 
Upvote 0
If you mean automatic as in requiring no user intervention, then no. You will have to trigger the refresh somehow.

The PQ output table and the pivot table are technically different items so you could refresh them separately from their respective tabs in the ribbon, but you can also refresh everything at once with the Refresh All command on the Data ribbon. It's also possible to trigger the refreshes with VBA, but I'd recommend against that unless there is a need to.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top