Friday, June 8, 2012

Yogi_Query A Range With Query Criteria Also Being A Range

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #584    Jun 08, 2012     www.energyefficientbuild.com.


user VP4711 said:
Query a range with query criteria is also a range
My spreadsheet looks like this
         A               B                 C           D
1     Car1        BMW             Car1      =query(?????)
2     Car2        Mercedes       Car3
3     Car3        Porsche         Car7
4     Car4        Hyundai
5     Car5        Honda
6     Car6        Ford
7     Car7        Ferrari
 I do not want to use vlookup in each column D1, D2, D3. I want to use Query function to show the list in D1:D3 basis criteria in C1:C3
Formula might be
Query (A1:C7; "Select B where A = C1:C3")
 and desired result is
        A         B               C          D
1     Car1        BMW             Car1       BMW
2     Car2        Mercedes        Car3       Porsche
3     Car3        Porsche         Car7       Ferrari
4     Car4        Hyundai
5     Car5        Honda
6     Car6        Ford
7     Car7        Ferrari
 Please tell me what am I doing wrong?
-----------------------------------------------------------------------------------------
you can use contain in QUERY Select as illustrated in
following solution to the problem


3 comments:

  1. Hi Yogi,

    I've always been a fan of your blog. Most of the work that I've done is because of some of your insights.

    Anyway, I would like to ask a favor if you have time to help me out on this one.

    I'm trying to come up with a project management report. My challenge is how to filter the tasks that were 100% complete in reference to the date today. I also need to get the task to be done in the next 7 days under 'work planned next week'

    I've tried doing this query in the 1st tab under work completed last week.
    =(QUERY (Progress Report Chart!A9:E16; "SELECT A WHERE E='100%' AND C < datetime '"&text(now()-"00:00","yyyy-mm-dd HH:mm:ss")&"' ")

    Looks like the formula needs a lot of work. Help please?

    I've published a dummy report here: (https://docs.google.com/spreadsheet/pub?key=0AqEpwVHEU0S1dHNhN1piS2twbHc4dkNsb3dqNlphZ0E&gid=7)

    ReplyDelete
  2. Hi Nacho:

    I suggest you share your Google spreadsheet for normal view with some sample but realistic data,
    let us take one formula at a time, so
    a) tell me what formula you need help with
    b) in which cell?
    of which sheet?
    c) show me your expected result
    along with needed logic/explanation as to why that is the correct result

    and then let us take it from there.

    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  3. Hi Yogi,

    You're totally awesome. Thanks for the prompt reply. I didn't imagine it would be this fast. I'll definitely bring in more links to your site so you would rank further in search engines.

    I've shared with you the worksheet. The end goal of this project is to generate a weekly report. In the first sheet, it will show what was 100% complete and what should we be working on the next week. I want something semi-automated. When I update Column E on the Progress report chart, it will update the Gantt chart and the summary in the 1st sheet.

    To clarify:
    I need help on the formula on C9 and G9 on campaign progress summary. (I've updated the formula in there which looks stupid lol) What's supposed to be there are queries pulling out the list of tasks that are 100% done based from the 'progress report chart' (column A).

    For c9
    In the dummy data, since only items 1.3 and 1.4 are 100% complete but the end date of 1.3 is 3/10/13 and 1.4 is 3/15/13, only item 1.4 should show up in the campaign progress sheet in c9. So I would like two filters: the end date and % of completion.

    For G9
    For this one, I only have 1 filter: the date. Any task that should be done 7 days today should be in there. With the current data, only item 1.6 should show up.

    I know I have trouble expressing myself sometimes but I do hope this conveyed what I want to accomplish.

    Thanks so much Yogi!

    ReplyDelete