It looks like so easy,, but when i'm trying to solve in using single formula,, I'm having trouble solving it
#Solved
Special thankz to Phan Quang 🙏
#NeedHelp
It looks like so easy,, but when i'm trying to solve in using single formula,, I'm having trouble solving it
Replies
What are you trying to do exactly?
I want to rearrange (z-a) data from the left table to the right one ased on the score on right column of table
Try Vlookup(large($B$2:$B$8;Row(A1));Choose({1;2};$B$2:$B$8;$A$2:$A$8);Column(A1);0)
In 365 it's too easy as long as you know the function SORT
Im using excel 2007
I got a feeling the solution for 2007 is going to be some kind of monster 🤣
On the phone now, will come back soon. Try a new range with: B:B+CODE(:A)*10^-3; then using Aggregate(15,6,new range,row(1:1))
Phan Quang there is no aggregate function in my excel,, im using excel 2007😥
Sorry, code(A:A) and aggregate(14,6...)
Wa Godeg use large(new range,row(1:1)) instead, require CSE.
Phan Quang can you give me the coplete syntax(formula) with cell reference,, i didn't get what you mean large(new range There is no thing in new range
New range is blank range,, where i want to fill the data in
Use function SORT column 1 and 2 its much easy.. no need complicate 🤔. Best of luck!
Nicolae Dogot This is a legacy excel version without sort =)
And a nightmare!
Yess there is no sort function in my excel 2007
Ahhh thanks Juninawan. Your solution helped me understand and it's a great solution.
I don't think so
I stand corrected. doesn't work!
Wa Godeg change column(a1) to Roundup(mod(column(a1);2)+1;0)
Wa Godeg or Change column(A1) to =MOD(COLUMN(A1);2)+1
Become Try Vlookup(large($B$2:$B$8;Row(A1));Choose({1;2};$B$2:$B$8;$A$2:$A$8);Mod(Column(A1);2)+1;0)
=Index($A$2:$B$8;Match(large($B$2:$B$8;row(A1));$B$2:$B$8;0);Column(A1))
I think it will give the same result,, match will get the first match value
Get ready to laugh friends...
=IF(COLUMN(B$1)=1,CHAR(SMALL(CODE($A$2:$A$8),ROW(B2)))&CHAR(SMALL(CODE($A$2:$A$8),ROW(B2)))&CHAR(SMALL(CODE($A$2:$A$8),ROW(B2))),LARGE($B$2:$B$8,ROW(B2)))
Why you use char function??
AAA QQQ ZZZ ECT. in fact i mean it as a name of persons
Ahhh ok =p back tot he drawing board!
Exactly i want to rearrange the data to new range in sort (z-a) basen on score (the highest the first)
Sort z-a not based on alfhabetic name
its horrible but it seems to work, it relies on the score column being calculated first
