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

In your list add helper column that retrieves first 7 character from 2nd
column.

Use formula =left(reference cell,7)

afterwards create your report with the help of Pivottable.

See more details for pivottable in Excel Help.



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
If You Can't Excel with Talent, Triumph with Effort.

Free Addins Office Classic Menu-2003 for Office-2007
http://in.geocities.com/shahshaileshs/menuaddins



"Sinner" wrote in message
...
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
-----------------------------------------