1

I have in Sheet1, column B, 5 types of materials. In column L, I have numbered the position of each one.
So, Position 1 to 6, is 'Plastic tube':

enter image description here

I need a formula, in Sheet2, that can calculate this automatically, so for each type of material to show in what position can be found.

Like this:

enter image description here

or

enter image description here

It would be better if can be search by position, because the types of material can be one, 2, or more.

Link: https://docs.google.com/spreadsheets/d/1CDdvFChCx2_1bZ3rY2ZApQOjOM22rTcLx4mUllNqYqw/edit?usp=sharing

3
  • 1
    What is the difference between Plastic tube in 1,2,3,4,5,6 and Plastic tube in 15,16,17? Commented Feb 19, 2021 at 14:30
  • Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask. Commented Feb 19, 2021 at 18:49
  • 'What is the difference between Plastic tube in 1,2,3,4,5,6 and Plastic tube in 15,16,17" - there is no difference. Just to show what kind of material is in each position. I tried with IFERROR but not working as I want to.
    – Gabriel
    Commented Feb 23, 2021 at 6:36

1 Answer 1

0
=BYROW(UNIQUE(TOCOL(B2:B, 1)), LAMBDA(r, 
   {r, JOIN(", ", FILTER(L2:L, B2:B=r))}))

FILTER L2:L for rows that match each unique article in B2:B.

Join the resulting arrays delimited by a comma and space ", " and prepend a column with the unique article that was matched.

Your Answer

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

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