Hi,
how to sort text data without helper column? Results are in U7:U16 like these: S-11, S-9, S-14, S-7.... How to sort them?
Here is the formula:
{=IFERROR(INDEX(List1!$E$2:$E$2000;SMALL(IF((List1!B$2:B$2000>=$Q$12)*(List1!J$2:J$2000>500)*(List1!E$2:E$2000<>"")*(List1!B$2:B$2000<>"")*(List1!J$2:J$2000<>"")*(COUNTIF($U$6:U6;List1!E$2:E$2000)=0);ROW(List1!E$2:E$2000)-ROW(List1!E$2)+1;"");1));"")}
Last edited by B.W.B.; 08-26-2024 at 04:05 AM.
Where's the sample workbook?
We shouldn't be having to ask for one ...
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks for the woorkbook.
Text strings like S-10 and S-6 are not numbers, do son't sort numerically. Instead, they sort alphabetically, and as such, S-6 is after S-10 and S-11. You can mitigate this by changing the serialisation thus:
S-06
S-10
S-11
Because 0 is before 1 in alphanumeric terms, 06 will sort above S-10.
Is there always S- ?
Here are few results for your question assuming there is S- at the start.
If that's not case I would suggest reorganize data or use helper column because it's very complicated for older version of excel.
Your solution is in X column
zbor zbor zbor zbor zbor zbor zbor zbor
Spremnik 23 i S-2, they don+t have value in "J" column. I could/must use a helper column with "random" S-14, S-9, S-10... values, then we could use AGGREGATE(Countif formula in U7:U16, right??
Wasn't focus on J column. I've just add few more S's for purpose of test
So here is the formula for "helper" column:w/o ctrl+sh+ent=IFERROR(INDEX(List1!$E$2:$E$20;AGGREGATE(15;6;(ROW(List1!$E$2:$E$20)-ROW(List1!$E$2)+1)/((List1!$B$2:$B$20>=$Q$12)*(List1!$J$2:$J$20>500)*(List1!$E$2:$E$20<>"")*(List1!$B$2:$B$20<>"")*(List1!$J$2:$J$20<>"")*(COUNTIF($U$6:$U$6;List1!$E$2:$E$20)=0));ROW(A1)));"")
So now i need to sort these helper column results into U7:U16, thank you all, GOD BLESS
There is already sorted in X column without helper column.
What I understand next is to create SUMIF formula, right?
Nothing to Sum, just some text results from E colOriginally Posted by zbor
thanks, hvala puno!
Try this:
=IFERROR(INDEX(List1!$E$2:$E$20,MOD(SMALL(IF((List1!B$2:B$20>=$Q$12)*(List1!J$2:J$20>500)*(COUNTIF($U$6:U6,List1!E$2:E$20)=0), ROW(List1!E$2:E$20)/1000000+REPLACE(List1!$E$2:$E$20,1,2,"")),1),1)*1000000-ROW(List1!E$2)+1),"")
This looks like "slow on calculation", my poor CPU.Try this:
=IFERROR(INDEX(List1!$E$2:$E$20,MOD(SMALL(IF((List1!B$2:B$20>=$Q$12)*(List1!J$2:J$20>500)*(COUNTIF($U$6:U6,List1!E$2:E$20)=0), ROW(List1!E$2:E$20)/1000000+REPLACE(List1!$E$2:$E$20,1,2,"")),1),1)*1000000-ROW(List1!E$2)+1),"")
This will sort results from U7:U16 :Thank you ALL, Thanks forum, Thank you zbor AliGW{=IFERROR("S-"&SMALL(IFERROR(--SUBSTITUTE(List1!$E$2:$E$20;"S-";"");"");ROW(A1));"")}
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks