How to query Column A base on Column C value


#1

Hi
Anybody knows how to query column A value base on Column C value? I know how to filter but I prefer query.


#2

Hi @desmondlee

How about something link:
SELECT A WHERE C="value"


#3

C cannot be a single value. C is a range of value comparing to another range of value (also at column C) at the raw data to retrieve the value. only those both C(s) will be extract including duolicate


#4

@desmondlee how about the following approach:

  1. Check if both source columns match and have the same value
  2. Use a query to only get those rows where they match (see 1)

Check below example. Important cells to look at are E1 and G1.


#5

Thanks bobby. I have 67,000 data to put TRUE n FALSE ! BTW my CIO in HQ still did not get back to me whether or not to allow me to subscribe ONE ADVANCE plan. I hope she can approve that.


#6

How about using =ARRAYFORMULA?

Great!


#7

No I don’t think I want to put in a 67000 row and its still slow. I tried and I saw it very slow. What is the fastest formula to pick the earliest date in tab WO Start Rev 03-1 column A reference to WO with criteria on column C:C

=ARRAYFORMULA(QUERY({‘WO Start Rev 03 -1’!A:J,N(ISNUMBER(MATCH(‘WO Start Rev 03 -1’!C:C,C:C,0)))}, “SELECT Col1,Col3,Col9,Col6 WHERE Col11=1”))


#8

How about you sort by earliest date in this column and then return the first row from the top?


#9

No practical to do that because database is shared by the whole plant and everyone view only except me. That is why everything I do I avoid go right to the database.


#10

Hi Bobby
Arrayformula is good and fast in large data


#11

Glad to hear! Always prefer it over =query or =filter, if possible.


split this topic #12

A post was split to a new topic: Question about corporate subscription


#13

I already have a better idea and I will share it later. There is no need to put helper in the source 67000 data