Text to columns and pivot table possible?

  • Hi Everyone -


    I have an problem that I haven't been able to figure out and I'm hoping someone can help. I have an excel file with roughly 200K+ rows. I'm trying to run a pivot on the data to understand what upgrades customers have purchased. The column which shows the upgrades is comma delimited where the data is in the following format:


    upgrade 1 | upgrade 2 | upgrade 3
    Upgrade 1
    upgrade 2 | Upgrade 3
    upgrade 3


    I initially tried running a text 2 columns then using a pivot table, but the data comes out extremely messy. I then tried to just not use text to columns, but then the data is at an aggregated level where it isn't counting each unique occurrence ( for example, upgrade 1 should be counted 2x in the above example - not once).


    Is there a way to use text to columns and use a pivot table? If not, what other options can I use?

  • Re: Text to columns and pivot table possible?


    Without seeing exactly what you have I would think that you need to separate the data then combine resulting columns into one. To use a pivotTable thought there must be other data so you need to attach an example workbook.

  • Re: Text to columns and pivot table possible?


    Wow - Herbds


    Thanks - I have Excel 2010 at home. Do I just use text to columns and run the pivot on the texts to column table?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!