Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to mimic pivot tables for comma separated cells, and output into comma separated cells as well, grouping by country. I have about 100 rows of this data, so I cannot convert to column or rows.

The data

Col A | Col B

country1,country1,country3,country4,country4,country4,country8|number1,number2,number3,number4,number5,number6,number7

needed output

country1,country3,country4,country8|(number1+number2),number3,(number4+number5+number6),number7

I'm new to pivot tables, and any macro or VBA function is okay. Any ideas?

share|improve this question
    
When you say comma separated cells, are the actually in different cells, or are then in the same cell> – soandos May 22 '12 at 19:00
    
the countries are in one cell, and the values are in another cell. – Hai Myster May 22 '12 at 19:12

The follow formula will work in excel 2007 and 2010 assuming the countries cell is A1 and that the values cell is B1 and that both cells contain 7 values separated by 6 commas:

Countries:

    =LEFT(A1,SEARCH(",",A1)-1)&","&LEFT(RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1)+1)),SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1)+1)-1)&","&LEFT(RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)),SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)-1)&","&RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)+1)+1)+1))

Values:

     =LEFT(B1,SEARCH(",",B1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1)),SEARCH(",",B1,SEARCH(",",B1)+1)-SEARCH(",",B1)-1)&","&LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1)+1)-1)&","&LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)-1)&"," &RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)+1))

This could also be done with VBA. Let me know if you're using an older version of excel and I can post that.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .