LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

I would think that if you had your data laid out like:

Cust# Rt# Date Qty
a 1069 08/27/2005 1
a 1069 08/29/2005 1
a 1069 08/31/2005 1
a 1081 08/27/2005 1
a 1081 08/29/2005 1
a 1088 08/27/2005 1
a 1088 08/30/2005 1
a 1088 08/31/2005 1
a 1090 08/29/2005 1
a 1090 08/30/2005 1
a 1090 08/31/2005 1
a 3271 08/27/2005 1

(I wasn't sure if the 1's were quantities or just placeholders meaning yes)

Then you could use Data|Filter|autofilter to review any date/route/customer.

If you think you want to try that, you could use a macro to rearrange the data
into that tabular form:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 4).Value _
= Array("Cust#", "Rt#", "Date", "Qty")
oRow = 1

With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
For iCol = 3 To .Cells(iRow, .Columns.Count).End(xlToLeft).Column
If IsEmpty(.Cells(iRow, iCol)) Then
'do nothing
Else
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value
End If
Next iCol
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Pasko1 wrote:

I have a table of data as below. There are more Customers, and more dates'
worth of data, but they won't fit in this window. For a number of our
customers, we change delivery routes during the week in order to optimize our
shipping capacity. Basically, I am trying to see for each delivery route, on
a given day, how many of the same customers are on the same route. In other
words, when one customer changes from one route to another over a date range,
do other customers switch to the same routes on the same days?

Cust # Rt# 8/27/2005 8/29/2005 8/30/2005 8/31/2005
a 1069
a 1081 1
a 1088
a 1090 1 1
a 3271
b 1003 1
b 1076 1
b 1089 1
b 1101
c 1069
c 1071
c 1081 1
c 1082
c 1090 1
d 958
d 1069
d 1070
d 1072
d 1080 1
d 1082
e 1069


--

Dave Peterson
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 03:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 02:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 10:49 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 07:07 AM


All times are GMT +1. The time now is 07:33 PM.

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

About Us

"It's about Microsoft Excel"