Ordering a dynamic list from smallest to largest

[lawi227]

I have a table with two columns - both columns have cell references. These columns produce a summary table.

The first column scans a row of data and only returns a list of unique values. There are over 500 rows of data, and my formula returns a maximum of 18 unique values. Sometimes the data will return fewer unique values.

In the second column, I calculate an average based on each of the unique values (using averageif).

In theory, I would like to be able to filter this table based off of the second column (from smallest-to-largest or largest-to-smallest), but when I add filters and filter, the references all get out of wack.

Can I do this by editing my formula?
Column 1 formula (entered into B41 dragged down to B58):='LPA Categories & Data Validate'!A4
Column 2 formula (entered into B41 dragged down to B58): =IFERROR(AVERAGEIF('Selected LP List'!$C$4:$C$740,'LPA Tables'!$B41,'Selected LP List'!$I$4:$I$740),"")


My current solution is to create a third column a rank the values from 1-18. I think create a second table (called "Sorted") which just does an index match off of the first table to return the data from 1-18 in descending order. I do not like having two tables in my workbook. Can this be done in one workbook?

I'm not opposed to a macro.

 

[edwin1682006]

Hi Lawi,

Have you tried pivot table? it saves you some time to create formula to count rows of data

 

[lawi227]

I have, but they don't quite work for the functionality of the tool. I'm trying to avoid pivot tables because I'm trying to keep this tool simplified for the end-user. Do you think this can be done with a macro or another formula?

Thanks!

 

[edwin1682006]

Hi Lawi,

There is an option call "Slicer" it is simply a click for user to filter the data out

 

[L. Howard]

Trying the macro route, something like this may work for you.

Where I am talking about the "style" of the macro not the actual ranges and sheet names I used on a test sheet.

The two WITH Range(... will put the formulas in the cells in columns A and B for the rows you wish. (The formulas will fill down same as pulling)
I am using 18 rows that start at A14 and B14 and fills down to row 31, and returns the values of those formulas ONLY (not the formula on the sheet).
With values in the cells instead of formulas, sorting does not mess with the formula references, i.e. there are no formulas.
Then sort on column B, Ascending/Descending, no header.

You would need to put formulas in the code that actually work on the sheet, and note the Double Quotes are expressed as """" to return 'nothing' instead of "".

=IFERROR(AVERAGE(A14:A31,C4:C996),"""")

Perhaps the major flaw with the VALUES only is that you need the formulas to continue to function after sorting for what ever reason.

Howard

Option Explicit

Sub myColumn_Sort()
  Dim lRowCount&
 
  lRowCount = 18
 
  With Range("A14").Resize(lRowCount)
    .Formula = "='LPA Categories & Data Validate'!A4"
    .Value = .Value
  End With
  
    With Range("B14").Resize(lRowCount)
    .Formula = "=IFERROR(AVERAGE(A14:A31,C4:C996),"""")"
    .Value = .Value
  End With
  
  Range("A:B").Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlNo
End Sub

 

[lawi227]

Thanks, Harold. I'm having some trouble on the second formula that is used to calculate the average. Note: I've copied the whole code - which has been modified from my original post. You'll notice the columns are B:C and has 9 rows.

Any idea why that formula isn't acceptable?

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #ff2600; background-color: #ffffff}span.s1 {color: #000000}span.s2 {color: #011993}</style>Option Explicit


Sub myColumn_Sort()
Dim lRowCount&

lRowCount = 9

With Range("B45").Resize(lRowCount)
.Formula = "='LPA Categories & Data Validate'!A4"
.Value = .Value
End With

With Range("C45").Resize(lRowCount)
.Formula = "=IFERROR(AVERAGEIF('Selected LP List'!$D$4:$D$740,'LPA Tables (1 org)'!$B45,'Selected LP List'!$J$4:$J$740),""")"
.Value = .Value
End With

Range("B:C").Sort Key1:=Range("C45"), Order1:=xlAscending, Header:=xlNo
End Sub

Trying the macro route, something like this may work for you.

Where I am talking about the "style" of the macro not the actual ranges and sheet names I used on a test sheet.

The two WITH Range(... will put the formulas in the cells in columns A and B for the rows you wish. (The formulas will fill down same as pulling)
I am using 18 rows that start at A14 and B14 and fills down to row 31, and returns the values of those formulas ONLY (not the formula on the sheet).
With values in the cells instead of formulas, sorting does not mess with the formula references, i.e. there are no formulas.
Then sort on column B, Ascending/Descending, no header.

You would need to put formulas in the code that actually work on the sheet, and note the Double Quotes are expressed as """" to return 'nothing' instead of "".

=IFERROR(AVERAGE(A14:A31,C4:C996),"""")

Perhaps the major flaw with the VALUES only is that you need the formulas to continue to function after sorting for what ever reason.

Howard

Option Explicit

Sub myColumn_Sort()
  Dim lRowCount&
 
  lRowCount = 18
 
  With Range("A14").Resize(lRowCount)
    .Formula = "='LPA Categories & Data Validate'!A4"
    .Value = .Value
  End With
  
    With Range("B14").Resize(lRowCount)
    .Formula = "=IFERROR(AVERAGE(A14:A31,C4:C996),"""")"
    .Value = .Value
  End With
  
  Range("A:B").Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlNo
End Sub

 

[L. Howard]

Probably a little matter of " is missing


Bad formula...

"=IFERROR(AVERAGEIF('Selected LP List'!$D$4:$D$740,'LPA Tables (1 org)'!$B45,'Selected LP List'!$J$4:$J$740),""")"

Good formula...?

"=IFERROR(AVERAGEIF('Selected LP List'!$D$4:$D$740,'LPA Tables (1 org)'!$B45,'Selected LP List'!$J$4:$J$740),"""")"

Howard