CODEWITHSUNDEEP
databasegoogle-sheetsfilterformulareduce
J-Drake
J-Drake

Reputation: 5

Trouble With Reduce Function in Google Sheets

I have a sheet of tools that I have organized that I called "DATABASE" which is essentially just a list of my tools that are organized by their location and ID number:

DATABASE SCREENSHOT

I also have a printable version of this that I use to show to my supervisors. This version utilizes the following formula to import the data from DATABASE:

=reduce(tocol(,1),unique(filter(DATABASE!K:K,DATABASE!L:L=B4)),lambda(a,c,ifna(vstack(a,regexreplace(c,"(^.*? - )",),filter(DATABASE!F:J,DATABASE!K:K=c)))))

The output of which looks like this:

DISPLAY/PRINT DOCUMENT

Now, the problem I am running into, is that the tools that are in kits are being appended to the bottom of the list instead of being inserted in line where they should appear.

You can see from the following list that tools that are part of a kit are skipped in the initial list and appended to the bottom out of order of the initial list:

TOOL SETS OUT OF ORDER

I was hoping someone would be able to help me adjust the formula that is used to import the list so that it would place those tools in their proper place in line.

Sample Sheet: https://docs.google.com/spreadsheets/d/1MncwXkiZNAoSMvVk21o3_4yuoOm5jMhZATPcp1VvZjM/edit?usp=sharing

Thank you all very much for your time!

I attempted to change different parts of the formula but each change resulted in failure.

Upvotes: 0

Views: 68

Answers (0)

Related Questions