Need to split cells, trasnpose certain columns to rows in large document

[BNPalmer]

I have a document that has about 400 rows of info.

One cell contains keywords that need to be spit into individual cells. They are delineated by a return. I can do this my turning the text to column.

This gives me the keywords in their own cells. Which I need.

I can copy and transpose the new columns into one new column of Keywords. Which I need.

However each row has two other cells that need to be married to the Keywords that were in the original row. For example :

Class Code Keywords
Emergency Medicine EMD1 Blood
Emergency Medicine EMD1 Sweat
Emergency Medicine EMD2 Tears
Primary Care PRI 1 Band Aids

Is there a faster way to do this? Splitting, copying, transposing, copy/paste again 400 times is a lot. (Maybe I'm lazy.)

Thanks for your help!I feel like I'm close and just need a little help in the right direction.

[daffodil11]

Lazy is the mother of all process improvement.

Can you give the column order?

Emergency Medicine

EMD1

Blood Sweat Tears

(without the bullet. vbulletin tables are hard)

And this needs to become:

Emergency Medicine	EMD1	Blood
Emergency Medicine	EMD1	Sweat
Emergency Medicine	EMD1	Tears

Is this correct?

[BNPalmer]

Yes! This is correct.

Thank for your quick response!

[daffodil11]

Do you know the max/min # of keywords?

[BNPalmer]

The min would be 1 the max would be 20.

Thanks again.

[daffodil11]

There has to be an easier way, but this was all I could come up with.

On the first tab I added formulas alongside the data to break out the keywords.

On the second tab I use another set of formulas to index and return relevant rows, with a column showing True / False.

Filter by True, select the data, paste the values somewhere else. Tada!

unconcatenation.xlsx

[BNPalmer]

Whoa. This is streets ahead of anything I was messing with.

THANK YOU and your radioactive mutations!

[daffodil11]

No problem. Saw a little hiccup in my formula.

For some reason I thought you had said 1-8 keywords.

To adjust stuff, just make sure you drag the formulas on Sheet1 out to Column X

And then on Sheet2:

B2: =IF($D2="","",INDEX(Sheet1!A$2:A$3,QUOTIENT(ROW(A1),22)+1))
C2: =IF($D2="","",INDEX(Sheet1!B$2:B$3,QUOTIENT(ROW(B1),22)+1))
D2: =IFERROR(INDEX(Sheet1!$E$2:$X$3,QUOTIENT(ROW(B1),20)+1,MOD(ROW(B1),21)),"")

Essentially, Index takes a range and turns it into a table. I'm then using Quotient and Modulation to divide a naturally incrementing number by a set number to get the integer and/or remainder. The integer +1 is the # of rows down, and the remainder is the # of columns over I go in the table.

So, imagine A1:C3 a little 3x3 range. My width is 3 columns, so that's my denominator.
If I paste this little formula into another tab in A1, it pulls 1/3 = (0+1) rows, 1 column = A1 of the index
2/3 = B1 (0+1) rows, 2 columns
3/3 = C1 (0+1 rows, 3 columns
4/3 = A2 (1+1) rows, 1 column
etc

In your scenario, we're just using a bigger table and a denominator of 20.