Text manipulaion (summarizing customer records) Index function probably not good choice

[Paul Buob]

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.

 

[Myrna Larson]

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.