Sort a column by Max number value

EimearC

Hi there, I'm hoping someone could help me, I am trying to sort a column by its highest numerical value but sorting ascending is not outputting as I expected, hopefully someone may be able to direct me to the best way to do this:

I am trying to sort by Total FY Spend, yet when I sort ascending:

It shows the top value, then rearranges all the other values right down at the bottom of the list.

I attempted to try adding a Max formula in the "Max" column to see if that might do it but the correct values aren't outputting there either, it's not picking up the correct values for some reason.

I thought this would be fairly simple but I'm stumped here, would someone be able to assist me please?

Many thanks,

Eimear

MarceHolzhauzen

Hi @EimearC

It seems some of your numbers are being seen as text.

Try adding =VALUE( to the front of your formula in Total FY Spend (remember te bracket at the end)

What this would do is ensure that the number is a value and not text. It should then solve the sort issue.

Let me know if this helps

EimearC

Hi @MarceHolzhauzen

Thank you so much for replying to me. I wrapped a value around it but unfortunately it is still not sorting as expected. Also each time I click on Sort Rows ascending, it sorts it a different way each time which is bizarre.

After sort 1:

After Sort 2:

If anyone can assist please?

MarceHolzhauzen

@EimearC
What does your sort criteria look like? and is there a filter applied before you sort?

EimearC

Hi @MarceHolzhauzen ,

Apologies for the delay in getting back to you.

There is no filter applied on the sheet. Please see below the formulas used in the sheet. It is bring back distinct names from two other sheets along with values of orders for those people. Then consolidating those names into a final column "Technician Name" & a final sum of their order values from 2 other sheets in column "Total FY Spend".

Consolidated Technician Name		=IFERROR(INDEX(DISTINCT({Uniform Tracker Range 1}), [Primary Column]@row), "")
Consolidated Invoicing Order Values		=SUMIFS({Uniform Tracker Range 2}, {Uniform Tracker Range 3}, =[Financial Year]@row, {Uniform Tracker Range 1}, =[Technician Name]@row)
Manual Technician Name		=IFERROR(INDEX(DISTINCT({Manual Uniform Order Entries Range 3}), [Primary Column]@row), "")
Manual Orders Value		=SUMIFS({Manual Uniform Order Entries Range 1}, {Manual Uniform Order Entries Range 2}, =[Financial Year]@row, {Manual Uniform Order Entries Range 3}, =[Technician Name]@row)
Technician Name		=IFERROR(INDEX(DISTINCT([Consolidated Technician Name]:[Manual Technician Name]), [Primary Column]@row), "")
Total FY Spend		=VALUE([Consolidated Invoicing Order Values]@row + [Manual Orders Value]@row)

MarceHolzhauzen

Good morning @EimearC

Your formulas seem to look and work correctly.

It also does not look like you have a filter applied

Also assuming that your sort screen only has one criteria (like below)

Last thing before we get some more brains in here to help, make a copy of this sheet (save as new) and replicate your steps, does the duplicate sheet behave the same way?

EimearC

Hi @MarceHolzhauzen

I appreciate you getting back to me.

Yes exactly, I only have one sort order as below.

Confirming I made a copy of the sheet & the copy is behaving exactly the same, sorting by Total FY Year is not sorting by the max value.

I'm wondering if it has anything to do with referencing the "Primary Row" column in a couple of the formulas?

Should I change this into a sheet report I wonder instead of using formulas?

Any & all advice appreciated :)

Thank you,

Eimear

MarceHolzhauzen

Hi @EimearC

Hmm…. I cannto see a clear issue in what you're describing. The only other think i can think of is that there could be hierarchies set up in your sheet. If so, this is an expected behaviour. If you’ve created sub-items, the sub-items will be sorted but kept beneath their parent row. The parent rows will also be sorted but their sub-items will move with them.

Here is the help article on sorting Sort Rows to Organize Your Data

Your next step would be to generate a report and see if the report also has an issue in sorting.

All I can think of is that the sheet is not seeing the intended column as numbers. If nothing works out, you can log a ticket with the support team here:

EimearC

Thanks @MarceHolzhauzen

No, there are no hierarchies set up in the sheet.

No worries, I'll give up on this formulas approach & attempt to build a sheet report to see if that will work.

I appreciate you taking the time to look at it :)

EimearC

I hope this is ok to do but would any other Smartsheet Guru's have any ideas on the above please? @Genevieve P. @Andrée Starå

I attempted to build this using a sheet report but then realised that I can't use formula in those so that won't work.

Paul Newcome

You can still put the formulas on the sheet but then reference the sheet with the report to get the sorting.

EimearC

Hi @Paul Newcome , Thank you so much for replying!

Can I ask, how do you reference a sheet report within a sheet formula? When selecting the options to reference other sheets in a formula, my reports do not appear, only sheets appear.

Many thanks,

Eimear

Genevieve P.

Hi @EimearC

I believe Paul was referencing using the sheet as the source for the report - keeping the formulas that you've built in the sheet and then using that sheet in the Report and applying your Sort there.

I have to admit I'm not sure why your formula column isn't sorting properly. Adding the VALUE function is exactly what I would have done! Let us know if the Report sorts that column in the same way, I'd be interested to see if the sheet is somehow looking at the formula text underneath instead of the output.

Cheers,
Genevieve

Paul Newcome

@EimearC Yes. Exactly what @Genevieve P. said. I was referring to leaving the formulas on the sheet and then using the sheet as a source sheet in a report and seeing if the report sorts properly.

EimearC

@Genevieve P. & @Paul Newcome

Ah ok :) Thank you both very much for clarifying :)

I gave that a go but again, it is not sorting correctly but also it doesn't do what I need it to do.

Maybe you may have a better suggestion for what I am trying to achieve if I can explain it?

What I am attempting to achieve is to have a column with unique names coming from two different source sheets & sum their order values to one column, so if the same person e.g. me has placed an order on 1) Consolidated Sheet or 2) Manual Sheet it will only bring in one Name value "Eimear Critchley" & then sum any orders from both sheets for that person so the final output will be a sum of all orders placed for that person from both sheets. Then the final need is to be able to sort that column from high to low.

Would either of you have a better suggestion to build this please?

Thank you very much for your time in looking at it.

Kind regards,

Eimear

Genevieve P.

Hi @EimearC

Thank you for this screen capture! The Report confirms that the value in your Total FY Spend column is not being recognized properly as numerical. I suppose you could try using the Report Summarize SUM function on that column to confirm this - do you see the correct total when you SUM the entire column?

In any case, I have one last thing to try:

Instead of wrapping the whole formula in a VALUE function, can you wrap each individual cell reference in one?

=VALUE([Consolidated Invoicing Order Values]@row) + VALUE([Manual Orders Value]@row)

If this doesn't work, I have to admit I'm out of ideas. There's something here that's blocking that final column from being recognized as numbers and it may take more detailed and individual troubleshooting to sort this out so we may need to submit a Support ticket.

Let us know what these last tests show!

Cheers,
Genevieve