Subtotaling and making 1 record

mws5872

New Member
Joined
Oct 10, 2006
Messages
11
This question may sound vague but its really important to figure this out. I am trying to set up a macro to make this process alot faster. Basically the account has more than one cost allocation centers that are the same number. When the number is different i want it to pick that number up and put it in the spreadsheet. How would one go along doing that? Thanks.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Welcome to the board.

I don't understand what you mean by:
...the account has more than one cost allocation centers that are the same number...
Can you give us an example?
 

mws5872

New Member
Joined
Oct 10, 2006
Messages
11
Ok I guess what I am trying to say is that the cost centers maybe like
132211
132211
132212
132212
132212
132213

There is more data in columns next to it. Basically what I want to do is make it so only one of each shows up. So it should look like this.
132211
132212
132213

The other information that goes along with it I don't really cares what happens to it. The list is never fixed it changes monthly as do the numbers. Any help is greatly appreciated.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
A couple of options:

1) use a pivot table
2) select your column and go to data --> filter --> advanced filter and check the "unique records only" box
 

mws5872

New Member
Joined
Oct 10, 2006
Messages
11

ADVERTISEMENT

awesome that helped alot. What about absolute data ranges I don't know if that is the right word. The amount of numbers changes per month and I want to run this in a macro so its automatic but the size changes I use control shift down to highlight all that have data in that column will that work even when it changes length? Thanks.
 

mws5872

New Member
Joined
Oct 10, 2006
Messages
11
one other thing that would complete it. I am subtotaling how would I copy all the subtotal amounts on to another sheet automatically? I know there has to be a way.. excel is so powerful. Thanks for all the help. I tried just copying it with control +C but it copied all the values that were subtotaled internally I only want the total values and clues? Thanks.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
With the changing range, try searching the board for "dynamic named range" for some ideas for how to set them up.

For your other question about subtotals, use ALT+; first to select only the visible cells, then CTRL+C to copy, etc.
 

Forum statistics

Threads
1,136,419
Messages
5,675,724
Members
419,584
Latest member
mrh8rr

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