Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with the script
Hi,
I have the following list. Date List 02-mar-2008 08459087671 02-mar-2008 08459087673 04-mar-2008 08465228672 07-mar-2008 08429087671 07-mar-2008 08429087571 02-mar-2008 08454287667 02-mar-2008 08454287657 ----------------------------------- Would like to calculate the following i.e. the formula or VB code should first list items based on first 7 characters uniqueness & then the quanity count. Result: If columnA of sheet1 are Dates & columnB is the list of numbers then following is required: It is same but now datewise and in table form with breakup. ------------------------------------------------------------------ Date: 0845908 0846522 0842908 0845428 02-mar-2008 2 2 04-mar-2008 1 07-mar-2008 2 ------------------------------------------------------------------ Already using the following code: ----------------------------------------- Sub get_unique() Dim FNum As String Sh1RowCount = 1 Sh2RowCount = 1 With Sheets("Sheet1") Do While .Range("A" & Sh1RowCount).Text < "" FNum = Left(.Range("A" & Sh1RowCount), 7) With Sheets("Sheet2") Set c = .Columns("A:A").Find(what:=FNum, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then .Range("A" & Sh2RowCount).NumberFormat = "@" .Range("A" & Sh2RowCount) = FNum .Range("B" & Sh2RowCount) = 1 Sh2RowCount = Sh2RowCount + 1 Else .Range("B" & c.Row) = .Range("B" & c.Row) + 1 End If End With Sh1RowCount = Sh1RowCount + 1 Loop End With End Sub ----------------------------------------- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with the script
I'd do it this way
1) Add a helper column with this =Left(B2,7) and put the appropriate label on it. Then use a Pivot table to get the list you want. -- HTH, Barb Reinhardt "Angela" wrote: Hi, I have the following list. Date List 02-mar-2008 08459087671 02-mar-2008 08459087673 04-mar-2008 08465228672 07-mar-2008 08429087671 07-mar-2008 08429087571 02-mar-2008 08454287667 02-mar-2008 08454287657 ----------------------------------- Would like to calculate the following i.e. the formula or VB code should first list items based on first 7 characters uniqueness & then the quanity count. Result: If columnA of sheet1 are Dates & columnB is the list of numbers then following is required: It is same but now datewise and in table form with breakup. ------------------------------------------------------------------ Date: 0845908 0846522 0842908 0845428 02-mar-2008 2 2 04-mar-2008 1 07-mar-2008 2 ------------------------------------------------------------------ Already using the following code: ----------------------------------------- Sub get_unique() Dim FNum As String Sh1RowCount = 1 Sh2RowCount = 1 With Sheets("Sheet1") Do While .Range("A" & Sh1RowCount).Text < "" FNum = Left(.Range("A" & Sh1RowCount), 7) With Sheets("Sheet2") Set c = .Columns("A:A").Find(what:=FNum, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then .Range("A" & Sh2RowCount).NumberFormat = "@" .Range("A" & Sh2RowCount) = FNum .Range("B" & Sh2RowCount) = 1 Sh2RowCount = Sh2RowCount + 1 Else .Range("B" & c.Row) = .Range("B" & c.Row) + 1 End If End With Sh1RowCount = Sh1RowCount + 1 Loop End With End Sub ----------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help ! How do I do this in VB Script | Setting up and Configuration of Excel | |||
I need some VB script please | Excel Discussion (Misc queries) | |||
VB script help - please!! | Excel Discussion (Misc queries) | |||
VBA script help..Please !!!! | Excel Discussion (Misc queries) | |||
VB script help..please !! | Excel Worksheet Functions |