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.
|