Replace Column data grouped by number based on most recent date

[Nmassarone]

Hello, hoping someone could help with the following I've been messing around with this for a little while and cant seem to figure out a solution...

I have a list of accounts that I need to fill in the annual income based on the most recent date (if applicable). there a few rows of examples I have on my sheet I added a column (MSTR_ANN_INCOME) to try to get a single value for the asked & not answered and Not asked as those would need to be replaced as well.

For Examples Row 1 is the header column…

The 1st thing I would want to look for is if the group (of MSTR_NUM)  all have no then the annual income would be filled in with the number

Example Rows 2-4 – because 2 & 4 are NO it would fill in 2-4 with 50k – 100k

The 2nd thing I would want to look for is if there are 2 different MSTR_ANN_INCOME (besides the NO) that it picks the most recent date and fills in all with the most recent date Annual income.

Example Rows 5-7 because 6&7 have different annual incomes but row 6 has a more recent date rows 5-7 would be filled in with 25k – 50k

The 3rd thing I would want to look for is if there are no dates (NULL) then it would pick the highest amount and fill in with the highest.

Example Rows 19 – 21 because 20&21 are missing dates I would want it to take the 250k – 500k and fill in 19-20 with 250k – 500k

Rows 8-18 are additional data

Below is the starting chart

MSTR_NUM	ANNUAL_INC_DESC	ACCT_OPEN_DT	NAF_or_BROK	MSTR_ANN_INCOME
178439			1894	NO
178439	50K - 100K	10/30/2017	JV737	50K - 100K
178439	ASKED & REFUSED		93857	NO
21958		2/18/2014	3312	NO
21958	25K - 50K	6/19/2019	H70198	25K - 50K
21958	50K - 100K	4/9/2002	2092	50K - 100K
84756		7/12/2018	4754	NO
84756		5/20/2020	5816	NO
84756	25K - 50K	7/23/2007	9631	25K - 50K
84756	ASKED & REFUSED	7/26/2017	4090	NO
84756	NOT ASKED	10/6/2015	F54818	NO
37372			16830	NO
37372	250K - 500K	5/31/2019	HV60705	250K - 500K
37372	500K+	6/10/2019	5183387	500K+
68673			445608	NO
68673	100K - 250K	1/24/2020	JA87640	100K - 250K
68673	ASKED & REFUSED		42376	NO
94837		5/19/2008	156868	NO
94837	100K - 250K		49464	100K - 250K
94837	250K - 500K		1768791	250K - 500K

Below the last row (IDEAL COLUMN) is what i would want the results to be (the MSTR_ANN_INCOME row could be deleted)

MSTR_NUM	ANNUAL_INC_DESC	ACCT_OPEN_DT	NAF_or_BROK	MSTR_ANN_INCOME	IDEAL COLUMN
178439			1894	NO	50K - 100K
178439	50K - 100K	10/30/2017	JV737	50K - 100K	50K - 100K
178439	ASKED & REFUSED		93857	NO	50K - 100K
21958		2/18/2014	3312	NO	25K - 50K
21958	25K - 50K	6/19/2019	H70198	25K - 50K	25K - 50K
21958	50K - 100K	4/9/2002	2092	50K - 100K	25K - 50K
84756		7/12/2018	4754	NO	25K - 50K
84756		5/20/2020	5816	NO	25K - 50K
84756	25K - 50K	7/23/2007	9631	25K - 50K	25K - 50K
84756	ASKED & REFUSED	7/26/2017	4090	NO	25K - 50K
84756	NOT ASKED	10/6/2015	F54818	NO	25K - 50K
37372			16830	NO	500K+
37372	250K - 500K	5/31/2019	HV60705	250K - 500K	500K+
37372	500K+	6/10/2019	5183387	500K+	500K+
68673			445608	NO	100K - 250K
68673	100K - 250K	1/24/2020	JA87640	100K - 250K	100K - 250K
68673	ASKED & REFUSED		42376	NO	100K - 250K
94837		5/19/2008	156868	NO	250K - 500K
94837	100K - 250K		49464	100K - 250K	250K - 500K
94837	250K - 500K		1768791	250K - 500K	250K - 500K

Any help would be greatly appreciated.

thank you.

[mceleavey]

@Nmassarone ,

on the first sort tool I chose record ID rather than MSTR_NUM.

This is just a case of playing with this area to ensure it's in the order you want it.

M.

[Nmassarone]

@mceleavey i think i figured it out my original sheet already had the date column set to date so i think the date tool was putting it out of whack ... i removed that and everything flowed through perfect and got all the results so it was a success! 

thank you again for the post and all your help on this i really appreciate it.