Hi!
I have a problem with searching within my spreadsheet.
I want to find all the Organizations (column C) that do not work with Univ of Cccc (from column A), but still are in United Kingdom (column B) and work with other Univs from UK. I tried to do that with nested If and Index Match, but all I get is either value or name error or the ones working with Univ of Cccc, not the rest. Can somebody give me some help with that?
Thank you in advance!
Yes, but you will need to be clear about how you want your results presenting. PowerQuery seems the most obvious route.
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.
Oh, I am sorry. You are right. I need the list of the organizations that does not work with Univ of Cccc. Preferably without repeating, just names.
I will check the PowerQuery, I haven't thought about that. Thank you!
If you can update your workbook with a few rows of results data to show what you want, that would help.
I need to create the list like this:
Org -Univ of Cccc
AoF
AoMS
Alzheimer's Society
Arcadia Fund
AHRC
I know I can do it by filtering manually and selecting what I need, but the original spreadsheet has over 5k rows and it would take too much time. Also, my boss wants the same formula to use in other but similar tables with choosing from even bigger workbooks.
I tried to use the Power Query, but it seems to have even more steps than the filtering, or I don't know how to use it properly.
Thank you for the suggestion though :-)
If you are able to make a list of "the ones working with Univ of Cccc" then we may be able to use that to extract the ones that aren't. Could you post an example?Originally Posted by akozka
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi,
Thank you for your help.
I think this list is a problem. I have rows with those who works with Univ of Cccc, but I have a rows with those who works, but it shows inn different row. I am not good in explanations, so I will try another way. I have
Univ of Cccc, Univ of Aaaa, Univ of Bbbb that works with Inst ZZZZ
And I have next row, where Univ of Bbbb and Univ of Dddd work with Inst YYYY
From this I can see that Bbbb is working with Z and Y, D is working only with Y and Cccc is working only with Z. And it is simple so far.
But then I have
Univ of Cccc, Univ of Eeee, Univ of FFFF working with Inst PPPP
Univ of Aaaa working with Inst PPPP
And I do not know how subtract the information that C is working with P, even though in the next line it does not. That is my main problem here I think.
My co-worker has an idea to use =(IF(ISERROR(VLOOKUP(T6,'Sheet1'!$A$2:$A$14400,1,FALSE)),T6,"")), where T6 is a name of the funder, but for that I need a Sheet1 with the names of all of the funders that Univ of Cccc is working. And this is I think the problem.
Thank you for trying to help me :-)
This proposal employs four helper columns (D:G) which may be moved and/or hidden for aesthetic purposes.
Column D is populated using: =IF(ISNUMBER(SEARCH("Univ of Cccc",A2)),C2,"")
Column E is populated using*: =IFERROR(INDEX(D$2:D$60, MATCH(0, IF(D$2:D$60="", 1, COUNTIF(E1:$E$1,D$2:D$60)), 0)),"")
Column F is populated using*: =IFERROR(INDEX(C$2:C$60, MATCH(0, IF(C$2:C$60="", 1, COUNTIF(F1:$F$1,C$2:C$60)), 0)),"")
Column G is populated using: =ISERROR(MATCH(F2,E$2:E$60,0))
The Org - Univ of Cccc is populated using: =IFERROR(INDEX(F$2:F$60,AGGREGATE(15,6,(ROW(A$2:A$60)-ROW(A$1))/(G$2:G$60=TRUE),ROWS(A$1:A1))),"")
*Denotes an array entered formula which is 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.
Let us know if you have any questions.
Hi,
thank you for trying, but this is adding extra 5 columns, which is a big "no" for my colleague. I have managed to do that with 5 columns and tiny bit of work, but he wants one line or a short macro for it.
Thank you everybody! I think I will just tell him that I cannot find a way to do it in a shorter way.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks