sort data in 2 columns

[jarhead58]

Hello everyone!! My question is this, how can I sort largest to smallest column B with a formula that have references that I don't want changed and column A to follow the results of column B? I'll try to show example below. TIA

A B
1 =COUNTIF(G2:G2189,1)
2 =COUNTIF(G2:G2189,2)
etc. etc

 

[KolGuyXcel]

Selecting only column B, excluding column A for sorting should work.

 

[jarhead58]

Selecting only column B, excluding column A for sorting should work.

Tried that, kept changing my references; =COUNTIF(G3:G2185,1), =COUNTIF(G4:G21xx,2), etc

 

[mikeross]

If you value your data it should work - is that an option?

 

[Joe4]

Did you try locking down your range references in your formulas, so they are absolute, i.e.

[COLOR=#333333]=COUNTIF([B]$[/B]G[B]$[/B]3:[B]$[/B]G[B]$[/B]2185,1)[/COLOR]

Alternatively, you could use Named Ranges. I really like using those for any formula with a fixed range, where you are going to be copying the formula down a bunch of rows. See: https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/

 

[jarhead58]

Mikeross,

I copied the data to another column as value. Sorted the values that I copied largest to smallest. Not sure how the formulas can be aligned with the results in the original column.

 

[jarhead58]

Joe4,

I will checkout that option after I return!! I let you know how that goes!!

 

[Joe4]

I copied the data to another column as value. Sorted the values that I copied largest to smallest. Not sure how the formulas can be aligned with the results in the original column.

I am finding it hard to envision what you are describing. If you are able to post a few before/after images, it may go a long way in showing us what you are trying to do.

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[jarhead58]

This worked out perfect!! Thanks for yours and everyone else's help with this!!

Did you try locking down your range references in your formulas, so they are absolute, i.e.

[COLOR=#333333]=COUNTIF([B]$[/B]G[B]$[/B]3:[B]$[/B]G[B]$[/B]2185,1)[/COLOR]

Alternatively, you could use Named Ranges. I really like using those for any formula with a fixed range, where you are going to be copying the formula down a bunch of rows. See: https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/

 

[Joe4]

Excellent!

You are welcome.