rearranging formula suggestion



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
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:

David McRitchie has an intro to macros:

Ron de Bruin's intro to macros:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sorting array formula 2
Rearranging DATA 6
Offset in Formula 1
Critical Counitif formula 3
*Assistance with formula* 3
Hi Alllen 1
Merged cells and formula data sources 10
Calc Speed & Formula simplification 2