Sorting Blanks from an XLOOKUP

[Marhier]

Good morning, I hope you're all well!
I'm using XLOOKUP for the first time and having an issue where when I sort the data in this column by A to Z, it's putting all the blanks first, and the data I want at the bottom of the table.
This has never happened in the past (where I've used VLOOKUP for instance), and doesn't happen with any other columns with formulas in.

I've tried the following:

=IF(ISBLANK([@CATEGORY]),"",XLOOKUP([@CATEGORY],Budgets[Category],Budgets[Material/Plant]))

=LET(X,XLOOKUP([@CATEGORY],Budgets[Category],Budgets[Material/Plant],""),IF(X="","",X))

and:

=IF(XLOOKUP([@CATEGORY],Budgets[Category],Budgets[Material/Plant],"")="","",XLOOKUP([@CATEGORY],Budgets[Category],Budgets[Material/Plant],""))

Everything I try, this particular column sorts blanks, rather than visible data.
I'm stumped.

If anyone can help, it would be greatly appreciated.

Thank you.
Regards
Marhier

 

[Fluff]

That's exactly what I would expect as a null string "" is a zero length string & therefore comes before the letter A alphabetically.

 

[Marhier]

How comes this doesn't happen with similar fucntions like VLOOKUP, or any other cell where I have formula preceeded by an IF(ISBLANK statement?

 

[Fluff]

Are those other formulae looking at text values or numbers?

 

[Marhier]

Ok, I see what you mean now.
The others are looking at numbers; this issue only occurs when the formula is looking at text.

Is there a way round this?

I thought about VBA to clear blanks in that range:

Set Rng = Range("F8", Cells(Rows.Count, 1).End(xlUp))
For Each cell In Rng
    If cell.Value = "" Then
    cell.ClearContents
    End If

My issue with that is if I need to generate my table again becuase the data set grows, I'll need that formula in thoese cells so it picks up what I need.

Any advice is greatly appreciated.
Thank you
Regards
Marhier

 

[Marhier]

Can easily VBA that formula back in, I guess.

Would be nice if it would just not sort the blanks, lol!

 

[Fluff]

The problem with using a macro to remove the formula, is that you then have to figure which cells need the formula replying at a latter date. Especially if the data has been re-sorted on a different column.

 

[Marhier]

My 'Generate' button clears said table first and starts a fresh, so adding formula in at the beginning and clearing it at the end via VBA will work just fine for my use case.

I'm writing the code now, will test and report back.

 

[Fluff]

Ok that's fine.

 

[Marhier]

I can confirm it has worked.