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				

0
Pasko1 (6)
10/6/2005 8:09:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
673 Views

Similar Articles

[PageSpeed] 19

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 DATA>FILTER>AUTOFILTER. 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 DATA>PIVOT 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?action=getinfo&userid=4545
View this thread: http://www.excelforum.com/showthread.php?threadid=473923

0
10/6/2005 11:41:49 PM
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
0
petersod (12005)
10/6/2005 11:54:52 PM
Reply:

Similar Artilces:

using files offline
My main work files are on a PC. I can share these files on the mac while I am on the same wireless network. I want to be able to work on these files offline on my mac when I am not at home. Any suggestions? When I had a PC laptop I could do this just by setting it up to work offline but this doesn't seem to be an option with the mac. I copy the files I am likely to need to a folder on the Mac before I leave the network. If that won't work for you, post again and tell us why. There are other options. On 18/02/08 1:18 AM, in article ee8dc5f.-1@webcrossing.caR9absDaxw, "naomi@of...

CDF Migration using current info
I have a basic handling of CDF and have successfully migrated over data. But, I'm wondering how does CDF handle data that needs to be linked to data that is currently in the working CRM? For example, I want to import a product into the CDF. I have an existing Subject tree in our working CRM. How to I relate a node on the existing subject tree to this product that I have in the CDF? This example can be generalized as I can't seem to do any sort of relation of existing information and the soon-to-be-migrated data on the CDF. Any ideas? tia, Gary How about I make it easier,..........

Using VBScript to Check for Network Share File?
I've been trying to get the following script to report on any new files that might be added to a folder (a Ricoh AIO sends faxes to the folder automatically). Each user on the network needs to be alerted to the incoming faxes stored in the shared folder. Running it on the "host" works fine - it reports any folder additions. Running it on a Windows Server 2008 share works fine (that's where I tested it). However, running it on a peer-to-peer Workgroup returns a runtime error in line 2, character 1 when the 10 second "poll" period elapses: 800A01CE - ...

How can I use a cell reference in Sumproduct array formula?
I have a table with dates down the and employee numbers across the top. I have SUMPRODUCT formulas in all the cells to gather data from named arrays from a database in the spreadsheet. I'd like to avoid munually changing (either individually or with REPLACE) date and employee number references in each formula in each cell. my formula looks like: {=SUMPRODUCT((Date=DATEVALUE("11/4/04"))*(Audempno=33758)*(Units))} also, I used a previous suggestion from this forum on another similar spreadsheet (successfully...for fiscal 3/05)with copying the whole spreadsheet and changing the d...

Compilator C++ freeware for commercial use
Is there a freeware compilator C++ for commercial use? On Tue, 22 May 2007 09:40:42 +0200, karol1208 <karol1208(Cut this)@gazeta.pl> wrote: >Is there a freeware compilator C++ for commercial use? Microsoft Visual C++ 2005 Express Edition? MrAsm GNU C++. joe On Tue, 22 May 2007 09:40:42 +0200, karol1208 <karol1208(Cut this)@gazeta.pl> wrote: >Is there a freeware compilator C++ for commercial use? Joseph M. Newcomer [MVP] email: newcomer@flounder.com Web: http://www.flounder.com MVP Tips: http://www.flounder.com/mvp_tips.htm I think, as MrAsm suggested, you can use th...

Using the VS .Net .msi setup
Hi, Based on Newcomer's latest recommendation I investigated using the built-in installer package .msi generator for deployment, so that I can use dynamic MFC dll. I added a setup project to my solution, that was ok, it detected dependencies OK. But while compiling it warns that two dll's should be excluded becuase they are under windows protection. MSVFW32.dll and AVIFIL32.dll. If I exclude them, how will it then work? Will that force it to include them in the executable, or is it always certain that any windows o/s already has them? Michael Archon Sequoia Nielsen wrote: &g...

GetPivotData function
I set up a spreadsheet in Excel 2002 using the GetPivotData function. Now I need to utilize the same function in Excel 2000 but it does not seem to have the same power. Does anyone know if there is a way to utilize the GetPivotData function in Excel 2000 to reference multiple fields (5) in a pivot table? In Excel 2002, the GetPivotData formula may be created automatically, when you reference a cell in the PivotTable. You can manually create a GetPivotData formula in Excel 2000, and its arguments are different than those in Excel 2002. Look in Excel's Help files for information ...

If Function #8
Trying to create an IF function with different criteria - ie formula i A1: =IF(E1=1,"X",0)+IF(E1=2,"W",0)+IF(E1=3,"Y",0)+IF(E1=4,"Z",0) Trying to make cell A1 change to either X,W,Y,Z so it can be counted o a separate shee Attachment filename: if function query.xls Download attachment: http://www.excelforum.com/attachment.php?postid=52065 -- Message posted from http://www.ExcelForum.com Hi try =IF(E1=1,"X",IF(E1=2,"W",IF(E1=3,"Y",IF(E1=4,"Z","")))) >-----Original Message--...

How to export the search result when using Advanced find in Outloo
I am using Outlook 2003 and Exchange 2003 and want to count the total email size used at different timeline e.g. current month, last month, or a period of dates specified etc. but seems Outlook does not provide a ready-made view for such sort. I therefore use the Advanced find to specific the criteria, search result displayed but it does not give me what I want i.e. total size, unless it could export out to a table. Can someone tell me how to export out the search results? Maybe this works for you: In the advanced search click File/Save as search Folder. Then in that search...

Hooking windows creation functions
I use some library to hook win32 functions (similar to Detours). I want to hook every Windows function that creates a window on the screen and extract its HWND. What are the needed functions to be hook? Regards Mandy I don't have the SDK docs handy but you should be able to see the different types of hooks available to the hook API functions. What I would like to point out to you is that these type of hooks are a little tricky. The reason is that they can occur in other processes. In order for this to work, your hook function must be in a DLL, which gets mapped into the address s...

REQ: Need help with function...
Hi, can anyone help??? I have a sheet with... Sales Stock Weeks Est. Total Cover 220 1000 3.6 250 1100 3.9 300 1150 4.3 280 1050 4.2 ----- 1050 ----- 270 1075 280 1075 250 1100 220 1200 ----- 1020 ----- 200 1250 Cover is worked out by taking away the next weeks sales estimate until less than a week and dividing the remainder to get part week. Example... 1000-250=750 75...

how to use Common Controls version 6
How can i use common controls version 6 controls in my MFC applications.Is it possible to use a preprocessor #define . THank you Which one are you trying to use? Many of them are built in already: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcsample/html/_sample_MFC_CMNCTRL1.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcsample/html/_sample_MFC_CMNCTRL1.asp Tom "Kenroy" <Kenroy@discussions.microsoft.com> wrote in message news:B3CB4AAA-F84A-49DC-88BE-1B3356E6CC0A@microsoft.com... > How can i use common controls version 6 co...

Select field from table not used by form
I'm working in Access 97 and am mostly a novice. I have a form linked to a table called "Participants". One of the fields is [Company]. I've got a control for it called [Companytxt]. The [Company] field exists in another table called "Pipeline". All the data in the "Participants" table is new. When I enter a new record, I want to choose the the [Company] name from the Pipeline table so I can set up a future relationship between the tables. I would like to choose from the list of existing companies in the "Pipeline" table and update the name of ...

functionality in Fix. Asset module to enter other expenses/investm
functionality required in Fixed Asset module to enter other expenses/investments aside from depreciation expense, in order to track Asset wise all other expenses / investments. for example: We have a building as our fixed asset item, the system has the ability to record the depreciation expense through the Module, but don't have any other option to record other expenses incurred on that building like repair & maintainance as well as no option to record any further enhancement in building like development of a new floor, rooms & etc. The system should be able to track and re...

