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
-----------------------------------------

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
-----------------------------------------