How to make columns based on repeated rows??

[oli merge]

I have regular order files come in where each product/ customer has one line.
for example, if a customer ordered 3 products then the would have three lines
in the order file, one for each product with repeated address information
across the 3 lines.

I am currently manually changing this so each customer has a single line and
each product in a seperate column (actually 3 columns product ID, product
desc, qty). SO to do this I subtotal the customers and add 3 times the amount
of columns the customer with the largest amount of products will require.

I then have to cut and paste the respective product IDs, product Desc and
qtys into the seperate columns and delete the duplicate lines.

How can I do this programattically? I am thinking about using Access
although am more familiar with Excel...

Thanks

 

[Dave F]

I'm not sure I understand your question but this is the type of thing
that is ideally suited for Access. You would have separate tables of
customer information and product information and create queries to
join the two tables, presumably based on order number.

You can (approximately) replicate this structure in an Excel workbook,
by creating separate tables on different sheets within the same
workbook, and running lookup functions between the two or three
tables. But this is harder (in my experience) to keep straight, as it
requires familiarity with complex lookup functions, in order to make
everything work properly.

Dave

 

[oli merge]

Hi,

Thats not quite what i wanted to do: I have no control over the format the
file is sent to me in and its a single file. I need just need to reformat it
so that its stored as a proper table rather than a list of orders if you
follow me.

 

[PaulH]

You might take a look at Excel's pivot tables. This sounds like a natural
application for them.

 

[oli merge]

Hi,

Thanks for the idea, if I first pivot by order number against product code
with qty as the data then I can use the pivot table with some vloopups to
make a new proper table.

Its quicked than manually cutting and pasting anyway! thanks