Cross join

**Hrcko** · 01-28-2010, 05:54 AM

I have columns and rows like this:

3004 05 AA I0108 XXX HRK J, V 01

What I would like to do is create two rows and each row with value J and V, to look like this:

3004 05 AA I0108 XXX HRK J 01
3004 05 AA I0108 XXX HRK V 01

Is there a way to do this? Some sort of cross join? I have like 1500 rows with this kind of values.

**contaminated** · 01-28-2010, 06:08 AM

can u pls uploadd workbook....?

**Richard Buttrey** · 01-28-2010, 06:15 AM

Hi and welcome to the forum.

Code:

B1:
=LEFT(A1,SEARCH(",",A1)-1)&" "&RIGHT(A1,LEN(A1)-SEARCH("!",SUBSTITUTE(A1," ","!",8)))

C1:
=LEFT(A1,SEARCH("!",SUBSTITUTE(A1," ","!",6)))&MID(A1,SEARCH("!",SUBSTITUTE(A1," ","!",7))+1,1)&" "&RIGHT(A1,LEN(A1)-SEARCH("!",SUBSTITUTE(A1," ","!",8)))

Copy B1:C1 down, convert all to values with Copy Paste Special Values and then copy the C column underneath the B column values and sort on column B.

HTH