Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Correlation - Which Analytical Function to Use
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 |
#2
|
|||
|
|||
Pasko1, I'm pretty unclear about precisely what you are trying to do, however, there are two suggestions I would make. One fairly easy to learn and use, the second more complex but more powerful. First suggestion is to use the Auto Filter. To do this, select your entire range of data, then go to DATAFILTERAUTOFILTER. This will put dropdown arrows into each of your column headings, with which you can filter you data. If for instance, one of your column headings is a date (as it appears to be) and the column under that contains route numbers, you can filter for a particular route number and only those rows will remain visable. Then you can use the drop down to select "All" and your entire data will be visable once more. The second method involves using a pivot table, very powerful, somewhat complex. If you go to DATAPIVOT TABLE and PIVOTCHART REPORT, a wizard will be launched to help you develop the pivot table. I encourage you to avail yourself of the help files to study the advanced techniques for both of these built-in Excel features. HTH -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=473923 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |