Short a schedule

[sctroy]

In A1:A8 I have
2-4
1-8
3-4
5-2
6-7
2-9
6-1


I want to be able to show how many ones twos threes ect. are in this column?
Be able to view the ones in their own column the twos in their colums and so on.
1-8 2-4 3-4 2-4 5-2 6-7 6-7 2-9
6-1 5-2 3-4 6-1
2-9

[Dave Peterson]

Is a macro ok?

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim mySplit As Variant

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With newWks
.Cells.NumberFormat = "@" 'stop them from converting to dates

For Each myCell In myRng.Cells
mySplit = Split97(myCell.Value, "-")
'or if xl2k and higher
'mySplit = Split(myCell.Value, "-")
.Cells(.Rows.Count, CLng(mySplit(LBound(mySplit)))) _
.End(xlUp).Offset(1, 0).Value _
= myCell.Value
.Cells(.Rows.Count, CLng(mySplit(UBound(mySplit)))) _
.End(xlUp).Offset(1, 0).Value _
= myCell.Value
Next myCell

With Intersect(.UsedRange.EntireColumn, .Rows(1))
.NumberFormat = "General"
.Formula = "=column()"
.Value = .Value
End With
End With
End Sub
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Split was added in xl2k. If you're using xl97, use Tom's split97. If you and
your users are all at xl2k or higher, you can delete that function completely.

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



sctroy wrote:
>
> In A1:A8 I have
> 2-4
> 1-8
> 3-4
> 5-2
> 6-7
> 2-9
> 6-1
>
> I want to be able to show how many ones twos threes ect. are in this
> column?
> Be able to view the ones in their own column the twos in their colums
> and so on.
> 1-8 2-4 3-4 2-4 5-2 6-7 6-7 2-9
> 6-1 5-2 3-4 6-1
> 2-9
>
> --
> sctroy
> ------------------------------------------------------------------------
> sctroy's Profile: http://www.excelforum.com/member.php...o&userid=25928
> View this thread: http://www.excelforum.com/showthread...hreadid=399144

--

Dave Peterson

[Biff]

Hi!

Here's one way:

In B1:K1 enter the headers: 0,1,2,3,4,5,6,7,8,9

In B2 enter this formula:

=SUMPRODUCT(LEN($A$1:$A$7)-LEN(SUBSTITUTE($A$1:$A$7,B1,"")))

Copy across to K2. This will give you the count of each number in the range
in column A.

In B3 enter this formula using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=B$2,INDEX($A$1:$A$7,SMALL(IF(ISNUMBER(SEARCH(B$1,$A$1:$A$7)),ROW($1:$7)),ROW(1:1))),"")

Now, since your sample data is in the range A1:A7 (not A8) and is a total of
7 entries the maximum number of matches for any single number could be 7.
So, copy the formula in B3 down 7 rows then across to column K.

Note: in the formula, this portion: ROW($1:$7), refers to the SIZE of the
data range. Your data range has 7 entries thus: ROW($1:$7). If the data
range had 100 entries and was in the physical range A22:A121, then the ROW()
argument would be: ROW($1:$100)

Biff

"sctroy" <[email protected]> wrote in
message news:[email protected]...
>
> In A1:A8 I have
> 2-4
> 1-8
> 3-4
> 5-2
> 6-7
> 2-9
> 6-1
>
>
> I want to be able to show how many ones twos threes ect. are in this
> column?
> Be able to view the ones in their own column the twos in their colums
> and so on.
> 1-8 2-4 3-4 2-4 5-2 6-7 6-7 2-9
> 6-1 5-2 3-4 6-1
> 2-9
>
>
> --
> sctroy
> ------------------------------------------------------------------------
> sctroy's Profile:
> http://www.excelforum.com/member.php...o&userid=25928
> View this thread: http://www.excelforum.com/showthread...hreadid=399144
>