SORTING DATA

sue.rob · May 22nd, 2002, 05:22 AM

I receive a production schedule weekly that has data in it relating to
various production lines A~F. The worksheet has "BLOCKS" of work for each
machine that vary in the number of rows they contain.

E.G.. Column B is the BLOCK column. B5 has a "1" in it to signify it is
block 1 and B9 has "C" in it to signify that the work should go on machine
C. B10 will then have "2" in it for Block 2 and B16 will have "D" in it for
machine D and so on for each block.

What I want to do is sort the data by machine letter. I.E. I want all of the
work for machine A at the top followed by the work for machine B etc..

The data goes from columns B to Z but the number of rows in each block can
vary but always starts with a number and ends in the letter of the machine
it is to be run through.

Is there any way I can sort this data by machine type ?

Thanks for your help.

Anne Troy · May 22nd, 2002, 05:34 AM

You need to make a new column for each your Block and for your machine, or at least one column that combines them, or remove all the numbers in column.
(Always save to a new file name to test.)
To remove the block numbers, select your column B cells (NOT the whole column). Hit Edit-Go to-Special-Choose Constants, and uncheck all except numbers, OK. Then hit your delete key.

Select all of them again. Hit Edit-Go to-Special-Blanks. If the first machine letter is in B5 (and this is the first cell you selected, but it is NOT selected now because it is NOT blank), type =B5 and hit Ctrl-Enter.

What that will do is put the appropriate machine letter into each cell. You'll want to then copy (while it's all still selected) and then Edit-Paste special, Values to remove the underlying formula. I hope this makes sense, 'cause it's really quick once you get the hang of it.

_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-05-22 04:37 ]

Abdc · May 22nd, 2002, 07:56 AM

Dreamboat's second paragraph reads :-

"Select all of them again. Hit Edit-Go to-Special-Blanks. If the first machine letter is in B5 (and this is the first cell you selected, but it is NOT selected now because it is NOT blank), type =B5 and hit Ctrl-Enter."

Change this to :-

Select all of them again. Hit Edit-Go to-Special-Blanks.
Type =B6 and hit Ctrl-Enter.

Anne Troy · May 22nd, 2002, 08:13 AM

ABDC: If the machine letter is in B5, presumably B6 is blank. No?

Abdc · May 22nd, 2002, 09:04 AM

Quote:

On 2002-05-22 07:13, Dreamboat wrote:
ABDC: If the machine letter is in B5, presumably B6 is blank. No?

But according to the original question, the machine letter cannot be in B5.
B5 should be the first blank cell of the first block of blank cells before the first letter appears. And then after the first letter the next block starts.

sue.rob · May 22nd, 2002, 11:37 AM

Thanks for the reply.

