I'm looking at a large data set, 170+ columns, 10k rows and want to summarize the data within the table by counting the number of times a certain descriptor is used.

In the example screenshot below, one column contains rows with repetitive information, so I added a value column, set the rows in that column to = 1 and summed this using a pivot table.

However with a much larger data set it isn't efficient to use a pivot table, is there a better way to count the duplicate data in the rows? Power query?

Example Results:

enter image description here

  • 2
    Show sample source data and desured result for it. In formatted table form, not as a screenshot. – Akina May 27 at 6:36
  • 1
    There are ways to do this which can involve VBA or a long formula, it dependes. – dmb May 27 at 17:34
  • 1
    1. It isn't clear what you mean by duplicate data. Are you considering each column independently, and looking for values that repeat within that column? 2. Why is a pivot table inefficient? 3. It isn't clear how the example results reflects your description. What values are you summing? 4. With 1.7M cells, what benefit is it to know that there were 6 duplicate values in Customer Price or 6 in Part Number? It doesn't tell you what values were duplicated, whether 6 values were each duplicated once or 1 value was duplicated 6 times, or where to find those duplicates to do anything about them. – fixer1234 May 27 at 23:43
  • 1
    Please provide more information about your source data. – Lee May 28 at 2:41
  • 1
    "Basically I'm trying to look at the data to see if I can spot any patterns or identify areas that need to be corrected (data inserted in wrong column" I still don't understand what you're trying to DO in a larger sense - but when I have one-offs like what you're describing, one way to immediately notice obvious problems is just to turn on a filter on the whole table, and click into each field's filter dropdown to see if there are values that obviously don't belong. – Alex M May 28 at 6:06

I would use Power Query for this. It has an Unpivot feature that can turn columns into rows, with Attribute and Value columns (for the Column Header and cell value). The trick is to use the Advanced option in the Unpivot to avoid aggregating the cell values.

You would need to keep a unique column out of the Unpivot, to preserve the original row counts. Or you could add an Index column to emulate row numbers in Excel.

From there you could use a Pivot Table, or Power Query can Group By and Count.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.