So you could just pull in the column you are interested in (i.e. But this will still pull all the data in the entire table through, even though you only want a distinct list of items. So you might want to have your data stored in another workbook, and just point the pivottable to that workbook. It's worth pointing out that this approach might significantly bloat the size of your file, because ALL your data is duplicated within the pivot cache.which could be many many megabytes even if your pivot summary only has a few item. Pivot Table tricks to make you a data analytics star.Introduction to Pivot Tables – Tutorial.More tips on using Pivot Tables to Analyze data: What about you? How do you deal with duplicate data? Share your techniques, tips & ideas using comments. However, you can automate this by writing a simple macro. The only glitch is that you need to refresh the pivot tables whenever source data changes. I have been using pivot table based technique with great success. In my work, I come across duplicate data all the time.
Download example file with this techniqueĬlick here to download an example file and play with it to understand how this works. Just make sure that you refresh the pivot table whenever source data changes. Step 4: Use the named range in formulas etc. The COUNTA($F:$F)-1 ensures that we get only row labels and not the title (in this case Product Names). The formula gives us all the values in column F, starting F6. =OFFSET($F$6, 0,0,COUNTA($F:$F)-1,1) and map it to a name like lstProducts. Assuming the pivot table row values start in cell F6, write a formula like, Using OFFSET formula, we can create a named range that refers to pivot table values and grows or shrinks as the pivot is refreshed. Step 3: Create a named range that refers to the pivot table values Make sure you have turned off grand totals and sub-totals as we just need the names. Very simple right? Step 2: Drag the field(s) to row label area of pivot Just select any cell and insert a pivot table. This is how it goes…, Step 1: Select your data & Create a pivot table It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8).īut today I want to show you a technique I have been using to dynamically extract and sort all unique items from a last list of values using Pivot Tables & OFFSET formula. Removing duplicate data is like morning coffee for us, data analysts.