Moved SharePoint DB using SQL Alias unable to backup with DPM2010 RC
Hello: I am unable to backup our SharePoint 2007 farm due to error 956. DPM cannot protect your Windows SharePoint Service farm until you install agents on the following servers: <Virtual Server IP> Farm consists of 3 WFEs and a SQL 2008 Failover Cluster with 2 nodes, all running Windows 2008 R2. Agent is installed on 1 WFE and both SQL servers. The SQL connection is aliased. When I run ConfigureSharepoint -ResolveAllSqlAliases it reports each of the databases with an acceptable clustername. (eg Database Name: SharePoint_Config, Alias Name:<Alias IP>, Logical Pa...

Pivot Table using OLAP cubes
I am creating a pivot table using OLAP cubes. I need to group one of my selections within my pivot. However, the group option is grayed out when I right click on it. This option is supposed to be availble in Excel 2002. Is this just a problem with 2002 or am I doing something wrong? ...

Using Contact categories to sort by color
In O2000, different entries into my contact list appeared in different colors depending on their category. Is there a way to do this on O2003? I haven't been able to find anything on the MS site, either in the Knowledge Base or in the MS Office 2003 section, or on Slipstick. Thanks Henry did you check the Automatic Formatting settings for the view? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "HenWin" <henwinNO SPAM@atlantech.n...

Using CComPtr with a DLL
Hi, I'm a bit new to COM/ActiveX and I can't find any appropriate help. I have a DLL which I have registered using regsvr32 and I need to use it in my code, trouble is I just have the DLL and no accompanying .h file. I have added the line struct __declspec(uuid("CD4F2C35-3D88-411B-B8C1-F201C2B10686")) ICSEncrypt; but when I try to declare it as: CComPtr<ICSEncrypt> pCSE; I get undefined type errors in atlbase.h etc... Can someone please give advice or point me to an atricle which does not involve including a .h file. thanks Chris Chris Baker wrote: > ...

Using ^F (Find) in the VS
Having long exposure to *NIX systems and tools, I think there's a language barrier between me and the Find Dialog in the Visual Studio. I wanted to search for an occurrence of an equal sign followed by a tab-character in the current file. If this were, say, the nedit-editor in Linux, I'd check the "regular expression" box in the dialog and search for the string '=\t' (without single quotes). Doing this in VS finds nothing. searching for a tab with '\t' just finds the occurrence of the 't' characters. That's not what wanted. I tried to enter a ...

Count Function
I am creating a report for pupils, grouped by school then collection time. I have managed to count the number of pupils being collected using the formula =count([Child Last Name]) However i cannot seem to get an overall total for each collection time in the footer, using the same formula results in 'Error' occurring in the layout? You can do this in the REPORT footer. You canNOT do this in the PAGE footer. Does that help? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County ELo wrote: > I am creating a r...

GP 9.0 - Analytical Accounting Options - Post through to GL
Hello, One of the options in the Analytical Accounting Options window (Tools >> Setup >> Company >> Analytical Accounting >> Options) is 'Post through to GL for Trx posting'. I'm not clear of the purpose of this option. 1. Does this override the checkbox 'Post to GL' in the Posting setup? 2. To what transactions this option apply? Thanks in advance...Murali ...

using macros to autofilter using CURRENT date
What is the macro I need for autofiltering using the current date? I want the macro to update every day and know the date. --- Message posted from http://www.ExcelForum.com/ || What is the macro I need for autofiltering using the current date? || I want the macro to update every day and know the date. || || || --- || Message posted from http://www.ExcelForum.com/ typing =now() will enter the current date into a cell. so you could have a cell containing this function and a autofilter referencing this cell, assuming the dates are stored in col A Worksheets("sheet1").Range("...

formula similar to "countifs" function in excel 2007
I need a formula similar to the countifs function offered in excel 2007. I'm using excel 2003 and can't upgrade to 2007. I want to add together cars at a particular location. The location names are in one column and the type of car is in another. In 2007, the formula would be =countifs(a2:a5,"new york", b2:b5,"ford"). What can I do to perform the same function in 2003? Please help. Try this: =3DSUMPRODUCT(--(A2:A5=3D"new york"),--(B2:B5=3D"ford")) Hope this helps. Pete On Apr 19, 5:13=A0pm, embee <em...@discussio...

Used to Word Perfect, Do I need Pub?
Hi, I've been creating flyers in WP and wonder if I'd be better off in Publisher. I paste, size, position, border graphics. I write text and play with fonts. I need to be able to email my flyers to a computer illiterate group, who can barely download a file and don't have WP, can't download Reader for PDFs. Ideally, I need to send the flyers in Word, but trying to manipulate graphics in Word is miserable. Any suggestions would be much appreciated. After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Mather <anonymous@discussions.microsoft.com>....

Importing Items using the QSImport Utility
I am trying to import an excel file with my items on it and every time i export it to csv format it recodes the 12 digit UPC code to Scientific notation no matter what i do. I read through some of the recent responsed to other postings but all i can find is the AutoFit option and that doesnt work. So any help would be greatly appreciated. In your file format upc as custom and in the box put 12 zeros works every time. "ILMARcorp" <ILMARcorp@discussions.microsoft.com> wrote in message news:0F55979E-0A2D-4CC1-8ED9-761C55708B26@microsoft.com... >I am trying to import an...