Tag duplicate rows based on a specific criteria

AKAvenger

New Member
Joined
Mar 22, 2017
Messages
32
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.

File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
785125689026417687804833Xxonel LLCGeneric90508676118/1/20192828278.44Ignore
7851256890264176878038Lion IncSales90508675388/1/20199978278.44Ignore
7851256890264176877995Bugs be goneSales90508675818/1/201979748278.44Primary
7851256890264176878048445Geeks & LeeksGeneric90508676168/1/2019854698278.44Ignore
File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
711075690893071390063Bradley's OrangesSales3009552338/1/20191115936.19Primary
711075690893071455039Olivia & CompanyGeneric3009554308/1/2019245695936.19Ignore
File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
785125689023927687804833Xxonel LLCGeneric90508675808/1/201958544331.82Ignore
7851256890239276878048Sunshine TailersGeneric90508675798/1/2019274654331.82Primary
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:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Re: Need Help - how to tag duplicate rows based on a specific criteria

How about:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:99.8px;" /><col style="width:96px;" /><col style="width:123.56px;" /><col style="width:97.9px;" /><col style="width:83.64px;" /><col style="width:79.84px;" /><col style="width:79.84px;" /><col style="width:82.69px;" /><col style="width:113.11px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">File Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Name</td><td style="background-color:#ffff00; text-align:center; ">Account Owner Role</td><td style="background-color:#ffff00; text-align:center; ">Invoice Number</td><td style="background-color:#ffff00; text-align:center; ">Invoice Date</td><td style="background-color:#ffff00; text-align:center; ">Invoice Amount</td><td style="background-color:#ffff00; text-align:center; ">Profit Amount of File Number</td><td style="background-color:#ffff00; text-align:center; ">Primary File Number line TAG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">7687804833</td><td >Xxonel LLC</td><td >Generic</td><td style="text-align:right; ">9050867611</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">282</td><td style="text-align:right; ">8278.44</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">76878038</td><td >Lion Inc</td><td >Sales</td><td style="text-align:right; ">9050867538</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">997</td><td style="text-align:right; ">8278.44</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">76877995</td><td >Bugs be gone</td><td >Sales</td><td style="text-align:right; ">9050867581</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">7974</td><td style="text-align:right; ">8278.44</td><td >Primary</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">76878048445</td><td >Geeks & Leeks</td><td >Generic</td><td style="text-align:right; ">9050867616</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">85469</td><td style="text-align:right; ">8278.44</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff00; text-align:center; ">File Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Name</td><td style="background-color:#ffff00; text-align:center; ">Account Owner Role</td><td style="background-color:#ffff00; text-align:center; ">Invoice Number</td><td style="background-color:#ffff00; text-align:center; ">Invoice Date</td><td style="background-color:#ffff00; text-align:center; ">Invoice Amount</td><td style="background-color:#ffff00; text-align:center; ">Profit Amount of File Number</td><td style="background-color:#ffff00; text-align:center; ">Primary File Number line TAG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">71107569089307</td><td style="text-align:right; ">1390063</td><td >Bradley's Oranges</td><td >Sales</td><td style="text-align:right; ">300955233</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">111</td><td style="text-align:right; ">5936.19</td><td >Primary</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">71107569089307</td><td style="text-align:right; ">1455039</td><td >Olivia & Company</td><td >Generic</td><td style="text-align:right; ">300955430</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">24569</td><td style="text-align:right; ">5936.19</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffff00; text-align:center; ">File Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Name</td><td style="background-color:#ffff00; text-align:center; ">Account Owner Role</td><td style="background-color:#ffff00; text-align:center; ">Invoice Number</td><td style="background-color:#ffff00; text-align:center; ">Invoice Date</td><td style="background-color:#ffff00; text-align:center; ">Invoice Amount</td><td style="background-color:#ffff00; text-align:center; ">Profit Amount of File Number</td><td style="background-color:#ffff00; text-align:center; ">Primary File Number line TAG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">78512568902392</td><td style="text-align:right; ">7687804833</td><td >Xxonel LLC</td><td >Generic</td><td style="text-align:right; ">9050867580</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">5854</td><td style="text-align:right; ">4331.82</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">78512568902392</td><td style="text-align:right; ">76878048</td><td >Sunshine Tailers</td><td >Generic</td><td style="text-align:right; ">9050867579</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">27465</td><td style="text-align:right; ">4331.82</td><td >Primary</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >I2</td><td >{=IF(COUNTIFS($A$2:$A$15,A2,$D$2:$D$15,"Sales")=0,IF(MAX(IF($A$2:$A$15=A2,$G$2:$G$15))=G2,"Primary","Ignore"),IF(MAX(IF(($A$2:$A$15=A2)*($D$2:$D$15="Sales"),$G$2:$G$15))=G2,"Primary","Ignore"))}</td></tr></table></td></tr></table>


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.
 
