Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Paul Buob
 
Posts: n/a
Default 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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 11:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"