I have some problems, I am trying to resolve this now since a while but I am always ad a dead end.
I have multiply taps in my DATA Sheet, each tap has 5000+ rows (in this example now way less), some rows have the same content and I want to find specific rows in my ArtikleFinder Sheet, while entering a specific number and the matching numbers copy those rows which are matching to a different sheet B and keep the formatting etc from sheet A.
Example: I need to make a shipping/order list I have 2 sheets Sheet A is full of information (my stockload), multiply tabs with a lot of rows in each tab. in Sheet B (new URL) I want to filter the information.
Let's say, I have 5000 articles spread in 5 tabs on Sheet A, someone is ordering 20 articles, so I just want to scan the a certain number or any other criteria to Sheet B, and all rows with that data, will be imported. Now I have a brand new list with only the 20 articles, which I can share to the client.
Till now I resolved this with Importrange and query, however it is limited. I can only search for 3 criteria within 1 query, and also the data appears randomly.
I had to make 2 tabs & codes with 2 different query but same Importrange to be able to find all the matching data.
PKG-ID & BOL Finder (Column 2 & 3)
=QUERY({
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 17.02. - 20.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 21.02 - 27.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 28.02 - 06.03!A:J")
}, "WHERE Col2 = '"&TEXTJOIN("' OR Col2 = '", TRUE, A:A)&"' OR Col3 like '"&TEXTJOIN("' OR Col3 like '", TRUE, A:A)&"'", 0)
ASIN, EAN, LPN Finder (Column 5 - 6 - 7)
NOTE: LPN(Col7) needs to be CONTAINS and not LIKE, since I often have multiple LPN numbers in one cell and it cant be found otherwise
=QUERY({
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 17.02. - 20.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 21.02 - 27.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 28.02 - 06.03!A:J")
}, "WHERE Col5 = '"&TEXTJOIN("' OR Col5 = '", TRUE, A:A)&"' OR Col6 like '"&TEXTJOIN("' OR Col6 like '", TRUE, A:A)&"' OR Col7 contains '"&TEXTJOIN("' OR Col7 contains '", TRUE, A:A)&"'", 0)
What I can't figure out are the following things:
When scanning the PKG-ID (for example), the Article appears. In the next scan, the article appears as well BUT either below, above or in the middle. I want everything to appear below each other, so I don't have to search the new scan to see, what the number is all about.
I would prefer to only have one tap instead of two for the scans. But Query seems to be limited, is there a workaround?
Importrange would also work easier if It see's automatically the whole sheet with all the tabs instead for each tab I have to put a new "Importrange"
related to this solution, I also need another option, where only specific rows will be imported, this also can be solved with query, I just would like to know If I can combine this with the problems above too?,
"select Col1,Col2,Col6,Col7,Col8,Col15,Col16,Col17 where Col2 matches '"&TEXTJOIN("|", 1, A2:A)&"'", 0)
Is there maybe only a some numbers I am missing with Importrange/Query?
Thank you