Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make columns based on repeated rows??
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make columns based on repeated rows??
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 On Dec 17, 10:40 am, oli merge wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make columns based on repeated rows??
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. "oli merge" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make columns based on repeated rows??
You might take a look at Excel's pivot tables. This sounds like a natural
application for them. "oli merge" wrote: 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. "oli merge" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make columns based on repeated rows??
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 "PaulH" wrote: You might take a look at Excel's pivot tables. This sounds like a natural application for them. "oli merge" wrote: 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. "oli merge" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Outlining - collapse rows based on repeated column value | Excel Discussion (Misc queries) | |||
Looking up information based on Columns and Rows | Excel Worksheet Functions | |||
how do I make rows and columns appear when I print a spreadsheet | Excel Worksheet Functions | |||
Sum based on criteria in rows and columns | Excel Discussion (Misc queries) | |||
repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) |