Collect Join Help

Ian Cooper

I have two sheets. Sheet 1 is where requests are tracked. The user enters the date, Part Number, and Quantity they need.

Sheet 2 is where the deliveries are posted. It contains the date of the delivery, Part Number, Quantity, and location for the parts.

Back on Sheet 1, I have a column that I want to pull in the date of the delivery from sheet 2 that is greater than or equal to the requested date for the part number and quantity requested.

I then have a second column on sheet 1 where I want to pull in the location from sheet 2. Same parameters. I've viewed several of @Genevieve P. comments, but I have not been able to nail this down. Any help would be greatly appreciated.

Kelly Moore

Hey @Ian Cooper

One approach is to the Index/Collect which can pull the delivery date (or location) that corresponds to the part number. Can multiple orders for the same part number be in motion simultaneously - if this is true do you have an order number on both sheets that would help tie the correct date to the specific order? Unless you are moving rows off of sheet1, you will need a helper column, like a checkbox column, to indicate that you are finished with that row. Otherwise dates in early rows would continue to update as you re-ordered the part number and delivery dates were added. I would use the Change Cell Value Automation (placed on Sheet1) to check the checkbox when the Delivery Date is added. My formula assumes this is in place.

In the event you do not need to worry about order number, here is the formula just looking at part numbers. I added an ISDATE(@cell) to weed out any non-date entries you may have in your sheet2. Of course you would modify the name of any of my column names in the formula to match your actual column name. Remember with the cross sheet references you must physically create those references on your sheet - you cannot simply copy paste these formulas. (In case you need to add the order numbers, you would add both the {sheet2 order number column} and [order number]@row as the criteria following the [part number]@row. Follow the syntax of the other pairs.)

DATE DELIVERED

=IF(Checkbox@row=0, MAX(INDEX(COLLECT({sheet 2 date of delivery}, {sheet 2 date of delivery}, ISDATE(@cell), {sheet 2 part number column}, [part number]@row),1)))

LOCATION

=IF(Checkbox@row=0, INDEX(COLLECT({sheet 2 location}, {sheet 2 date of delivery}, ISDATE(@cell), {sheet 2 date of delivery}, [delivery date]@row, {sheet 2 part number column}, [part number]@row),1))

Change cell value automation

Does this get you what you need?

Kelly