Hi,

I need one help. If I have excel rows like this :
1 a b c d
2 a b c d e
3 a b c d

I want like this :
1 a
1 b
1 c
1 d
2 a
2 b
2 c
2 d
2 e
3 a
3 b
3 c
3 d

Is it possible ?

Best Regards

Raju C Padaria
India


--
padarrju
------------------------------------------------------------------------
padarrju's Profile: http://www.excelforum.com/member.php...o&userid=35787
View this thread: http://www.excelforum.com/showthread...hreadid=555517

One play to get there ..

Assume source data is in Sheet1, from cols A to F (a max 5 cols for the
text-data in cols B to F is assumed), from row1 down

In a new Sheet2,

In A2:
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/5),)

In B2:
=OFFSET(Sheet1!$B$1,INT((ROW(A1)-1)/5),MOD(ROW(A1)-1,5))

Select A2:B2, copy down until continuous zeros appear, signalling exhaustion
of data

Freeze the values in cols A & B with an "in-place": copy paste special
check "values" ok (overwrite the formulas with the evaluated values).

Do an autofilter on col B, filter out zeros, select all the filtered rows &
then right-click delete rows. Remove the autofilter, and you'd get the
desired results.

Adapt to suit. Change the "5" in the formulas to correspond with the max
number of text-data cols involved in Sheet1. I used "5" as I assumed there
were a max 5 cols (B to F) of text-data in Sheet1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"padarrju" wrote:
I need one help. If I have excel rows like this :
1 a b c d
2 a b c d e
3 a b c d

I want like this :
1 a
1 b
1 c
1 d
2 a
2 b
2 c
2 d
2 e
3 a
3 b
3 c
3 d

Is it possible ?

Best Regards

Raju C Padaria
India


--
padarrju
------------------------------------------------------------------------
padarrju's Profile: http://www.excelforum.com/member.php...o&userid=35787
View this thread: http://www.excelforum.com/showthread...hreadid=555517

Raju ,

If you need to do that a lot, use a macro. Select a cell in your table, and run the code below.

HTH,
Bernie
MS Excel MVP


Sub MakeTableNoColumnHeaders()
Dim myCell As Range
Dim newSheet As Worksheet
Dim mySheet As Worksheet
Dim i As Long
Dim j As Integer
Dim k As Long
Dim l As Integer
Dim mySelection As Range
Dim RowFields As Integer

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
RowFields = 1
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
Application.DisplayAlerts = True
Set newSheet = Worksheets.Add
newSheet.Name = "New Database"
mySheet.Activate
i = 1
For j = mySelection(1).Row To _
mySelection(mySelection.Cells.Count).Row
For k = mySelection(1).Column + RowFields To _
mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value < "" Then
For l = 1 To RowFields
newSheet.Cells(i, l).Value = _
Cells(j, mySelection(l).Column).Value
Next l
newSheet.Cells(i, RowFields + 1).Value = _
Cells(j, k).Value
i = i + 1
End If
Next k
Next j

End Sub



"padarrju" wrote in message
...

Hi,

I need one help. If I have excel rows like this :
1 a b c d
2 a b c d e
3 a b c d

I want like this :
1 a
1 b
1 c
1 d
2 a
2 b
2 c
2 d
2 e
3 a
3 b
3 c
3 d

Is it possible ?

Best Regards

Raju C Padaria
India


--
padarrju
------------------------------------------------------------------------
padarrju's Profile: http://www.excelforum.com/member.php...o&userid=35787
View this thread: http://www.excelforum.com/showthread...hreadid=555517