0

Hey all so I currently have 3 Gsheets. 2 are sql imports with the below tables. The goal is to get the information based off a Type Id 28353 into my Master sheet. The plan is to get all values from one of the tables that match the Type ID I have. I then want to be able to take the value in col1 and replace it with the correct name instead of the number. =query(importrange(xls_url!B1,"industryActivityMaterials!A1:D40000"),"select Col3, Col4 where Col1 matches '"&B1&"'") This first query will respond with the below query

materialTypeID  quantity
21009   9
21013   9
21017   7
21019   11
21021   11
21023   9
21025   11
21027   22
21029   6

I know that the names can be queryed with this next query =VLOOKUP(A17,importrange(xls_url!B$2,"invTypes!A2:C"),3,false) . The question is How can I combine the Two query's so that the MaterialTypeID will have the expected name returned from query 2 ?

Another question With the First query can I prevent the response of materialTypeID quantity ? These values do reside at row 1 of the first sheet?

3
  • Hi and welcome. Your description of your sheets,queries and objectives is not clear. May I try and clarify; please correct me if I am wrong. You have one spreadsheet file containing three sheets - one sheet is a "Master", one is populated from an external spreadsheet with quantity data by Type ID and the third is populated from an external spreadsheet with "invType" data by Type ID (this also includes a Type Name for each ID). For a given ID (#28353), you want to retrieve the quantity on hand from the second sheet and the Type name from the third sheet.
    – Tedinoz
    Dec 10 '21 at 10:33
  • Yes I have 3 sheets that I'm keeping as separate files. 1 files is what I would say I consider the Master File where I want the combined data to be placed. The other two contain different info but both are sql exports in xml format and imported into their own sheets. The materialTypeID sheet contains all the Data on individual items and how they are like, or another way a Table of items in game that are the Blueprint materials to make the item. Sadly materialtypeID does not contain names just numbers which is why i can get the above output but I would like to be able to get the Names.
    – Grasume
    Dec 11 '21 at 1:04
  • With the First query can I prevent the response of materialTypeID quantity ? Not sure that I understand this BUT... When you use IMPORTRANGE you specify the URL and the range. So if the data in one spreadsheet starts on row#1, then you define a range for IMPORTRANGE that starts on row#1.
    – Tedinoz
    Dec 13 '21 at 7:33
0

Try this in the "Master" spreadsheet file:

=query({query({importrange(B3,"A2:C")},"select Col1,Col3 where Col1 contains '" &B4&"'",0),query({importrange(B2,"A2:B")},"select Col2 where Col1 contains '" &B4&"'",0)},"select Col1, Col3, Col2")

  • Enter the Spreadsheet URL for "industryActivityMaterials" in cell B2
  • Enter the Spreadsheet URL for "invTypes" in cell B3
  • Enter the "materialTypeID" that you want to query in cell B4

Logic

  • the query consists of two nested queries.
  • Both of the nested queries use importrange to access data the external spreadsheets.
    • each nested query includes a where clause: Col1 contains '" &B4&"'"
      • this returns only the records that contain the "materialTypeID" entered in Cell B4. For the purposes of this answer, it is assumed that each spreadsheet contains only one record per ID.
  • the output of the nested queries are combined in the range of single query using curly brackets {} and separated by a comma (for countries that use decimal point notation).

Master

Master


Spreadsheet: industryActivityMaterials

Activity


Spreadsheet: invTypes

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.