0

I have data spread out on rows (SKU#s) that relate to a part# on the same row. I have TEXTJOINED the SKU# cells into one cell on each row. Then, on another sheet I have a formula that uses IF, INDEX and MATCH to look at a SKU# next to it, then search for that SKU# in the textjoined cell and return the part#. It works well as long as there aren't too many SKU#s textjoined together. 12 SKU#s works fine, 30 does not. I have as many as 80 SKU#s on a row.

I only know that one row of data that has 12 SKU#s works, but the rows with 30 or more SKU#s do not. Unfortunately I don't have rows with say 15 or 20 so I can't determine exactly what the limit is.

I should note that all of the textjoined cells have data in them - none of them have thrown up an error.

The TEXTJOIN formula starting on row 5.

=TEXTJOIN(",",TRUE,H5:CI5)

The IF, INDEX & MATCH formula starting on row 5.

=IF(LEN(A5)=8,INDEX('DEST MISMATCH'!$CO$3:$CO$5000,MATCH("*"&A5&"*",'DEST MISMATCH'!$CM$3:$CM$5000,0)),"")

What I need to have happen is for the formulas to work on up to 80 textjoined values (SKU#s).

Norm Robichaud
  • 7
  • 3
  • How many matches at a time are you doing? It can be done simpler with an array type formula but too many will cause issues with the recalc. – Scott Craner Aug 20 '19 at 19:11
  • `=IF(LEN(A5)=8,INDEX('DEST MISMATCH'!$CO$3:$CO$5000,AGGREGATE(15,7,ROW('DEST MISMATCH'!$H$3:$CI$5000)/('DEST MISMATCH'!$H$3:$CI$5000=A5),1)),"")` – Scott Craner Aug 20 '19 at 19:14
  • On the sheet 'DEST MISMATCH' (it's essentially a pick list) I will most likely be copying and pasting a bunch of them at a time, the most would be all of the SKUs at once, which is 300-ish. Because the 'DEST MISMATCH' sheet has conditional formatting to highlight duplicates (to see if I've picked each unit), scanning them one at a time is painfully slow. – Norm Robichaud Aug 20 '19 at 19:55
  • Of course, I could remove the conditional formatting and do it at the end to avoid the slow data entry. ;) – Norm Robichaud Aug 20 '19 at 20:00
  • I tried your formula and it is mostly working except it's returning part#s in the row below the correct part#. – Norm Robichaud Aug 20 '19 at 20:33
  • Are the references correct for that line? – Scott Craner Aug 20 '19 at 20:34
  • As far as I can tell they are. On row A5 I see A5 in the formula. Otherwise everything else is static. Same on other rows, say, A42 would show A42 in the formula. – Norm Robichaud Aug 20 '19 at 20:41
  • Change `'DEST MISMATCH'!$CO$3:$CO$5000` to `'DEST MISMATCH'!$CO:$CO` sorry my bad. – Scott Craner Aug 20 '19 at 20:42
  • That did it. Thanks for all your help. I owe you a beer. :) – Norm Robichaud Aug 20 '19 at 20:46
  • I think you need to post your answer as an answer instead of a comment. Then I can smack that check-mark and give you credit. :) The formula that worked was, =IF(LEN(A5)=8,INDEX('DEST MISMATCH'!$CO:$CO,AGGREGATE(15,7,ROW('DEST MISMATCH'!$H$3:$CI$5000)/('DEST MISMATCH'!$H$3:$CI$5000=A5),1)),"") – Norm Robichaud Aug 20 '19 at 20:53
  • Go ahead an self answer. I am AFK, using phone. – Scott Craner Aug 20 '19 at 20:55

1 Answers1

0

The formula that worked was, 

=IF(LEN(A5)=8,INDEX('DEST MISMATCH'!$CO:$CO,AGGREGATE(15,7,ROW('DEST MISMATCH'!$H$3:$CI$5000)/('DEST MISMATCH'!$H$3:$CI$5000=A5),1)),"")

Credit to: Scott Craner

Norm Robichaud
  • 7
  • 3