I dont actually know what function i should use to my problem...

I want to make a list of different text-cells, if they contain a number.

example
A B C D E
1 Wall Ceiling Motor Sling
2 Cost 10 20
3 Invest 30 40
4 Develop 10 55 55

I would then like to make a Cost list. So it takes only those where numbers
are written.
So the list should be like this
Cost
Wall
Ceiling

And if it is possible
Cost
Wall 10
Ceiling 20

And another list would be
Invest
Motor 30
Sling 40


Hopefully you understand what i want...

Brian

I would use VBA

Sub Test()
Dim iLastRow As Long
Dim iNextRow As Long
Dim iLastCol As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iNextRow = iLastRow + 3
For i = 2 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If iLastCol < 1 Then
Cells(iNextRow, "A").Value = Cells(i, "A").Value
iNextRow = iNextRow + 1
For j = 2 To iLastCol
If Cells(i, j).Value < "" Then
Cells(iNextRow, "A").Value = Cells(1, j).Value
Cells(iNextRow, "b").Value = Cells(i, j).Value
iNextRow = iNextRow + 1
End If
Next j
iNextRow = iNextRow + 1
End If
Next i

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian Brandt" wrote in message
...
I dont actually know what function i should use to my problem...

I want to make a list of different text-cells, if they contain a number.

example
A B C D E
1 Wall Ceiling Motor Sling
2 Cost 10 20
3 Invest 30 40
4 Develop 10 55 55

I would then like to make a Cost list. So it takes only those where
numbers
are written.
So the list should be like this
Cost
Wall
Ceiling

And if it is possible
Cost
Wall 10
Ceiling 20

And another list would be
Invest
Motor 30
Sling 40


Hopefully you understand what i want...

Brian

Hi Bob..
Thank you very much... with a little adjustments (after figuring out how VBA
works ;-) ), it worked perfectly...

Is it possible to take the same text-formatting into the list, so my
headlines that are bold, also becomes bold in the VBA made list... ?

Brian


"Bob Phillips" skrev:

I would use VBA

Sub Test()
Dim iLastRow As Long
Dim iNextRow As Long
Dim iLastCol As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iNextRow = iLastRow + 3
For i = 2 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If iLastCol < 1 Then
Cells(iNextRow, "A").Value = Cells(i, "A").Value
iNextRow = iNextRow + 1
For j = 2 To iLastCol
If Cells(i, j).Value < "" Then
Cells(iNextRow, "A").Value = Cells(1, j).Value
Cells(iNextRow, "b").Value = Cells(i, j).Value
iNextRow = iNextRow + 1
End If
Next j
iNextRow = iNextRow + 1
End If
Next i

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian Brandt" wrote in message
...
I dont actually know what function i should use to my problem...

I want to make a list of different text-cells, if they contain a number.

example
A B C D E
1 Wall Ceiling Motor Sling
2 Cost 10 20
3 Invest 30 40
4 Develop 10 55 55

I would then like to make a Cost list. So it takes only those where
numbers
are written.
So the list should be like this
Cost
Wall
Ceiling

And if it is possible
Cost
Wall 10
Ceiling 20

And another list would be
Invest
Motor 30
Sling 40


Hopefully you understand what i want...

Brian

I don't know what changes that you made, but this is based on my original
code, so you will need to re-do them

Sub Test()
Dim iLastRow As Long
Dim iNextRow As Long
Dim iLastCol As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iNextRow = iLastRow + 3
For i = 2 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If iLastCol < 1 Then
Cells(i, "A").Copy Cells(iNextRow, "A")
iNextRow = iNextRow + 1
For j = 2 To iLastCol
If Cells(i, j).Value < "" Then
Cells(1, j).Copy Cells(iNextRow, "A")
Cells(i, j).Copy Cells(iNextRow, "b")
iNextRow = iNextRow + 1
End If
Next j
iNextRow = iNextRow + 1
End If
Next i

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian Brandt" wrote in message
...
Hi Bob..
Thank you very much... with a little adjustments (after figuring out how
VBA
works ;-) ), it worked perfectly...

Is it possible to take the same text-formatting into the list, so my
headlines that are bold, also becomes bold in the VBA made list... ?

Brian


"Bob Phillips" skrev:

I would use VBA

Sub Test()
Dim iLastRow As Long
Dim iNextRow As Long
Dim iLastCol As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iNextRow = iLastRow + 3
For i = 2 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If iLastCol < 1 Then
Cells(iNextRow, "A").Value = Cells(i, "A").Value
iNextRow = iNextRow + 1
For j = 2 To iLastCol
If Cells(i, j).Value < "" Then
Cells(iNextRow, "A").Value = Cells(1, j).Value
Cells(iNextRow, "b").Value = Cells(i, j).Value
iNextRow = iNextRow + 1
End If
Next j
iNextRow = iNextRow + 1
End If
Next i

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian Brandt" wrote in message
...
I dont actually know what function i should use to my problem...

I want to make a list of different text-cells, if they contain a
number.

example
A B C D E
1 Wall Ceiling Motor Sling
2 Cost 10 20
3 Invest 30 40
4 Develop 10 55 55

I would then like to make a Cost list. So it takes only those where
numbers
are written.
So the list should be like this
Cost
Wall
Ceiling

And if it is possible
Cost
Wall 10
Ceiling 20

And another list would be
Invest
Motor 30
Sling 40


Hopefully you understand what i want...

Brian