Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Short a schedule
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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(ISNUM BER(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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where can I find a template for a perpetual shift schedule. | Excel Discussion (Misc queries) | |||
Conditional Formatting a Schedule | Excel Worksheet Functions | |||
room schedule | Excel Worksheet Functions | |||
I would like to make a schedule for my dept. Who works what days . | Charts and Charting in Excel | |||
Status Bar - Key Board Short Cut | Excel Discussion (Misc queries) |