Sorting and Formatting

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

Would you mean something along this line:
Book1
DEFGHI
15
16Original data
17
18initialrevisednameamt
1910200Inventory related8
2010500Inventory3
2110500Inventory - on loan1
2210 Total12
23
2420500Accts Rec-trade9
2520200Accts Rec related12
2620 Total21
27Grand Total33
28
29As redefined
30
31initialrevisednameamt
3210200Inventory related8
3320200Accts Rec related12
34200 Total20
3510500Inventory3
3610500Inventory - on loan1
3720500Accts Rec-trade9
38500 Total13
39Grand Total33
40
41
Sheet2


plettieri
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,137,335
Messages
5,680,887
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top