Hey guys
I need formula which would work like "small" but it would work not with numbers but with text.
I need to get 1st, 2nd, 3rd.... result for text and conditions. I dont think that uploading file would be necessary in this case.
Do you know which formula works similar to SMALL but to text?
Don't be lazy - please upload a sample file.
You will get a much faster resolution that way. Without the workbook, we are just guessing at your data layout and outcomes, etc.
The only thing I'd suggest based on the scant information so far is using a helper column with a number in it.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
file uploaded
Oh, come on! Please try a little bit harder. Replace "It goes on" with meaningful sample data and then provide one list of sample output data manually. You are expecting us to put ourselves out for you - the least you can do is spend a little of your time providing useful data for us to work with.
Can warsaw exist on more than 1 row? Your current example shows it only on row 6.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Filtering the table with slicers would work just as well, by the way - have you considered doing this?
i am doing macro to organize files, this is only 1 step which i have to figure out and i need your help to guess how i can solve this issue
i am uploading file, maybe it would be more clear what is purpose
It can happen that 1 neighborhood exist in more than 1 class
I was thinking that INDEX can work somehow... like if it is WarsawHighclass then return #1, #2, #3 ... with values its doable but with text i didnt do it yet
Once again:
Can warsaw exist on more than 1 row? Your current example shows it only on row 6.
No it cant :P
First split the column headings in your results table, so that you have Warsaw in A9 and HIGH CLASS in B9.
Next, Array confirm this formula in C9, then drag right and down to fill the table.
=IFERROR(INDEX($D$6:$G$7,MATCH($A9,$C$6:$C$7,0),SMALL(IF($D$4:$G$4=$B9,COLUMN($D$4:$G$4)-COLUMN($D$4)+1),COLUMNS($C9:C9))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I'm out. I am not going to help someone who cannot be bothered to give the sample data I've asked to see. If you can't be bothered to answer my questions fully, then I can't be bothered to help you freely and in my own spare time. Good luck!
Thank youOriginally Posted by jason.b75
You already have an answer, but, FWIW, here is a normal (non-array) formula alternative:
=IFERROR(INDEX(INDEX($6:$7,MATCH($A9,$C$6:$C$7,0),),AGGREGATE(15,6,COLUMN($D$4:$G$4)/($D$4:$G$4=$B9),COLUMNS($C:C))),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks