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

Report •


#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...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#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.

Report •

#3
May 9, 2016 at 16:19:46
✔ Best Answer
Remove the subtotals, create your filtered list on a different sheet, then reapply the subtotals.

Use the SUMIF function against the filtered list.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 10, 2016 at 10:50:27
DerbyDad03, Thanks. I could not get the advanced Filter to work at all. But I did manage to get =sumif to do what I needed. I had to copy all my states into another column and then did a =sumif using the state column which had the verbiage xx Total, the column that had my fifty states, and the coulmn that had the tax subtotals. all 3 inputs were ranges. Worked perfectly. Now I just cut and paste two columns into my invoice and its done. I just did a practice run of the whole process and I can do it all in 22 min. That is down from 3.5 hours using the long hand version of cut and paste and simple sums. Thanks for the help.

Report •

#6
May 10, 2016 at 12:19:38
If you post a small sample of what your sheet looks like, we might be able to
shorten the time even more.

Please use Column Letters and Row numbers, and read this HOW-TO which
explains the use of the < PRE > tags to align your data.

http://www.computing.net/howtos/sho...


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.

Are the Subtotal cells identified in any way?
Do they have an adjacent cell that says something like "CT Subtotal"?
Are you using the DATE, associated with the item, in any way?

MIKE

http://www.skeptic.com/


Report •


Ask Question