Upvote 0
Re: Need Help - how to tag duplicate rows based on a specific criteria

Hey DanteAmor

Thank you so much for your reply.
When I place all the data in a table without the split with headers etc, it does not work as expected. The formula needs to only calculate on 1 file number at a time.
thanks again, hope you can help

File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
785125689026417687804833Xxonel LLCGeneric90508676118/1/20192828278.44Ignore
7851256890264176878038Lion IncSales90508675388/1/20199978278.44Ignore
7851256890264176877995Bugs be goneSales90508675818/1/201979748278.44Primary
7851256890264176878048445Geeks & LeeksGeneric90508676168/1/2019854698278.44Ignore
711075690893071390063Bradley's OrangesSales3009552338/1/20191115936.19Primary
711075690893071455039Olivia & CompanyGeneric3009554308/1/2019245695936.19Ignore
785125689023927687804833Xxonel LLCGeneric90508675808/1/201958544331.82Ignore
7851256890239276878048Sunshine TailersGeneric90508675798/1/2019274654331.82Primary

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>



How about:

ABCDEFGHI
1File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
2785125689026417687804833Xxonel LLCGeneric905086761108/01/20192828278.44Ignore
37851256890264176878038Lion IncSales905086753808/01/20199978278.44Ignore
47851256890264176877995Bugs be goneSales905086758108/01/201979748278.44Primary
57851256890264176878048445Geeks & LeeksGeneric905086761608/01/2019854698278.44Ignore
6
7
8File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
9711075690893071390063Bradley's OrangesSales30095523308/01/20191115936.19Primary
10711075690893071455039Olivia & CompanyGeneric30095543008/01/2019245695936.19Ignore
11
12
13File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
14785125689023927687804833Xxonel LLCGeneric905086758008/01/201958544331.82Ignore
157851256890239276878048Sunshine TailersGeneric905086757908/01/2019274654331.82Primary

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:99.8px;"><col style="width:96px;"><col style="width:123.56px;"><col style="width:97.9px;"><col style="width:83.64px;"><col style="width:79.84px;"><col style="width:79.84px;"><col style="width:82.69px;"><col style="width:113.11px;"></colgroup><tbody>
</tbody>

CellArray Formula
I2{=IF(COUNTIFS($A$2:$A$15,A2,$D$2:$D$15,"Sales")=0,IF(MAX(IF($A$2:$A$15=A2,$G$2:$G$15))=G2,"Primary","Ignore"),IF(MAX(IF(($A$2:$A$15=A2)*($D$2:$D$15="Sales"),$G$2:$G$15))=G2,"Primary","Ignore"))}

<tbody>
</tbody>

<tbody>
</tbody>



Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.
 
Upvote 0
Re: Need Help - how to tag duplicate rows based on a specific criteria

