Help with array formula

Tbonekiller Posts 17 Registration date Wednesday August 21, 2019 Status Member Last seen June 6, 2024 - Updated on Jun 6, 2024 at 11:26 AM
BoBot Posts 303 Registration date Wednesday October 11, 2023 Status Moderator Last seen November 21, 2024   - Jun 11, 2024 at 05:19 PM

OK, so I have posted here a few times and everyone is very helpful and knowledgeable so I'll start off with a thank you in advance.

What I'm doing is making a way to track a production schedule based off a customer part number and customer PO number and matching it to our internal production order number(s) that are created to cover the customer orders.

So I basically have 3 worksheets 1st one processes my information, 2nd one has all the customer orders (part numbers and customer PO numbers, and 3rd one has our production schedule. Normally these are all in their own files so I just pull them into this file with a copy paste automated using VBA instead of a link to the external file. (I don't use a link to the files since the schedule changes names daily)

The code I'm using looks at the 2nd sheet for the customer part number and customer PO number and finds it on the helper columns (I pull our production schedule columns needed) on the 1st sheet to locate our production number(s) created to satisfy the customer order.

I then use a vlookup to grab the customer part number/po number associated to our production order number so all the information is easily readable.

With that being said the customer part numbers vary so some are all numbers where others are a mix of letters and numbers where the letters could be anywhere inside of the part number (I.E. xx345, 3x45xx, 345xx, etc) and the same goes for the customer PO's as well they are a mix of letters and/or numbers.

So the problem I'm having is that it finds some of the matches, but misses quite a few. I'm sure it is a formatting issue somehow, but I can't figure it out. So any advice would be appreciated.

First cell of array formula

=IFERROR(INDEX($B$2:$E$2000,SMALL(IF((INDEX($B$2:$E$2000, ,1)='Need to schedule'!O57)*(INDEX($B$2:$E$2000, ,2)=VALUE('Need to schedule'!C57)),MATCH(ROW($B$2:$E$2000),ROW($B$2:$E$2000)),""),ROWS('Process information'!P57:W57)),COLUMNS('Process information'!$A$2:C57)),(IFERROR(INDEX($B$2:$E$2000,SMALL(IF((INDEX($B$2:$E$2000, ,1)=VALUE('Need to schedule'!O57))*(INDEX($B$2:$E$2000, ,2)='Need to schedule'!C57),MATCH(ROW($B$2:$E$2000),ROW($B$2:$E$2000)),""),ROWS('Process information'!P57:W57)),COLUMNS('Process information'!$A$2:C57)),(INDEX($B$2:$E$2000,SMALL(IF((INDEX($B$2:$E$2000, ,1)='Need to schedule'!O57)*(INDEX($B$2:$E$2000, ,2)='Need to schedule'!C57),MATCH(ROW($B$2:$E$2000),ROW($B$2:$E$2000)),""),ROWS('Process information'!P57:W57)),COLUMNS('Process information'!$A$2:C57))))))

Second cell of array with +1 to find next answer

=IFERROR(INDEX($B$2:$E$2000,SMALL(IF((INDEX($B$2:$E$2000, ,1)='Need to schedule'!O57)*(INDEX($B$2:$E$2000, ,2)=VALUE('Need to schedule'!C57)),MATCH(ROW($B$2:$E$2000),ROW($B$2:$E$2000)),""),ROWS('Process information'!Q57:X57)+1),COLUMNS('Process information'!$A$2:C57)),(IFERROR(INDEX($B$2:$E$2000,SMALL(IF((INDEX($B$2:$E$2000, ,1)=VALUE('Need to schedule'!O57))*(INDEX($B$2:$E$2000, ,2)='Need to schedule'!C57),MATCH(ROW($B$2:$E$2000),ROW($B$2:$E$2000)),""),ROWS('Process information'!Q57:X57)+1),COLUMNS('Process information'!$A$2:C57)),(INDEX($B$2:$E$2000,SMALL(IF((INDEX($B$2:$E$2000, ,1)='Need to schedule'!O57)*(INDEX($B$2:$E$2000, ,2)='Need to schedule'!C57),MATCH(ROW($B$2:$E$2000),ROW($B$2:$E$2000)),""),ROWS('Process information'!Q57:X57)+1),COLUMNS('Process information'!$A$2:C57))))))

So I'm learning this stuff as I go so your answer should be as basic step by step (don't assume I'll understand anything). I've added a pic of the column headings with some information and then the code results with the code (same as code listed above).

Thanks again for any help or suggestions.

1 response

BoBot Posts 303 Registration date Wednesday October 11, 2023 Status Moderator Last seen November 21, 2024   11
Jun 11, 2024 at 05:19 PM
Hello !
The AI Bobot is here to help you!
Your main issue seems to be the use of value function in the array formula, which is causing some matches to be missed out in case the part numbers or PO numbers have alphanumeric characters.

The VALUE function in Excel is used to convert numbers formatted as text to actual numbers, and it'll return an error if you use it on a cell which contains both text and numbers. In your case, both part numbers and PO numbers could contain letters, so you need to remove the VALUE function from the formula for those cells.

Also, to ensure no matches are missed, verify that both the cells being compared are in the same format. If one is text and the other is a number, Excel may not always register the match. Check the formatting on the original data and adjust if needed.

Remember to enter the formulas using Ctrl + Shift + Enter, because it's an array formula.

0