Prev: How to display decimal number only?
Next: Weighted averaging of dates
From: driller on 6 Jul 2008 09:22
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.

--
regards,

From: Dave Peterson on 6 Jul 2008 11:00
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 wrote:
>
> 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.
>
> --
> regards,

--

Dave Peterson
From: driller on 7 Jul 2008 09:28

Dave,

you've done it so simple yet quick.

thanks a lot.

--
regards,



"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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.
> >
> > --
> > regards,
>
> --
>
> Dave Peterson
>
 | 
Pages: 1
Prev: How to display decimal number only?
Next: Weighted averaging of dates