i have around 20 sheets each one has a following data
A B C D E F
1 5 6 7 8 9 abc
2 6 5 8 8 9 def
3 8 7 9 2 1 ghi
4 7 7 6 6 5 jkl
5 5 4 1 1 1 mno

i like the result to be converted as follows

abc 5
abc 6
abc 7
abc 8
abc 9
def 6
def 5
def 8
def 8
def 9
and so on

Regards

This works according to my read of your situation. It puts the transformed
data in column G. Assumed is that your data starts in A1 and ranges A to F
and down an indefinite number of rows. Change to suit. After creating the new
data you can confirm that it's OK and delete the original. Then paste it in
its place.

Test it rigorously on experimental data. Written and tested in 10 minutes!!!

Sub TransformData()
Dim r As Range
Dim i As Long, ii As Long, x As Long
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For i = 1 To r.Count
For ii = 1 To 5
x = x + 1
r(x, 7).Value = r(i, 6) & r(i, ii).Value
Next
Next
End Sub

Greg

"osaka78" wrote:

i have around 20 sheets each one has a following data
A B C D E F
1 5 6 7 8 9 abc
2 6 5 8 8 9 def
3 8 7 9 2 1 ghi
4 7 7 6 6 5 jkl
5 5 4 1 1 1 mno

i like the result to be converted as follows

abc 5
abc 6
abc 7
abc 8
abc 9
def 6
def 5
def 8
def 8
def 9
and so on

Regards

Or use Pivot Table.
No formulas or VBA needed.
You can convert 20 sheets with
odd sized tables at odd locations
into one list.
http://www.freefilehosting.net/download/3b5gk