PDA

View Full Version : Consolidating Order Sheet



jamshed89
05-03-2016, 05:12 AM
Date
Order No
Customer Name
Address
Phone Number
Order Amount
Order Count



01/05
xx01
Smith
12 xx road
123456
150
1



01/05
xx02
John
14 xx road
987652
190
1



02/05
xx03
Smith
12 xx road
123456
100
1



03/05
xx04
Smith
12 xx road
876541
200
1


This is the format of my Order Book, now I want to consolidate this report to track all the repeat orders. I tried consolidate function on excel but the problem is consolidation is done on the basis of Phone Number and if i tried to include 'Customer Name or Address Column' it becomes a mess. So my consolidated report has only numbers and i wanted to include all the data fields and if possible mention of all the dates of ordering for any particular customer. On the table i have prepared a sample, now Smith has ordered thrice but for third order he has provided a different phone number so is there any way I can track this down based on other criteria (like name and address) and be able to consolidate together. Actually I want it to look something like this:


Customer Name
Address
Phone Number
Alt Number
Total Amount
Order Count
Order Date
Order Date
Order Date


Smith
12 xx road
123456
876541
450
3
01/05
02/05
03/05


John
14 xx Road
987652

190
1
01/05
















I would really appreciate any help or any better suggestion.

Paul_Hossler
05-03-2016, 06:53 AM
Probably something like this

I did the Alt Phones a little differently since it was easier my way





Option Explicit

Const cModuleName As String = "ConsolidateOrders"

Sub ConsolidateOrders()
Dim wsOrders As Worksheet, wsSummary As Worksheet
Dim rOrders As Range, rOrdersData As Range
Dim iOrderLine As Long, iNameLine As Long, iDateColumn As Long

'ask user
If MsgBox("Consolidate Orders?", vbQuestion + vbYesNo, cModuleName) = vbNo Then Exit Sub

Application.ScreenUpdating = False

Set wsOrders = Worksheets("Orders")

'delete old summary, create new
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add.Name = "Summary"
Set wsSummary = Worksheets("Summary")
With wsSummary
.Cells(1, 1).Value = "Customer Name"
.Cells(1, 2).Value = "Address"
.Cells(1, 3).Value = "Phone Number"
.Cells(1, 4).Value = "Total Amount"
.Cells(1, 5).Value = "Order Count"
End With

'sort Orders by Date
Set rOrders = wsOrders.Cells(1, 1).CurrentRegion
With rOrders
Set rOrdersData = .Cells(1, 1).Resize(.Rows.Count - 1, .Columns.Count)
End With

With wsOrders.Sort
.SortFields.Clear
.SortFields.Add Key:=rOrdersData.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rOrders
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'add each date as to row 1
With rOrders
For iOrderLine = 2 To .Rows.Count
'new date
If .Cells(iOrderLine, 1).Value <> .Cells(iOrderLine - 1, 1).Value Then
wsSummary.Cells(1, 1).End(xlToRight).Offset(0, 1).Value = .Cells(iOrderLine, 1).Value
End If
Next iOrderLine
End With

'sort Orders by Name and phone
With wsOrders.Sort
.SortFields.Clear
.SortFields.Add Key:=rOrdersData.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=rOrdersData.Columns(5), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rOrders
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'add each name to column 1
With rOrders
For iOrderLine = 2 To .Rows.Count
'if new name add name address phone
If .Cells(iOrderLine, 3).Value <> .Cells(iOrderLine - 1, 3).Value Then
iNameLine = wsSummary.Cells(wsSummary.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
wsSummary.Cells(iNameLine, 1).Value = .Cells(iOrderLine, 3).Value
wsSummary.Cells(iNameLine, 2).Value = .Cells(iOrderLine, 4).Value
wsSummary.Cells(iNameLine, 3).Value = .Cells(iOrderLine, 5).Value

'not new, check phone
ElseIf .Cells(iOrderLine, 5).Value <> .Cells(iOrderLine - 1, 5).Value Then
iNameLine = Application.WorksheetFunction.Match(.Cells(iOrderLine, 3).Value, wsSummary.Columns(1), 0)
wsSummary.Cells(iNameLine, 3).Value = wsSummary.Cells(iNameLine, 3).Value & vbLf & .Cells(iOrderLine, 5).Value
End If

Next iOrderLine
End With

'read orders and summarize
'Orders
' 1 2 3 4 5 6 7
'Date Order No Customer Name Address Phone Number Order Amount Order Count

'summary
' 1 2 3 4 5 6
'Customer Name Address Phone Number Alt Number Total Amount Order Count Order Date Order Date Order Date
With rOrders
For iOrderLine = 2 To .Rows.Count

iNameLine = Application.WorksheetFunction.Match(.Cells(iOrderLine, 3).Value, wsSummary.Columns(1), 0)
'need to match on double
iDateColumn = Application.WorksheetFunction.Match(CDbl(.Cells(iOrderLine, 1).Value), wsSummary.Rows(1), 0)

'total
wsSummary.Cells(iNameLine, 4).Value = wsSummary.Cells(iNameLine, 4).Value + rOrders.Cells(iOrderLine, 6).Value
wsSummary.Cells(iNameLine, 5).Value = wsSummary.Cells(iNameLine, 5).Value + rOrders.Cells(iOrderLine, 7).Value
wsSummary.Cells(iNameLine, iDateColumn) = rOrders.Cells(iOrderLine, 1).Value
Next iOrderLine
End With

'change headers
With wsSummary
Range(.Cells(1, 6), .Cells(1, 6).End(xlToRight)).Value = "Order Date"
End With

Application.ScreenUpdating = True
Call MsgBox("All done", vbInformation + vbOKOnly, cModuleName)

End Sub

SamT
05-03-2016, 08:08 AM
Having been in business, I would set up each sub part of the entire system as interactive stand alone workbooks.

One WkBk for Customer (and Vendor) data, one for sales data with a separate sheet to give a running summary, and one to provide periodic summaries.

Each Book would have VBA User Forms to handle entering data.

The Customer sheet needs a Customer ID, (Primary Key,) and the Invoice sheet needs an Invoice number, (Primary Key.)

The Sales book would have a sheet from a template, (looks like an invoice,) for each sale with all the Items, Invoice Number, Date, Customer ID, Total Sale, and Total Sales Tax. It would also have one Running Summary sheet with a summary of each Sale, (looks like a list.) The Running Sales summary sheet would only have Invoice Number, Sales Date, Shipping Date, Customer ID, Total Sale, Total Sales Tax, and Balance Due.

I would include on the Customer Data sheet total purchases including sales tax and total balance due.

The Data Input form for the Sales book would push data to the Customer book and the Sales summary sheet. the Reports workbook would pull data as needed from the sales book and the customer book.

UhOh! having power troubles must post now.

Paul_Hossler
05-06-2016, 06:10 AM
Added 10 more lines to 'Orders' and the Summary sheet was regenerated