Tag duplicate rows based on a specific criteria

A

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.

[TABLE="class: grid, width: 1048"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]File Number[/TD]
[TD]Customer Account Number[/TD]
[TD]Customer Account Name[/TD]
[TD]Account Owner Role[/TD]
[TD]Invoice Number[/TD]
[TD]Invoice Date[/TD]
[TD]Invoice Amount[/TD]
[TD]Profit Amount of File Number[/TD]
[TD]Primary File Number line TAG[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]7687804833[/TD]
[TD]Xxonel LLC[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867611[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]76878038[/TD]
[TD]Lion Inc[/TD]
[TD]Sales[/TD]
[TD="align: right"]9050867538[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]997[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]76877995[/TD]
[TD]Bugs be gone[/TD]
[TD]Sales[/TD]
[TD="align: right"]9050867581[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]7974[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Primary[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]76878048445[/TD]
[TD]Geeks & Leeks[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867616[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]85469[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File Number[/TD]
[TD]Customer Account Number[/TD]
[TD]Customer Account Name[/TD]
[TD]Account Owner Role[/TD]
[TD]Invoice Number[/TD]
[TD]Invoice Date[/TD]
[TD]Invoice Amount[/TD]
[TD]Profit Amount of File Number[/TD]
[TD]Primary File Number line TAG[/TD]
[/TR]
[TR]
[TD="align: right"]71107569089307[/TD]
[TD]1390063[/TD]
[TD]Bradley's Oranges[/TD]
[TD]Sales[/TD]
[TD="align: right"]300955233[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]5936.19[/TD]
[TD]Primary[/TD]
[/TR]
[TR]
[TD="align: right"]71107569089307[/TD]
[TD]1455039[/TD]
[TD]Olivia & Company[/TD]
[TD]Generic[/TD]
[TD="align: right"]300955430[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]24569[/TD]
[TD="align: right"]5936.19[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File Number[/TD]
[TD]Customer Account Number[/TD]
[TD]Customer Account Name[/TD]
[TD]Account Owner Role[/TD]
[TD]Invoice Number[/TD]
[TD]Invoice Date[/TD]
[TD]Invoice Amount[/TD]
[TD]Profit Amount of File Number[/TD]
[TD]Primary File Number line TAG[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902392[/TD]
[TD]7687804833[/TD]
[TD]Xxonel LLC[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867580[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]5854[/TD]
[TD="align: right"]4331.82[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902392[/TD]
[TD]76878048[/TD]
[TD]Sunshine Tailers[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867579[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]27465[/TD]
[TD="align: right"]4331.82[/TD]
[TD]Primary[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The logic I need[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]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".[/TD]
[/TR]
[TR]
[TD="colspan: 9"]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".[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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

[TABLE="class: grid, width: 1048"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]File Number[/TD]
[TD]Customer Account Number[/TD]
[TD]Customer Account Name[/TD]
[TD]Account Owner Role[/TD]
[TD]Invoice Number[/TD]
[TD]Invoice Date[/TD]
[TD]Invoice Amount[/TD]
[TD]Profit Amount of File Number[/TD]
[TD]Primary File Number line TAG[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]7687804833[/TD]
[TD]Xxonel LLC[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867611[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]76878038[/TD]
[TD]Lion Inc[/TD]
[TD]Sales[/TD]
[TD="align: right"]9050867538[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]997[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]76877995[/TD]
[TD]Bugs be gone[/TD]
[TD]Sales[/TD]
[TD="align: right"]9050867581[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]7974[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Primary[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902641[/TD]
[TD]76878048445[/TD]
[TD]Geeks & Leeks[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867616[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]85469[/TD]
[TD="align: right"]8278.44[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]71107569089307[/TD]
[TD]1390063[/TD]
[TD]Bradley's Oranges[/TD]
[TD]Sales[/TD]
[TD="align: right"]300955233[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]5936.19[/TD]
[TD]Primary[/TD]
[/TR]
[TR]
[TD="align: right"]71107569089307[/TD]
[TD]1455039[/TD]
[TD]Olivia & Company[/TD]
[TD]Generic[/TD]
[TD="align: right"]300955430[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]24569[/TD]
[TD="align: right"]5936.19[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902392[/TD]
[TD]7687804833[/TD]
[TD]Xxonel LLC[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867580[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]5854[/TD]
[TD="align: right"]4331.82[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD="align: right"]78512568902392[/TD]
[TD]76878048[/TD]
[TD]Sunshine Tailers[/TD]
[TD]Generic[/TD]
[TD="align: right"]9050867579[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]27465[/TD]
[TD="align: right"]4331.82[/TD]
[TD]Primary[/TD]
[/TR]
</tbody>[/TABLE]



DanteAmor said:
How about:

ABCDEFGHI
Xxonel LLCGenericIgnore
Lion IncSalesIgnore
Bugs be goneSalesPrimary
Geeks & LeeksGenericIgnore
Bradley's OrangesSalesPrimary
Olivia & CompanyGenericIgnore
Xxonel LLCGenericIgnore
Sunshine TailersGenericPrimary

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Account Owner Role[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Profit Amount of File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Primary File Number line TAG[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]7687804833[/TD]

[TD="align: right"]9050867611[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]76878038[/TD]

[TD="align: right"]9050867538[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]997[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]76877995[/TD]

[TD="align: right"]9050867581[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]7974[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]76878048445[/TD]

[TD="align: right"]9050867616[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]85469[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Account Owner Role[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Profit Amount of File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Primary File Number line TAG[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]71107569089307[/TD]
[TD="align: right"]1390063[/TD]

[TD="align: right"]300955233[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]5936.19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]71107569089307[/TD]
[TD="align: right"]1455039[/TD]

[TD="align: right"]300955430[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]24569[/TD]
[TD="align: right"]5936.19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Account Owner Role[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Profit Amount of File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Primary File Number line TAG[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]78512568902392[/TD]
[TD="align: right"]7687804833[/TD]

[TD="align: right"]9050867580[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]5854[/TD]
[TD="align: right"]4331.82[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]78512568902392[/TD]
[TD="align: right"]76878048[/TD]

[TD="align: right"]9050867579[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]27465[/TD]
[TD="align: right"]4331.82[/TD]

</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.
Click to expand...
 
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 :-)

DanteAmor said:
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
Xxonel LLCGenericIgnore
Lion IncSalesIgnore
Bugs be goneSalesPrimary
Geeks & LeeksGenericIgnore
Bradley's OrangesSalesPrimary
Olivia & CompanyGenericIgnore
Xxonel LLCGenericIgnore
Sunshine TailersGenericPrimary

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Customer Account Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Account Owner Role[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Invoice Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Profit Amount of File Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Primary File Number line TAG[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]7687804833[/TD]

[TD="align: right"]9050867611[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]76878038[/TD]

[TD="align: right"]9050867538[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]997[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]76877995[/TD]

[TD="align: right"]9050867581[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]7974[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]78512568902641[/TD]
[TD="align: right"]76878048445[/TD]

[TD="align: right"]9050867616[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]85469[/TD]
[TD="align: right"]8278.44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]71107569089307[/TD]
[TD="align: right"]1390063[/TD]

[TD="align: right"]300955233[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]5936.19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]71107569089307[/TD]
[TD="align: right"]1455039[/TD]

[TD="align: right"]300955430[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]24569[/TD]
[TD="align: right"]5936.19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]78512568902392[/TD]
[TD="align: right"]7687804833[/TD]

[TD="align: right"]9050867580[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]5854[/TD]
[TD="align: right"]4331.82[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]78512568902392[/TD]
[TD="align: right"]76878048[/TD]

[TD="align: right"]9050867579[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD="align: right"]27465[/TD]
[TD="align: right"]4331.82[/TD]

</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>
Click to expand...
 
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
You must log in or register to reply here.

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

Share this page

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