INDEX COLLECT formula

Barbara Witt

I kindly ask for help with my logic.

I am looking to display the most recent UPS tracking number for a specific project number. I can use the most recent ship date for this specific project number. I commonly have several shipments that use the same project number on several shipping dates.

My thought was as follows, but is says "unparseable":

Collect the Tracking Number, where there is one (cell is not empty), where the project numbers lined up and the ship dates matches the most recent ship date.

=INDEX(COLLECT({Shipping Tracking}, {Shipping Tracking}, @cell<>" ",{Project #}, [Project Number]@row), {Most Recent Ship Date}, [ship date]@row),1)

Does that make sense? Thanks so much.

Barbara

P.S. @KDM See above, I thought about using your INDEX/COLLECT approach......

Kelly Moore

Hey Barbara

I see an extra space in the 'not blank' cell- but that wouldn't give you the unparseable - just no data. I think you inadvertently left an extra parenthesis after the [Project Number]@row. Also - is your [ship date] matching the case? I ask since you usually capitalize your column names? If your column names are colored, then it's correct.

Kelly Moore

Hey Barbara

I see an extra space in the 'not blank' cell- but that wouldn't give you the unparseable - just no data. I think you inadvertently left an extra parenthesis after the [Project Number]@row. Also - is your [ship date] matching the case? I ask since you usually capitalize your column names? If your column names are colored, then it's correct.

Barbara Witt

Hey @KDM, yes, it must have been the extra parenthesis, because it's working now. Thanks so much.

Paul Newcome

NOTE: Column names are not case sensitive when manually typing them into a formula, and when referencing "not blank", the space will make no difference. "" is the same as " ".

Barbara Witt

Hi @KDM, I am looking for your trouble shooting skills, again. I rebuilt this formula many times, but it still won't work. I added @value, but it still does not work. It works in another cell, but it does not work here. I have taken it apart, so I could figure out which part does not work and I don't understand why it does not work. What else could I be doing wrong?

This is the version, which works in another cell:

=INDEX(COLLECT({DAX Ambient Inventory Shipping Tracking}, {DAX Ambient Inventory Shipping Tracking}, @cell <> "", {DAX Ambient Inventory Project #}, [Project Number]@row, {DAX Ambient Inventory Ship Date}, [Ambient device ship date]@row), 1)

I mirrored it, but it says "invalid". It's exactly the same:

=INDEX(COLLECT({DAX iOS Apple Watch Inventory Shipping Tracking}, {DAX iOS Apple Watch Inventory Shipping Tracking}, @cell <> "", {DAX iOS Apple Watch Inventory Project Number}, [Project Number]@row, {DAX iOS Apple Watch Inventory Ship Date}, [Apple ship date]@row), 1)

If I count, it comes back with accurate data, so I know data is there.

=COUNTIFS({DAX iOS Apple Watch Inventory Shipping Tracking}, @cell <> "", {DAX iOS Apple Watch Inventory Project Number}, <>"", {DAX iOS Apple Watch Inventory Project Number}, [Project Number]@row, {DAX iOS Apple Watch Inventory Ship Date}, [Apple ship date]@row)

Kelly Moore

Hey Barbara

What if you added one additional criteria to your formula - {DAX iOS Apple Watch Inventory Ship Date}, ISDATE(@cell)

Kelly