So I take the left one, Ctrl+T "My table has headers", Yes Take each of these tables and make them into- Format as Table using Ctrl+T,Īlright. Take the Sector's from the right table, paste all of these together, and thenĬhoose the whole set, and under Data, choose Remove Duplicates- right here-Īnd we end up with just the unique list of Sectors. So I take the Sectors from the left table, I Tables, and that table is just going to be really simple- it's just going toīe a list of all of the Sectors. We're going to build a third table that's going to live between the other two Tables- I need to just completely start over. And if I have anyĮxisting pivot tables on these two data sets, I need to get rid of those pivot Has Sector, and I have a table on the right that has Sector. And so, you know, I wondered if there was an easier way to doĪnd so, I have a table here on the left that Really caused a lot of problems because people have slicers based on data thatĭoesn't match. Question, he said, "Hey, look, I have pivot tables built on two differentĭata sets and I'd like one slicer to be able to control those." And now Iĭid a video about this- Episode 2104- that used some VBA, but this video has I was in New Jersey doing a seminar there, and Rick asked a Hey, welcome back to the MrExcel netcast, I'mīill Jelen. Learn Excel from MrExcel Podcast, Episode This method seems far easier than the VBA method described in video 2104.
The result will be two pivot tables (from different data sets) that react to the slicer. Select the other pivot table and choose Filter Connections.Ĭonnect the other pivot table to the slicer Initially, only one pivot table will respond to the slicer. Find the Slicer Source table and build the slicer from there. When you insert a slicer, there will be two tabs across the top. When you create a pivot table from each of the two data sets, be sure to check the box for Add This Data to the Data Model. Use Data, Remove Duplicates to create a unique list of the sectors found in either table.īuild a third table to be the source for the slicer If both of your data sets contain a field called Sector and you want the pivot table to be based on Sector, copy the Sectors from both tables to a new table. The key step is to build a new SlicerSource table. The data model is easier than the VBA solution.
0 kommentar(er)
