Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that will pull items from a certain column, depending on two other criteria related to two other columns, and print out every instance that matches all of the above once and only once, and stop printing when all instances are printed. Currently have been relying on this for half of the job (printing one, and only one, instance of every unique element), but have not been able to incorporate the other two criteria: =ArrayFormula(index(RawData!$E$2:$E$500000, match(0, COUNTIFS($C$1:$C12,RawData!$E$2:$E$5000),0)))
Solved by I. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/06/2018 - 04:38
Hello and welcome to Got it! :)
User
07/06/2018 - 04:40
Hi
User
07/06/2018 - 04:40
how does this work
Excelchat Expert
07/06/2018 - 04:40
Okay, I've seen your query, and I believe you need to modify your existing formula to consider two other criteria. Is this correct? :)
User
07/06/2018 - 04:41
yes, that is correct, unless there is another formula
Excelchat Expert
07/06/2018 - 04:42
The formula I was thinking of was actually similar to your formula, but with a few modifications. Will it be okay if we look at the file you're working on to get a better picture on this? Thanks! :)
User
07/06/2018 - 04:44
Sure, getting it online now
Excelchat Expert
07/06/2018 - 04:45
Okay, noted on that! :)
User
07/06/2018 - 04:47
https://docs.google.com/spreadsheets/d/11B0ZbCyAoX5WfMjoffy_MWPUqSn7hXgCXyYFhq5R-d8/edit?usp=sharing
Excelchat Expert
07/06/2018 - 04:48
Okay, already opened the file! What might be the criteria we're looking at here? :)
User
07/06/2018 - 04:50
commented on the file
User
07/06/2018 - 04:50
https://docs.google.com/spreadsheets/d/11B0ZbCyAoX5WfMjoffy_MWPUqSn7hXgCXyYFhq5R-d8/edit?disco=AAAAB_Fv7jM
Excelchat Expert
07/06/2018 - 04:51
Okay I see. I saw your comment, and you wish to match the city and date, then return unique values. Okay, let me have a look and will come up with a formula for this. :)
User
07/06/2018 - 04:52
ok, thank you so much for your help in advance
Excelchat Expert
07/06/2018 - 04:52
Btw, I don't see any data in RawData column E. Can we put some dummy data in there so we could know if we did it right? Thanks! :)
User
07/06/2018 - 04:59
updated the formula
User
07/06/2018 - 04:59
sorry, got pulled away because my complementary time window was closed
Excelchat Expert
07/06/2018 - 04:59
Oh okay, noted on that. :)
User
07/06/2018 - 05:00
how much tim edo you think that it will take for you to finish this out of curiousity
Excelchat Expert
07/06/2018 - 05:01
If all goes well, that 17 minutes will be more than enough. :)
Excelchat Expert
07/06/2018 - 05:01
In worst case scenario, we may need to extend until the last free session. :)
User
07/06/2018 - 05:02
ok, fantastic
Excelchat Expert
07/06/2018 - 05:11
LOL
Excelchat Expert
07/06/2018 - 05:12
I've tried pretty much every combination of INDEX MATCH IF statements, and it still won't return a value. Just noticed we don't have any value in Raw Data matching the criteria, and that's why. :D
User
07/06/2018 - 05:12
lmfao
Excelchat Expert
07/06/2018 - 05:13
I was nearly draining my sanity as to why it's not working. Turns out, we should need to change some data so we could test this one. :D
User
07/06/2018 - 05:14
i literally spent three hours on this today trying to figure it out, so if you can figure it out in the next ten minutes, I would be over the moon, not to mention will be using this service for a long time to come
Excelchat Expert
07/06/2018 - 05:14
Okay, that's nice! :)
User
07/06/2018 - 05:15
not sure if either of these will help, or if you already know this stuff, but I was looking at the arrays produced and trying to figure out how to combine queries
User
07/06/2018 - 05:15
https://exceljet.net/formula/extract-unique-items-from-a-list
User
07/06/2018 - 05:15
https://exceljet.net/formula/index-and-match-with-multiple-criteria
Excelchat Expert
07/06/2018 - 05:21
Finally, already done. :)
Excelchat Expert
07/06/2018 - 05:22
I just had to retrace the combinations I made since the data we're looking at back then doesn't have any that matches both our criteria. :)
Excelchat Expert
07/06/2018 - 05:23
Btw, i haven't seen the references you posted. Upon looking at them, they won't actually work in our case since they're only good for returning a single value.
Excelchat Expert
07/06/2018 - 05:23
We need to incorporate a COUNTIF function in our formula so that we could return unique data from our reference based on multiple criteria. :)
User
07/06/2018 - 05:24
this looks amazing
User
07/06/2018 - 05:24
give me a minute to go through this
Excelchat Expert
07/06/2018 - 05:24
I hope you wouldn't go over the moon btw, so that you could always return to our service should you need powerful Excel solutions. :)
Excelchat Expert
07/06/2018 - 05:24
Sure thing! :)
Excelchat Expert
07/06/2018 - 05:31
Any update btw? :)
User
07/06/2018 - 05:32
trying to get it to work in my real file (more confidential data, a lot more going on)
User
07/06/2018 - 05:33
how would I do a less than instead of if A1 is equal
Excelchat Expert
07/06/2018 - 05:34
You mean A2 (date) right? You can just change the = sign to <, so that it would be A2<....
User
07/06/2018 - 05:35
a2, hes
User
07/06/2018 - 05:35
but wouldn't I have to do ">"& or something
Excelchat Expert
07/06/2018 - 05:36
Not necessarily, since that only applies for fixed values, and not references. If you look at B2, I changed A2 = to A2 <, and it works fine. :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
