# Add data to certain columns if conditions of separate 1 or more conditions for data in each row are met

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:

Output:

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`

• 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 Commented 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/… Commented 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" Commented 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. Commented 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. Commented Nov 17, 2022 at 8:10