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" <(E-Mail Removed)> wrote in message
news:89f0946a-9c35-442a-9c92-(E-Mail Removed)...
> 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
> -----------------------------------------