Formula UNIQUE across multiple columns

[pure vito]

Hi All,

Hoping for some help if possible please,

Column K,L,M have a list of parts/qty's/packing slips, columns P,Q,R I have used the UNIQUE formula to remove duplicates, what I didn't account for is ("K") could have the same part number but have a different QTY/Pack ("L") and ("M")

What I'm hoping to achieve is to remove any items that have the same parts/qty's/packing slips across the 3 columns and then display them in P,Q,R

Hope this makes sense any help would be appreciated,

 

[alansidman]

Cannot manipulate data in a picture. Please reload the sample data to this forum using XL2BB so that we don't have to retype all your data. Help us to help you.

 

[saboh12617]

Hello,

I would do a concatenation of the row values to filter them row by row.

But then to recreate the array you need to use a construction array method, i found solutions with either MAKEARRAY or REDUCE. Please find below the proposition with MAKEARRAY.

=LET(mydatas, K5:M500,
concatT, BYROW(mydatas, ARRAYTOTEXT),
uVals, UNIQUE(concatT),
MAKEARRAY(ROWS(uVals), 3, LAMBDA(r, c, INDEX(mydatas, XMATCH(INDEX(uVals, r), concatT), c))))

 

[Georgiboy]

Do you wish to SUM the QTY if the row has the same part number and packing slip?

If you wish to just remove duplicates the UNIQUE should suffice as long as all three columns are included in the range passed to the UNIQUE function as below:

=UNIQUE(TRIMRANGE(K4:M10000))

 

[pure vito]

Hi @alansidman, Unfortunately I have tried multiple times and simply can't get XL2BB to work on my computer, I think it being a work computer is the issue, I know it would help greatly and I wish I could make use of it,

@saboh12617 you have come to my rescue again can't thank you enough for taking the time, I genuinely hope you didn't have to type this up, Thanks!

@Georgiboy elegant solution again thank you for looking into this for me, It always amazes me the level of knowledge here I also love the fact questions can have have multiple solutions

 

[pure vito]

Do you wish to SUM the QTY if the row has the same part number and packing slip?

If you wish to just remove duplicates the UNIQUE should suffice as long as all three columns are included in the range passed to the UNIQUE function as below:

=UNIQUE(TRIMRANGE(K4:M10000))

Looking at your solution, that would actually help to sum the Qty of matching part numbers the packing slip matching isn't important in that scenario

 

[saboh12617]

Hello again,

I recommend you use the solution of @pure vito , I don't know where I had my mind, I forgot that UNIQUE was already doing what you wanted by default. It is way simpler/faster/elegant.

Have a good day.

 

[Georgiboy]

Looking at your solution, that would actually help to sum the Qty of matching part numbers the packing slip matching isn't important in that scenario

Thanks for the feedback.

If you wanted to sum the QTY based on matching part numbers alone, you could use the PIVOTBY function:

=PIVOTBY(TRIMRANGE(K4:K10000),,TRIMRANGE(L4:L10000),SUM,,0)

 

[pure vito]

That's perfect, Thanks again for taking the time