Find minimum value of a group of data then create new column with min value and IDs

[JPSeagull]

Hi. I have tried various ways, but I simply need to isolate any Item numbers that have multiple Account IDs with Days to Pay values and then list the minimum Days to Pay and the associated Account ID to be for the entire group of Item Numbers. Here is the raw data (with Item ID that appears more than once bolded)

Item ID	Account Number	Days to Pay
1	B4578	15
2	B8873	30
3	Z5444	30
3	T7712	45
3	V0023	15
4	C6912	30
5	M0385	15
5	P0005	30
6	A9734	30

Here is what I want to get to (I bolded the Item ID that appears more than once)

Item ID	Account Number	Days to Pay	Account Number (By min Days to Pay Min)	Days to Pay on Group Min
1	B4578	15	B4578	15
2	B8873	30	B8873	30
3	Z5444	30	V0023	15
3	T7712	45	V0023	15
3	V0023	15	V0023	15
4	C6912	30	C6912	30
5	M0385	15	M0385	15
5	P0005	30	M0385	15
6	A9734	30	A9734	30

[IraWatt · Accepted solution]

Hey @JPSeagull,

One way is to sort the data with the sort tool then grab the lowest element for each Item ID with the sample tool. Finally join it all together:

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[JPSeagull]

@IraWatt - Thank you! It works perfect. Much appreciated!