rearranging formula suggestion

[driller]

Hello for help,

With the best of my memory, i forgot what i learned before.

Please suggest how i can prepare a sorted column of infos based on below
(e.g) table

In sheet 1
col A col B col C col D col E
row 1 NAME 100 105 215 280
row 2 ABC A1 A2
row 3 CDE C1 C2
row 4 DEF X1 X3
row 5 FGH F1 F2

Result desired thru formulation
In Sheet 2 (e.g)

col A col B col C
row 1 NAME SN SQ
row 2 ABC A1 100
row 3 ABC A2 105
row 4 CDE C1 105
row 5 CDE C2 215
row 6 DEF X1 100
row 7 DEF X3 280
row 8 FGH F1 105
row 9 FGH F2 280

looking for suggestion.

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim iRow As Long
Dim iCol As Long
Dim oRow As Long

Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("A1").Resize(1, 3).Value _
= Array("NAME", "SN", "SQ")

oRow = 1
With CurWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = 2 To LastRow
For iCol = 2 To .Cells(iRow, .Columns.Count).End(xlToLeft).Column
If .Cells(iRow, iCol).Value = "" Then
'skip it
Else
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "C").Value = .Cells(1, iCol).Value
End If
Next iCol
Next iRow
End With
End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[driller]

Dave,

you've done it so simple yet quick.

thanks a lot.