How to cross-multiply text data in excel??

S

sang

I need to cross-multiply following dat:

COLUMNS:
A........................B

ABC...................PQR
BBC...................LMN
KBC...................KKP

The result should be:

COLUMNS:
A........................B

ABC...................PQR
ABC...................LMN
ABC...................KKP
BBC...................PQR
BBC...................LMN
BBC...................KKP
KBC...................PQR
KBC...................LMN
KBC...................KKP

plz help
 
M

Mike H

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub swapem()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Copy
Range("A" & 65536 - lastrow).PasteSpecial
Range("B1:B" & lastrow).Copy
Range("A1").PasteSpecial
Range("A" & 65536 - lastrow & ":A65536").Copy
Range("B1").PasteSpecial
Range("A" & 65536 - lastrow & ":A65536").ClearContents
End Sub

Mike
 
Top