Search code examples
excelexcel-formulauniquecelldelimiter

Convert delimited text in cells to table with unique headers

I have an Excel table with two columns and about 500 rows. In the first column is the name of a company, and in the second column is a list of partners, delimited by a semicolon. For example, one row would look like [Apple][Foxcomm; Samsung; Microsoft; Intel]. There is an unknown number of partners for each partners cell, and some partners appear with multiple companies.

What I'm trying to accomplish is to reorganize this collection of data into a new table, where each column header is a partner (unique, doesn't repeat), and each company that shares that partner is listed below.

So far I've manged to find a formula for splitting the text based on the delimiter semicolon, but it spreads it across the row (predictably). I've also found formulas for transcribing a table by basically switching the rows and columns, but I'm not sure if I'll even need to do so. My main problem seems to be finding the correct way to phrase my searches on Google!

How can I get Excel to do what I'm trying to accomplish? Or, what would be a better way to display this information?

Solution

  • I fear you may be doing more work than really necessary. I think you have most of the components and as I am not sure quite where something is awry an image may help:

    SO23231949 example

    Then, assuming Apple is in A2, 

    =IFERROR(IF(SEARCH(C$1,$B2)>0,C$1,),"")

    in C2 copied across to the right (I think as far as GF2) then all these formulae copied down to suit.

    Better to TRIM before removing duplicates (switch order of 3. and 4.)