Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Text manipulaion (summarizing customer records) Index function probably not good choice
I have a data set in csv format (over 100,000 rows) that I broke into 2
files. the layout is as such (a customer may not purchase each month) CUST# Sales_mon Invoice Customer A Jan-2001 $1000 Customer A Feb-2001 $1202 Customer A Mar-2002 $34 Customer A Mar-2003 $3333 Customer A Apr-2003 $889 Customer A May-2003 $1232 Customer A Jun-2003 $33 Customer C Jul-2004 $4 Customer C Aug-2001 $893 Customer M Jan-2002 $989 Customer M Mar-2002 $76 Customer M Apr-2002 $52 Customer M Jun-2003 $73 ........ I'd like to transpose the file for input into an analysis tool in the following format: CUST# Jan-2001 Feb-2001 Mar-2001 ..... Mar-2002 .........................................Dec-2004 Customer A $1000 $1202 0 $34 Where I would have an entry for each month in a column, this would reduce my records down to 1 row per customer with the column heading as the month that customer purchased. WHat's the best plan of attack, many of the excel tools assume a single occurance (list tools such as index & hlookup etc.) Input very much welcome! Assume skill level = moderate here. |
#2
|
|||
|
|||
A Pivot Table with the customer number as the row field, the sales month as
the column field, and the Invoice as the data field will give you this layout. Whether Excel can handle 100,000 rows on two sheets is another issue. You might have to create a pivot table on each sheet, then create a 3rd table using the 1st two as the sources. BUT..... your data is a "piece of cake" for Access. You could import the entire data set into a table there, then create a crosstab query and export it. BTW, with either Excel or Access, you are limited to 255 columns. On Tue, 01 Feb 2005 06:19:28 GMT, "Paul Buob" wrote: I have a data set in csv format (over 100,000 rows) that I broke into 2 files. the layout is as such (a customer may not purchase each month) CUST# Sales_mon Invoice Customer A Jan-2001 $1000 Customer A Feb-2001 $1202 Customer A Mar-2002 $34 Customer A Mar-2003 $3333 Customer A Apr-2003 $889 Customer A May-2003 $1232 Customer A Jun-2003 $33 Customer C Jul-2004 $4 Customer C Aug-2001 $893 Customer M Jan-2002 $989 Customer M Mar-2002 $76 Customer M Apr-2002 $52 Customer M Jun-2003 $73 ....... I'd like to transpose the file for input into an analysis tool in the following format: CUST# Jan-2001 Feb-2001 Mar-2001 ..... Mar-2002 ........................................Dec-2004 Customer A $1000 $1202 0 $34 Where I would have an entry for each month in a column, this would reduce my records down to 1 row per customer with the column heading as the month that customer purchased. WHat's the best plan of attack, many of the excel tools assume a single occurance (list tools such as index & hlookup etc.) Input very much welcome! Assume skill level = moderate here. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|