Sorting and Formatting

[srizki]

I receive a worksheet every month from another department, and then I sort that worksheet the way we want by moving rows up or down by cutting and pasting. I do this every month. I am sure there must be a faster way to do it, can anyone please help me.

Thanks

 

[xcellnoob]

Autosort???

 

[srizki]

No, the sheet I receive is sorted by the area where assets are located, and subtotals that way; I cut and paste by assets regardless of area, and then sub-total it. Therefore, in my worksheet I may have five rows and sub-total and six rows and subtotal and three rows and subtotal, original sheet may have set up differently three rows and subtotal, seven rows and subtotal and four rows and sub-total, and again the row in original sheets are at different place.

 

[plettieri]

Hi:

If you have set format that is used for comparative reasons, you might consider giving each account (or asset type) a code (using a lookup table- preparing the lookup table once) then sorting or grouping based on that criteria.



plettieri

 

[srizki]

I did that but there are blank spaces after each sub-total, (my sub-totals are different from the original one), that gives #N/A error and in sorting all #N/A moves to the bottom. any solution?

 

[plettieri]

Hi:

Would you mean something along this line:

Book1
	D	E	F	G	H	I
15
16		Original data
17
18		initial	revised	name	amt
19		10	200	Inventory related	8
20		10	500	Inventory	3
21		10	500	Inventory - on loan	1
22		10 Total			12
23
24		20	500	Accts Rec-trade	9
25		20	200	Accts Rec related	12
26		20 Total			21
27		Grand Total			33
28
29		As redefined
30
31		initial	revised	name	amt
32		10	200	Inventory related	8
33		20	200	Accts Rec related	12
34			200 Total		20
35		10	500	Inventory	3
36		10	500	Inventory - on loan	1
37		20	500	Accts Rec-trade	9
38			500 Total		13
39			Grand Total		33
40
41
Sheet2

plettieri

 

[srizki]

Yes that is right, and when I want to lay it out may way withouto going through all this troubble.
thanks

 

[plettieri]

Hi:

Well, granted the setting up the coding in a lookup table is a one shot deal. You will need to recode the accounts at some point to house them into your groupings. I utilized the advanced filter, coping the accounts to another area and subtotaled them.

Another direction may be to try a pivot table with the grouped data you extracted from the file submitted to you.

Hope this helps,

plettieri

 

[srizki]

plettieri, you are right, I am thinking of applying your this answer. in this post I used the wrong heading that is why I did not get many responses, But thankfully you responded me.
Thanks