Find Min from a Dynamic Number of Fields

Question

I have a series of fields, "Date_Product1", "Date_Product2", "Date_Product3" etc. The number of these fields varies (needs to be able to handle 10 or 30 product fields). They each have a purchase date in them. I need to create a "First Product" field that pulls in the field name with the earliest date, whose calculation method can handle a changing number of fields.

Any ideas?

Dynamic_First_Product_Table.png

jdunkerley79 · Accepted Answer

The easiest way to do this would be to:

1. transpose the data keeping the User as a Key field

2. Sort by User and Value

3. Sample the first for each user

4. Join back to input

Sample attached