CODEWITHSUNDEEP
google-sheetsuniquegoogle-formsinventorygoogle-sheets-query
Rudy Acevedo
Rudy Acevedo

Reputation: 13

Query only most recent value if duplicates exist

I work at a warehouse and I am developing an Inventory System. When products come in, it is because they are damaged. I have one person fill out a row in Google Sheets when the product arrives (Sheet1 in the workbook link below). I mainly need Barcode, Issue, and Date from this Sheet).

Next, a mechanic fixes the product and fills out a google form with his name and the barcode of the product; the responses are Form Responses 1 in the workbook link below.

I then pull the data for all the incoming products from Sheet1 to a new tab (Barcodes In/Out in the workbook link below), querying Barcode, Issue, and Date. I pull the data for all the outgoing product in another column in this tab, querying the Barcode from the Form Responses 1. Column E (Backlog) has a formula that only shows barcodes of products that have not been repaired (gone "out").

I pull the data for what is in my Back log (products still in warehouse needing repair) into another tab named Back Log. This was working perfectly until I realized products can come back to our warehouse (i.e. "123" can come in and get fixed and sent out again. "123" can come back again for more repairs.)

This poses a problem because Back Log now shows "123" twice with two different Issues.

I need Back Log to query "123" only and only with the most recent Issue.

This is a lot of info, but I tried to be as detailed as I could. Thank you for your help in advance!

Here is the link to my workbook

Upvotes: 1

Views: 81

Answers (1)

JPV
JPV

Reputation: 27262

In G2 I entered

=ArrayFormula(iferror(vlookup(unique(Sheet1!B2:B), sort({Sheet1!B2:B, Sheet1!A2:F}, 7, 0), {2, 3, 4, 5, 6}, 0)))

UPDATE: Based on new info...

=query(ArrayFormula(iferror(vlookup(unique(Sheet1!B2:B), sort(filter({Sheet1!B2:B, Sheet1!A2:F}, not(regexmatch(Sheet1!B2:B&"", textjoin("|", 1, filter('Barcodes In/Out'!D2:D, isna(match('Barcodes In/Out'!D2:D, 'Barcodes In/Out'!E2:E, 0))))))), 7, 0), {2, 3, 4, 5, 6}, 0))), "where Col1 <>''")

See if that works for you?

Upvotes: 1

Related Questions