0

I have a table with "manufacturing quotes" and each quote has 1 or more parts in it (please see screenshot below). The data in parts1-3 (columns E-J) are filled, however, in reality, the quote includes only if that is specified in columns B-D.

I need to create a table with all the separate parts (which quotes they belong to doesn't matter) - each part is in its row - and append their info in respective columns.

I somewhat understand FILTER and QUERY functions but I can't find a solution to how to combine them with correct parameters in one formula to give the required query.

Input:

Input

Output:

Output

Editable sheet: https://docs.google.com/spreadsheets/d/1_2fRxrQqyZepQTkBVcDXrU2J_gEayY4X6KuszgE0uAg/edit?usp=sharing

Improve this question

1 Answer 1

Reset to default
0

Try this formula:

=query(
index({
if(B3:B9="",,{A3:A9,E3:F9});
if(C3:C9="",,{A3:A9,G3:H9});
if(D3:D9="",,{A3:A9,I3:J9})
}),
"order by Col1 desc,Col3 asc")

you can see it at work in the copy of sheet1

Improve this answer
6
  • You were right, obviously. I mis-read some information. I've tried the other day to find documentation about this use of INDEX, let me know if you have some
    – Martín
    Nov 15, 2022 at 20:01
  • Thanks for your input, Daniele! It works well in the sheet where you posted it, however, I copied the formula (and adjusted the parameters) in my working sheet and the table expands outside the columns I want the result to be in. Please take a look here in this editable sheet and I would appreciate taking the time to review it: docs.google.com/spreadsheets/d/…
    – Martins Kruklis
    Nov 16, 2022 at 16:15
  • Found your mistake... you used the comma where the semicolon should have been used instead... then I also simplified your formula... see tab "solved"
    – Daniele
    Nov 16, 2022 at 18:50
  • Daniele, thank you so much for the help, obviously, you are a kind pro and that's what we need here! Such a small mistake, I always thought that the use of "comma" or "semicolon" depends on the "version" or "locale" of Google Sheets (I think I've seen identical formulae with either of one), so if you could explain that here, I think it's the cherry on top and other people can learn from that as well.
    – Martins Kruklis
    Nov 17, 2022 at 7:22
  • In a US locale, only commas or semicolons can be used between curly brachets; the former arrange the values ​​in two (or more) adjacent cells of the same row, the latter arrange them on the same column in two (or more) adjacent rows.
    – Daniele
    Nov 17, 2022 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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