Hi Excel Guru's
Need help please, I know what my logic should be, I know what my result should be, but I am unable to build any formula to show the result I need.
Below is the data set up and three different scenarios. The column named [Primary File Number line TAG] is where I would like to add the formula.
Hope I can get someone to figure this out.
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Need help please, I know what my logic should be, I know what my result should be, but I am unable to build any formula to show the result I need.
Below is the data set up and three different scenarios. The column named [Primary File Number line TAG] is where I would like to add the formula.
Hope I can get someone to figure this out.
File Number | Customer Account Number | Customer Account Name | Account Owner Role | Invoice Number | Invoice Date | Invoice Amount | Profit Amount of File Number | Primary File Number line TAG |
78512568902641 | 7687804833 | Xxonel LLC | Generic | 9050867611 | 8/1/2019 | 282 | 8278.44 | Ignore |
78512568902641 | 76878038 | Lion Inc | Sales | 9050867538 | 8/1/2019 | 997 | 8278.44 | Ignore |
78512568902641 | 76877995 | Bugs be gone | Sales | 9050867581 | 8/1/2019 | 7974 | 8278.44 | Primary |
78512568902641 | 76878048445 | Geeks & Leeks | Generic | 9050867616 | 8/1/2019 | 85469 | 8278.44 | Ignore |
File Number | Customer Account Number | Customer Account Name | Account Owner Role | Invoice Number | Invoice Date | Invoice Amount | Profit Amount of File Number | Primary File Number line TAG |
71107569089307 | 1390063 | Bradley's Oranges | Sales | 300955233 | 8/1/2019 | 111 | 5936.19 | Primary |
71107569089307 | 1455039 | Olivia & Company | Generic | 300955430 | 8/1/2019 | 24569 | 5936.19 | Ignore |
File Number | Customer Account Number | Customer Account Name | Account Owner Role | Invoice Number | Invoice Date | Invoice Amount | Profit Amount of File Number | Primary File Number line TAG |
78512568902392 | 7687804833 | Xxonel LLC | Generic | 9050867580 | 8/1/2019 | 5854 | 4331.82 | Ignore |
78512568902392 | 76878048 | Sunshine Tailers | Generic | 9050867579 | 8/1/2019 | 27465 | 4331.82 | Primary |
The logic I need | ||||||||
if [account owner role] = "sales" then return "primary" BUT if there are two or more [account owner role] which return as "sales" for same [file number] then return whichever [account owner role] with "sales" row has the highest [Invoice Amount] and return as "Primary". all other rows for the same file number to be tagged as "ignore". | ||||||||
if all rows for the same [file number] does not contain [account owner role] "sales" then the row with the highest [Invoice amount] if to be tagged as "primary", all others tagged as "ignore". |
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Last edited: