Does anyone have an idea how to solve this problem using Excel? I have this table. As you can see there is false information in the data, as the "Customer_since" column has different values for the same customer (column "cust_id"). "Customer since" column defines the year of the customer's first purchase. Here is the table:

In fact, the year of the first purchase must be the same in all rows for the same client. In my solution, I would like to keep the earliest year (of the first purchase) for all clients. As you can see one client can appear in many rows, for example, client 275250 had 8 purchases (means 8 rows in a data set) and client 275246 had only 4 purchases. I cannot change it manually as the data set contains over 7000 rows. The desired solution would be to get the same year of their first purchase for the same customer in all rows (for example, correct data is for 275252 and 275233 customers).

CodePudding user response：

MINIFS will get you there.

CodePudding user response：

I would do the following:

1. Create a new table with unique cust_id and the corresponding min(Customer_since). A PivotTable would do it easily.

2. Change the column Customer_since to use XLOOKUP() to find the correct value on the new table.

If needed, you can do it on another worksheet and Paste Values back into this.