Getting a row where I have duplicate sales order number

  • I am not sure how to use the row function or the index function.


    what I need is to find out if a cell contains a duplicate entry.


    I am just guessing, but I would like the cell address of any duplicates so I can delete them from the spreadsheet.


    does anyone have a formula for this?


    This is what I started - which, naturally doesn't work.
    {=IF(H43:H153 = H2, ROW(H43:H153),"")}

  • not sure how you'd formulate multiple duplications...that would be very tricky, but the following simulation, copied down, captures any duplicates by giving a "TRUE" value


    <CENTER&gt;<TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%&gt;<TR&gt;<TD COLSPAN=6 BGCOLOR=#0C266B &gt;<TABLE ALIGN=CENTER BORDER=0 WIDTH=100%&gt;<TR&gt;<TD ALIGN=LEFT&gt;<FONT COLOR=WHITE&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD ALIGN=RIGHT&gt;<FONT COLOR=WHITE SIZE=2&gt;___Running: xl97 : OS = Windows (32-bit) 4.90</FONT&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=#D4D0C8 COLSPAN=6&gt;<TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD ALIGN=RIGHT VALIGN=MIDDLE&gt;<FORM NAME='formCb2339942116'&gt;<INPUT TYPE='Button' NAME='btCb9273296640' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb4473621504.sltNb5698059465.value);'&gt;</FORM&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=WHITE COLSPAN=6&gt;<TABLE BORDER=0&gt;<TR&gt;<Form name='formFb4473621504'&gt;<TD WIDTH=5% ALIGN=CENTER BGCOLOR=White&gt;<SELECT NAME='sltNb5698059465' onChange='document.formFb4473621504.txbFb5434202903.value = document.formFb4473621504.sltNb5698059465.value'&gt;<option value='=COUNTIF($A$1:A1,A1)&gt;1'&gt;B1<option value='=COUNTIF($A$1:A2,A2)&gt;1'&gt;B2<option value='=COUNTIF($A$1:A3,A3)&gt;1'&gt;B3<option value='=COUNTIF($A$1:A4,A4)&gt;1'&gt;B4<option value='=COUNTIF($A$1:A5,A5)&gt;1'&gt;B5<option value='=COUNTIF($A$1:A6,A6)&gt;1'&gt;B6<option value='=COUNTIF($A$1:A7,A7)&gt;1'&gt;B7<option value='=COUNTIF($A$1:A8,A8)&gt;1'&gt;B8<option value='=COUNTIF($A$1:A9,A9)&gt;1'&gt;B9<option value='=COUNTIF($A$1:A10,A10)&gt;1'&gt;B10<option value='=COUNTIF($A$1:A11,A11)&gt;1'&gt;B11<option value='=COUNTIF($A$1:A12,A12)&gt;1'&gt;B12<option value='=COUNTIF($A$1:A13,A13)&gt;1'&gt;B13<option value='=COUNTIF($A$1:A14,A14)&gt;1'&gt;B14</select&gt;</TD&gt;<TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 &gt;<B>=</B&gt;</TD&gt;<TD ALIGN=LEFT BGCOLOR=White&gt;<input type='text' name='txbFb5434202903' size='120' value='=COUNTIF($A$1:A1,A1)&gt;1'&gt;</TD&gt;</form&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;E</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A1,A1)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A2,A2)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A3,A3)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A4,A4)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;f</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A5,A5)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;wer</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A6,A6)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;sd</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A7,A7)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;8</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;er</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A8,A8)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;9</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;3rt6</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A9,A9)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;10</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;her</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A10,A10)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;11</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;jr</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A11,A11)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;12</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;tuk</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A12,A12)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;13</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;587l</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A13,A13)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;FALSE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;14</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=COUNTIF($A$1:A14,A14)%3E1')&gt;<FONT FACE=Arial COLOR=#000000&gt;TRUE</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD COLSPAN=6&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TABLE&gt;<BR&gt;<FONT COLOR=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT COLOR=RED SIZE=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT&gt;<BR&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;The above image was automatically generated by [HtmlMaker [email protected]]</FONT&gt;<FONT COLOR=#339966 SIZE=1&gt;If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]&gt;Ivan F Moala</A&gt; All credit to <A HREF=mailto:[email protected]&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

  • Thanks Chris, this does help, though not exactly what I need.


    I still have to scroll down the worksheet to find out where I have duplicates.


    I may have the same Sales Order number in different months.


    My list is getting longer monthly, I need something to verify that they are all original orders, not duplicates.


    Thanks for your help.

  • how does the info get into the column ?


    if it's typed in, why not trap the errors at scource using data validation ?


    this will NOT ALLOW duplicates :


    highlight the column
    data
    validation
    custom
    formula is


    =countif(A:A,A1)<2


    anyone who tries to enter a duplicate value will have it rejected

  • Here is the attachment.


    In column H, I have Sales orders.


    The way that the Sales orders work is that an order after it has been initially "booked" can have a "change order". This means that something is either added to the original value of the order or the value is decreased (called a "debooking").


    Anyway, the same order appears in more than one month (Nov, Dec, Jan).


    Column J is where I would like the cell address for the duplicates.


    Thanks for you help.

  • n column H, I have Sales orders.


    The way that the Sales orders work is that an order after it has been initially "booked" can have a "change order". This means that something is either added to the original value of the order or the value is decreased (called a "debooking").


    Anyway, the same order appears in more than one month (Nov, Dec, Jan).


    Column J is where I would like the cell address for the duplicates

  • Bearcub,


    Sorry for not getting back so soon I had a slight case of the flu. After reveiwing what you said and looking at your workbook Probably one of the best ways to analize your data is to use Auto Filter. Chris has already provided you with a way to count duplicates. So the next step is to filter all of the duplicates. This way you can make the neseccary changes for your workbook. I took the process of finding the cell addresses with a formula that I constructed but it defeates the purpose when you already have a formula that Chris has given. Auto Filter!!!:lol:

  • The purpose of this report is to let the Sales person know how many orders he/she has been given credit for during the year.


    This is a new report that I created for the company and am modifying it as I go along.


    I have a YTD sales number from another source that I need to balance to--which is why I need to be aware of the duplicates.


    I do it for all our salesman so I am trying to streamline the process.


    Thank you all for your input.


    I was going through John Walkenbach's Formulas book and found this formula for find an address:


    Address(Row(Data),Column(Data)+ Match(Target,Data,0)-1).


    I have attached the workbook.


    Do you think this will give me the cell address where I have the duplicates?

  • Bearcub,


    John's formula works off of a target and from the target it gives you the cell address, which means that if your looking for Mike then you must put Mike in a cell (The Target) and the references will refer to just (The Target) Mike. If I read right you are looking to find all of the duplicate values and get the cell addresses for all of them or just the duplicates! By all of them I mean that if you have Mike in cell A1 and Mike in cell A14, You will see cell the cell address A1 and A14. By just the duplicate you will see cell A14. Which approach are you trying to take!:lol:

  • Trying to find the duplicate which means that my search range will be exclusive of the original.


    How would I use this approach in my file?


    What would I substituate for Data and Target. would target be the source cell and the Data the search range?