# Solved Hello, Novice needs help with sort and copy. Thx

May 9, 2016 at 11:51:42
Specs: Windows 7
 Hello, I am a novice in a new job that uses Xcel 2013. I have a spreadsheet that lists line item services and corresponding taxes by each state involved( 2 digit abbreviation). Some states may have 10 lines others may have 200 lines. They are subtotaled by state using the subtotal function under the data tab. What I need to do now is to take each of those subtotals and copy just the state abbreviation and tax amount into a new location so that I have all 50 states listed in one column (only 50 rows not 100s) and the next column has the corresponding taxes. I need it this way so I can cut and paste just these two (50rows*2) columns into an invoice all at one time. VS copying one line at a time. Is there a formula or function I can use easily? I tried using the advanced sort but cant seem to make it cooperate. Thanks

See More: Hello, Novice needs help with sort and copy. Thx

#1
May 9, 2016 at 12:31:05
 Look at the SUMIF solution I offered in the following thread. Let me know if that makes sense/works for you.http://www.computing.net/answers/of...

#2
May 9, 2016 at 15:23:53
 Thanks, I did look at sumif, I cant see how to have it list the sums in a listing without any gaps. Thats how I got the totals for revenue and taxes on each line. Then used the subtotal to sum up each states taxes. What I want to do now is use a formula on the Taxes column to select only the subtotal sum for each state and copy that info into another location all in order (50 rows) so I can cut and paste the whole group as one cut into an invoice xcel. I tried the sumif but ended up with 50 rows spread out across 3800 rows. I only need one row (subtotal) from each state. Im trying to avoid having to cut 3750 rows just to get the 50 I need. So if something can pull them out and group them that would be great. If you know how to use sumif in that fashion do tell.

#3
May 9, 2016 at 16:19:46
 Remove the subtotals, create your filtered list on a different sheet, then reapply the subtotals.Use the SUMIF function against the filtered list.

Related Solutions

#4
May 9, 2016 at 18:28:45
 Another possible option, depending on how your sheet is set up...If the Subtotal titles are in the same column as the states, then use the Advanced filter option with a Criteria of <>*Total*, Unique records only.