Subtotaling and making 1 record

[mws5872]

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.

 

[Oaktree]

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]

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]

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]

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]

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]

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.