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

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

"sang" wrote:

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

Use Pivot Table.
No code, no formulas.
Example at:
http://www.savefile.com/files/1745820