Multiple order lines

[dunlopoil]

Hi,
I've posted this query before and had responses which, although helpful in other ways, didn't quite address what I was looking to achieve. I now think I've found a way around the problem and would be grateful for any feedback on whether it sounds ok.

Problem:
Database of sales records is not consolidated by part number, therefore instead of:

Order No.: 12345 / Part Number: 6789 / Qty: 3

The data appears as:

Order No.: 12345 / Part Number: 6789 / Qty: 1
Order No.: 12345 / Part Number: 6789 / Qty: 1
Order No.: 12345 / Part Number: 6789 / Qty: 1

I'm aware I can create Pivot Tables, but this doesn't quite work the way I want it to. What I want is for the source data to be tidied up so that it can then be queried in whatever way is required.



What I've since done is as follows:

• Concatenate the 'Order' and 'Part' columns into a new column 'Order&Part'
• Copy and paste back as values only
• Subtotal: at each change in Order&Part, sum in Qty column
• Select level 2 outline
• Go to visible cells only
• Select all the blank cells and Ctrl-Enter formula which equals the cell above.
• Select all the data- copy and paste to new worksheet as values.
• Delete the 'Order&Part' column as no longer required.
• This leaves me with the data appearing as it should, for example with a quantity of 3 on one row, instead of three separate rows of 1 qty.

Does this make sense?

Thanks,
Doug.

 

[RalphA]

Doug, I'm not too clear on what you have. Assuming that your "Order No.: 12345" is in cells B2:B4, "Part Number: 6789" is in C2"C4, and "Qty: 1" is in D2:D4, this worked for me.

1. I inserted a new, empty "C" column. Then, in E2 I entered,
=--MID(D2,FIND(" ",D2),LEN(D2))
and copied down, which gave me a 1 in each cell of E2:E4.
2. In cell C2, I entered,
="Qty: "&TEXT(SUM(E2:E4),"#")
3. I highlighted C2, did a Copy, Edit, Paste Special, OK.
4. I deleted cells D2:E4
Is this what you need? Of course, I realize that this is only the end of the solution. However, for the limited example you posted, I could do no more, without assuming too much and exposing myself to doing a lot of useless work.

 

[dunlopoil]

Doug, I'm not too clear on what you have. Assuming that your "Order No.: 12345" is in cells B2:B4, "Part Number: 6789" is in C2"C4, and "Qty: 1" is in D2:D4, this worked for me.

1. I inserted a new, empty "C" column. Then, in E2 I entered,
=--MID(D2,FIND(" ",D2),LEN(D2))
and copied down, which gave me a 1 in each cell of E2:E4.
2. In cell C2, I entered,
="Qty: "&TEXT(SUM(E2:E4),"#")
3. I highlighted C2, did a Copy, Edit, Paste Special, OK.
4. I deleted cells D2:E4
Is this what you need? Of course, I realize that this is only the end of the solution. However, for the limited example you posted, I could do no more, without assuming too much and exposing myself to doing a lot of useless work.

Hi Ralph,
Many thanks for your reply. I've posted an example sheet at http://www.box.net/shared/m39cfa84ko. Hopefully this will demonstrate the problem more clearly. As you will see, an identical part number is repeated over several rows, instead of the total quantity appearing on one row. I understand it's to do with warehouse picking lines, but for my purposes it's irrelevant. I just want the data to appear on one row, which would greatly reduce the size of the worksheet.

Regards,
Doug.

 

[RalphA]

Doug, I sent you a PM today. Please look for it.