Is an Array formula, To enter you must edit it and press Shift + Control + Enter at the same time.
The formula works without the headings:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:128.32px;" /><col style="width:96px;" /><col style="width:123.56px;" /><col style="width:82.69px;" /><col style="width:76.99px;" /><col style="width:75.09px;" /><col style="width:74.14px;" /><col style="width:82.69px;" /><col style="width:113.11px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">File Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Number</td><td style="background-color:#ffff00; text-align:center; ">Customer Account Name</td><td style="background-color:#ffff00; text-align:center; ">Account Owner Role</td><td style="background-color:#ffff00; text-align:center; ">Invoice Number</td><td style="background-color:#ffff00; text-align:center; ">Invoice Date</td><td style="background-color:#ffff00; text-align:center; ">Invoice Amount</td><td style="background-color:#ffff00; text-align:center; ">Profit Amount of File Number</td><td style="background-color:#ffff00; text-align:center; ">Primary File Number line TAG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">7687804833</td><td >Xxonel LLC</td><td >Generic</td><td style="text-align:right; ">9050867611</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">282</td><td style="text-align:right; ">8278.44</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">76878038</td><td >Lion Inc</td><td >Sales</td><td style="text-align:right; ">9050867538</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">997</td><td style="text-align:right; ">8278.44</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">76877995</td><td >Bugs be gone</td><td >Sales</td><td style="text-align:right; ">9050867581</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">7974</td><td style="text-align:right; ">8278.44</td><td >Primary</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">78512568902641</td><td style="text-align:right; ">76878048445</td><td >Geeks & Leeks</td><td >Generic</td><td style="text-align:right; ">9050867616</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">85469</td><td style="text-align:right; ">8278.44</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">71107569089307</td><td style="text-align:right; ">1390063</td><td >Bradley's Oranges</td><td >Sales</td><td style="text-align:right; ">300955233</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">111</td><td style="text-align:right; ">5936.19</td><td >Primary</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">71107569089307</td><td style="text-align:right; ">1455039</td><td >Olivia & Company</td><td >Generic</td><td style="text-align:right; ">300955430</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">24569</td><td style="text-align:right; ">5936.19</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">78512568902392</td><td style="text-align:right; ">7687804833</td><td >Xxonel LLC</td><td >Generic</td><td style="text-align:right; ">9050867580</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">5854</td><td style="text-align:right; ">4331.82</td><td >Ignore</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">78512568902392</td><td style="text-align:right; ">76878048</td><td >Sunshine Tailers</td><td >Generic</td><td style="text-align:right; ">9050867579</td><td style="text-align:right; ">08/01/2019</td><td style="text-align:right; ">27465</td><td style="text-align:right; ">4331.82</td><td >Primary</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >I2</td><td >{=IF(COUNTIFS($A$2:$A$9,A2,$D$2:$D$9,"Sales")=0,IF(MAX(IF($A$2:$A$9=A2,$G$2:$G$9))=G2,"Primary","Ignore"),IF(MAX(IF(($A$2:$A$9=A2)*($D$2:$D$9="Sales"),$G$2:$G$9))=G2,"Primary","Ignore"))}</td></tr></table></td></tr></table>
 
Upvote 0
Re: Need Help - how to tag duplicate rows based on a specific criteria

Hi Dante, thank you kindly :)

Is an Array formula, To enter you must edit it and press Shift + Control + Enter at the same time.
The formula works without the headings:

ABCDEFGHI
1File NumberCustomer Account NumberCustomer Account NameAccount Owner RoleInvoice NumberInvoice DateInvoice AmountProfit Amount of File NumberPrimary File Number line TAG
2785125689026417687804833Xxonel LLCGeneric905086761108/01/20192828278.44Ignore
37851256890264176878038Lion IncSales905086753808/01/20199978278.44Ignore
47851256890264176877995Bugs be goneSales905086758108/01/201979748278.44Primary
57851256890264176878048445Geeks & LeeksGeneric905086761608/01/2019854698278.44Ignore
6711075690893071390063Bradley's OrangesSales30095523308/01/20191115936.19Primary
7711075690893071455039Olivia & CompanyGeneric30095543008/01/2019245695936.19Ignore
8785125689023927687804833Xxonel LLCGeneric905086758008/01/201958544331.82Ignore
97851256890239276878048Sunshine TailersGeneric905086757908/01/2019274654331.82Primary

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:128.32px;"><col style="width:96px;"><col style="width:123.56px;"><col style="width:82.69px;"><col style="width:76.99px;"><col style="width:75.09px;"><col style="width:74.14px;"><col style="width:82.69px;"><col style="width:113.11px;"></colgroup><tbody>
</tbody>

CellArray Formula
I2{=IF(COUNTIFS($A$2:$A$9,A2,$D$2:$D$9,"Sales")=0,IF(MAX(IF($A$2:$A$9=A2,$G$2:$G$9))=G2,"Primary","Ignore"),IF(MAX(IF(($A$2:$A$9=A2)*($D$2:$D$9="Sales"),$G$2:$G$9))=G2,"Primary","Ignore"))}

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Re: Need Help - how to tag duplicate rows based on a specific criteria

I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top