Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Required Columner Data
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default Required Columner Data
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Required Columner Data
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default Required Columner Data
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Required Columner Data
Try these changes. I commented out some old code, may some changes, and
added new code.
1) Removed clearing of "Unique Item List"
2) Commented out code that wrote headers (row and columns) on "Unique Item
List" to use your standard template. Added Error messages if the headers
didn't exist.
3) Added first Instruction that sets the filter Date you are looking for.
You need to modify this instruction to use you Input box. Since the date in
the Input box is a string you need to add DateValue (like I did in the code)
to convert to a microsoft serial date.
4) Added code to add only the date required.
5) It is simple to convert to drop the hours and minutes from the input
time. All you need to do is add INT() to the serial time to get just the
date. for example

MyTime = "1/1/08 22:33:25"
MyDate = Int(DateValue(MyTime))

SerialDate in Microsoft is a number with Day 1 = Jan 1,1900. Each day is
equal to 1 so 2 = Jan 2,1900. Each hour = 1/24 and each minute = 1/(24 * 60)
each second = 1/(24 * 60 * 60)
5) to



Sub GetUniqueData()

RequestDate = DateValue("22/09/2008")

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) < ""
PartDate = .Range("A" & RowCount)
If PartDate = RequestDate Then
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
MsgBox ("Error: Cannot find Vehicle : " & Vehicle)
'.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
MsgBox ("Error: Cannot find Part Number : " & PartNo)
'.Range("A" & NewRow) = PartNo
'.Cells(NewRow, VehicleCol) = QTY
'NewRow = NewRow + 1
Else
.Cells(c.Row, VehicleCol) = QTY
End If
End With
End If
RowCount = RowCount + 1
Loop
End With
With Sheets("Unique Item List")

'Add Sum formula
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B2").FormulaR1C1 = "=Sum(R[]C3:R[]C" & (LastCol) & ")"
.Range("B2").Copy Destination:=.Range("B2:B" & (LastRow))
End With
End Sub


"Vikram Dhemare" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default Required Columner Data
Hello Mr. Joel,

Excellent !. Its woking.
Thanks a lot.
Again I am giving you trouble, there is one more field named Del_Id. This is
unique for one
consignement. One vehicle may have roateted twice / thrice a day The result
must be accordingly.
When second cosignment goes out then Del_Id changes.
Hope I am explaining correctly. Can we get such result.

Awating your favourable reply.

--
Thanks,
Vikram P. Dhemare


"Joel" wrote:

Try these changes. I commented out some old code, may some changes, and
added new code.
1) Removed clearing of "Unique Item List"
2) Commented out code that wrote headers (row and columns) on "Unique Item
List" to use your standard template. Added Error messages if the headers
didn't exist.
3) Added first Instruction that sets the filter Date you are looking for.
You need to modify this instruction to use you Input box. Since the date in
the Input box is a string you need to add DateValue (like I did in the code)
to convert to a microsoft serial date.
4) Added code to add only the date required.
5) It is simple to convert to drop the hours and minutes from the input
time. All you need to do is add INT() to the serial time to get just the
date. for example

MyTime = "1/1/08 22:33:25"
MyDate = Int(DateValue(MyTime))

SerialDate in Microsoft is a number with Day 1 = Jan 1,1900. Each day is
equal to 1 so 2 = Jan 2,1900. Each hour = 1/24 and each minute = 1/(24 * 60)
each second = 1/(24 * 60 * 60)
5) to



Sub GetUniqueData()

RequestDate = DateValue("22/09/2008")

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) < ""
PartDate = .Range("A" & RowCount)
If PartDate = RequestDate Then
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
MsgBox ("Error: Cannot find Vehicle : " & Vehicle)
'.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
MsgBox ("Error: Cannot find Part Number : " & PartNo)
'.Range("A" & NewRow) = PartNo
'.Cells(NewRow, VehicleCol) = QTY
'NewRow = NewRow + 1
Else
.Cells(c.Row, VehicleCol) = QTY
End If
End With
End If
RowCount = RowCount + 1
Loop
End With
With Sheets("Unique Item List")

'Add Sum formula
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B2").FormulaR1C1 = "=Sum(R[]C3:R[]C" & (LastCol) & ")"
.Range("B2").Copy Destination:=.Range("B2:B" & (LastRow))
End With
End Sub


"Vikram Dhemare" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Required data entry SixBowls Excel Discussion (Misc queries) 6 November 4th 09 02:23 PM
Required data in one sheet Junaid Excel Worksheet Functions 4 November 26th 08 11:18 AM
Conditional Formatting based on data in adjacent cell & restrict save without required data bjohnson Excel Programming 1 August 28th 07 10:27 PM
Help required with Consolidating Data JosephJohnSmith Excel Worksheet Functions 1 October 8th 05 01:56 AM
Removing lines of data that do not contain required data (macro) Sean[_7_] Excel Programming 1 May 21st 04 03:53 PM


All times are GMT +1. The time now is 11:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement