Hello Sir,
Thanks for your early reply. The codes you supplied is absolutely magestic,
running very well.
But my unique item list is standard & result must be incorporated in front
of particular part no. for
particular date, say like for the date of 22/09/2008
The date crieteria is there. I have given users a choice to see the data for
a particular date via input box.
Can we get the result for the user define date.
Awaiting your favourable reply.

Further, I want to know, Can we manipulate recordset.field while importing
data from access into Excel,
There is one field named Date. The field is actually in DD/MM/YYYY HH:MM:SS
format.
Can we import the date only in DD/MM/YYYY format.
Any help in this regard will be highly appreciated.

--
Thanks,
Vikram P. Dhemare


"Joel" wrote:

Try this code. The code will crete the unique list of Vehicles and
ParNumbers. The code expect input data to be in sheet "Despatch Data" and a
sheet called "Unique Item List" which will be cleared everytime code is run
and then filled in as required.


Sub GetUniqueData()

With Sheets("Unique Item List")
.Cells.ClearContents
.Range("A1") = "PART_NO"
.Range("B1") = "Sum"
NewRow = 2
NewCol = 3
End With
With Sheets("Despatch Data")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Vehicle = .Range("B" & RowCount)
PartNo = .Range("C" & RowCount)
QTY = .Range("D" & RowCount)
With Sheets("Unique Item List")
'check if Vehicle exists
Set c = .Rows(1).Find(what:=Vehicle, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, NewCol) = Vehicle
VehicleCol = NewCol
NewCol = NewCol + 1
Else
VehicleCol = c.Column
End If

'check if part number exists
Set c = .Columns("A").Find(what:=PartNo, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = PartNo
.Cells(NewRow, VehicleCol) = QTY
NewRow = NewRow + 1
Else
.Cells(c.Row, VehicleCol) = QTY
End If
End With
RowCount = RowCount + 1
Loop
End With
With Sheets("Unique Item List")

'Add Sum formula
.Range("B2").FormulaR1C1 = "=Sum(R[]C3:R[]C" & (NewCol - 1) & ")"
.Range("B2").Copy Destination:=.Range("B2:B" & (NewRow - 1))
End With
End Sub


"Vikram Dhemare" wrote:

Hello all,

I have an sheet where despatch data is being stored, another sheet is the
unique item list. Now I wish to put the
the vehilce wise details for the particular date in a unique item list
sheet. I know, we can use pivot table but I want
this to run through vba code as users are not conversant with pivot table.
I am not very much conversant with vba macro. Can anybody help me by
providing the codes to resolve this issue.

Unique Item List Sheet
Result for the date of 22/09/2008
PART_NO Sum GJ18T-1091 GJ6Y-5476 GJ6Y-6053
FAGM0034 20 20
FAGM0042 20 20
FAGM0044 20 20
FAGM0046 18 18
FAGM0049 0
FAGM0050 16 16
FAGM0052 16 16
FAGM0071 24 24
FAGM0074 40 40
FAGM0100 0
FAGM0126 26 26
FAGM0151 23 23
FAGM0153 24 24
FAGM0156 24 24
FAGM0157 40 16 24
FAGM0159 32 32

Despatch Data Sheet
DATE VEH_NO PART_NO QTY
22/09/2008 GJ06Y-6053 FAGM0156 33
22/09/2008 GJ06Y-6053 FAGM0179 20
23/09/2008 GJ18T-1091 FAGM0162 18
23/09/2008 GJ18T-1091 FAGM0151 23
23/09/2008 GJ18T-1091 FAGM0178 40
23/09/2008 GJ18T-1091 FAGM0183 40
23/09/2008 GJ18T-1091 FAGM0198 21
23/09/2008 GJ18T-1091 FAGM0201 40
23/09/2008 GJ18T-1091 FMGM0668 40
23/09/2008 GJ18T-1091 FMGM0642 40
23/09/2008 GJ18T-1091 FAGM0206 45
23/09/2008 GJ18T-1091 FAGM0179 20
23/09/2008 GJ18T-1091 FMGM0656 100
23/09/2008 GJ6Y-5476 FAGM0162 24
23/09/2008 GJ6Y-5476 FAGM0126 26
23/09/2008 GJ6Y-5476 FAGM0074 40
23/09/2008 GJ6Y-5476 FAGM0156 24
23/09/2008 GJ6Y-5476 FAGM0042 20
23/09/2008 GJ6Y-5476 FAGM0044 20
23/09/2008 GJ6Y-5476 FPGM0012 24
23/09/2008 GJ6Y-5476 FPGM0011 24
23/09/2008 GJ6Y-5476 FAGM0157 16
23/09/2008 GJ6Y-5476 FAGM0178 20
23/09/2008 GJ6Y-5476 FAGM0153 24
23/09/2008 GJ6Z-3075 FAGM0159 16
23/09/2008 GJ6Y-6053 FAGM0162 18
23/09/2008 GJ6Y-6053 FAGM0071 24

--
Thanks,
Vikram P. Dhemare