This didn't work as planned as when I used it the cells in column B just referenced to the cell 3 rows below it to give the machine letter.
As there are varying numbers of rows between the letters in column B of the original list (as each block is a different numbr of rows long some only 2 rows, some

I ended up with some of the blocks having mixed letters.

Any other ideas ?

thanks.

dimrod · May 22nd, 2002, 05:49 PM

If it doesn't work, you have not followed Dreamboat's/Abcd's instructions properly.

Here is what they said, step by step :-

- Select column B, copy and paste to a blank column (let's say column C).

- Delete the contents of C1:C4

- Select column C.

- Go to Edit>GoTo>Special>Constants>Numbers(de-select Text/Logicals/Errors). This step will select all the cells containing the Block numbers.

- Press the keyboard Delete key.

- Select the range from C5 down to the last data row.

- Go to Edit>GoTo>Special>Constants>Blanks. This step will select all the blank cells.

- Type =B6 and press Ctrl+Enter.

- Sort your data by column C.

- Delete column C.

[ This Message was edited by: dimrod on 2002-05-22 16:50 ]

sue.rob · May 23rd, 2002, 02:13 PM

I followed the instruction corrctly but I think my question wasn't clear. I need to keep the data in each block together and sort the "blocks" by the machine letter in them.

E.G. Block 1 covers B5:AA10, it has a 1 (block 1) in B5 and B (machine B) in B9.

Block 2 covers B11:AA16 and has 2 (Block 2) in B11 and C in B15.

Block 3 is B17:AA24, B17 is 3 B23 is C.

And so the file goes on, there are 114 blocks in total ending at B587.

Each block holds data about the product type, how many, what day to make etc.

What I want to do is keep the "blocks" together but sort them by machine so that all blocks for machine A are together etc...

I hope that all makes sense and hope someone can help.

Thanks for your time.

Arviragus · May 23rd, 2002, 06:43 PM

Dimrod's steps should achieve what you want.
Either you must be doing something wrong, or the data in column B are not constants.

If the data are not constants, some small changes to the procedure are necessary.

At what step do things not happen the way they are supposed to?

sue.rob · May 24th, 2002, 11:06 AM

I think you've misunderstood the layout of the sheet I have. When following dimrods method I end up getting rid of all the numbers and then when I CTRL-ENTER I just make the blank cells in column C = the cell below in column B. Because the first block only has data in B5 (1) and B9 (C) the CTRL-ENTER only makes the cells turn to zero,s. I'll look on the board how to paste a picture of part of the sheet so it's clear.

thanks for the replies so far.

May 22nd, 2002, 08:13 AM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	ABDC: If the machine letter is in B5, presumably B6 is blank. No? __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 05:22 AM	#1
sue.rob New Member Join Date: May 2002 Location: Vauxhall Motors Posts: 10	I receive a production schedule weekly that has data in it relating to various production lines A~F. The worksheet has "BLOCKS" of work for each machine that vary in the number of rows they contain. E.G.. Column B is the BLOCK column. B5 has a "1" in it to signify it is block 1 and B9 has "C" in it to signify that the work should go on machine C. B10 will then have "2" in it for Block 2 and B16 will have "D" in it for machine D and so on for each block. What I want to do is sort the data by machine letter. I.E. I want all of the work for machine A at the top followed by the work for machine B etc.. The data goes from columns B to Z but the number of rows in each block can vary but always starts with a number and ends in the letter of the machine it is to be run through. Is there any way I can sort this data by machine type ? Thanks for your help.

May 22nd, 2002, 05:34 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	You need to make a new column for each your Block and for your machine, or at least one column that combines them, or remove all the numbers in column. (Always save to a new file name to test.) To remove the block numbers, select your column B cells (NOT the whole column). Hit Edit-Go to-Special-Choose Constants, and uncheck all except numbers, OK. Then hit your delete key. Select all of them again. Hit Edit-Go to-Special-Blanks. If the first machine letter is in B5 (and this is the first cell you selected, but it is NOT selected now because it is NOT blank), type =B5 and hit Ctrl-Enter. What that will do is put the appropriate machine letter into each cell. You'll want to then copy (while it's all still selected) and then Edit-Paste special, Values to remove the underlying formula. I hope this makes sense, 'cause it's really quick once you get the hang of it. _________________ TheWordExpert [ This Message was edited by: Dreamboat on 2002-05-22 04:37 ]

May 22nd, 2002, 07:56 AM	#3
Abdc Join Date: Mar 2002 Posts: 20	Dreamboat's second paragraph reads :- "Select all of them again. Hit Edit-Go to-Special-Blanks. If the first machine letter is in B5 (and this is the first cell you selected, but it is NOT selected now because it is NOT blank), type =B5 and hit Ctrl-Enter." Change this to :- Select all of them again. Hit Edit-Go to-Special-Blanks. Type =B6 and hit Ctrl-Enter.

May 22nd, 2002, 11:37 AM	#6
sue.rob New Member Join Date: May 2002 Location: Vauxhall Motors Posts: 10	Thanks for the reply. This didn't work as planned as when I used it the cells in column B just referenced to the cell 3 rows below it to give the machine letter. As there are varying numbers of rows between the letters in column B of the original list (as each block is a different numbr of rows long some only 2 rows, some I ended up with some of the blocks having mixed letters. Any other ideas ? thanks.

May 22nd, 2002, 05:49 PM	#7
dimrod Join Date: May 2002 Posts: 73	If it doesn't work, you have not followed Dreamboat's/Abcd's instructions properly. Here is what they said, step by step :- - Select column B, copy and paste to a blank column (let's say column C). - Delete the contents of C1:C4 - Select column C. - Go to Edit>GoTo>Special>Constants>Numbers(de-select Text/Logicals/Errors). This step will select all the cells containing the Block numbers. - Press the keyboard Delete key. - Select the range from C5 down to the last data row. - Go to Edit>GoTo>Special>Constants>Blanks. This step will select all the blank cells. - Type =B6 and press Ctrl+Enter. - Sort your data by column C. - Delete column C. [ This Message was edited by: dimrod on 2002-05-22 16:50 ]

May 23rd, 2002, 02:13 PM	#8
sue.rob New Member Join Date: May 2002 Location: Vauxhall Motors Posts: 10	I followed the instruction corrctly but I think my question wasn't clear. I need to keep the data in each block together and sort the "blocks" by the machine letter in them. E.G. Block 1 covers B5:AA10, it has a 1 (block 1) in B5 and B (machine B) in B9. Block 2 covers B11:AA16 and has 2 (Block 2) in B11 and C in B15. Block 3 is B17:AA24, B17 is 3 B23 is C. And so the file goes on, there are 114 blocks in total ending at B587. Each block holds data about the product type, how many, what day to make etc. What I want to do is keep the "blocks" together but sort them by machine so that all blocks for machine A are together etc... I hope that all makes sense and hope someone can help. Thanks for your time.

May 23rd, 2002, 06:43 PM	#9
Arviragus Join Date: Mar 2002 Posts: 33	Dimrod's steps should achieve what you want. Either you must be doing something wrong, or the data in column B are not constants. If the data are not constants, some small changes to the procedure are necessary. At what step do things not happen the way they are supposed to?

May 24th, 2002, 11:06 AM	#10
sue.rob New Member Join Date: May 2002 Location: Vauxhall Motors Posts: 10	I think you've misunderstood the layout of the sheet I have. When following dimrods method I end up getting rid of all the numbers and then when I CTRL-ENTER I just make the blank cells in column C = the cell below in column B. Because the first block only has data in B5 (1) and B9 (C) the CTRL-ENTER only makes the cells turn to zero,s. I'll look on the board how to paste a picture of part of the sheet so it's clear. thanks for the replies so far.