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.
Solved! Go to Solution.
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.
@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.