Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating row with no duplicate matching amount
I have a spread sheet with 4 columns and 900 rows I am trying to identify the
rows that do not contain a duplicate amount. For example I have rows with 100 100 97 97 96 96 98 101 I am trying to pull the list of 98 and 101. The spreadsheet is a list of invoices paid vs invoices unpaid if it is paid there are two rows with the same data if it is unpaid there is only one row. I am trying to find the unpaid invoices. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating row with no duplicate matching amount
Here is one way...
Assuming your invoice numbers are in column A, in the first free column enter =COUNTIF(A:A,A2) Copy it down through all rows of data. This formula counts the number of times each invoice appears in column A. Now apply an autofilter to the worksheet. Set it to only show records with a value of 1 in the column you just added. The resulting visible rows are your unmatched invoices. To copy that list to another sheet, highlight all the filtered data. Press F5 (Go to) and select Special Visible cells only OK. Copy & paste to another sheet. Hope this helps, Hutch "Waylonk" wrote: I have a spread sheet with 4 columns and 900 rows I am trying to identify the rows that do not contain a duplicate amount. For example I have rows with 100 100 97 97 96 96 98 101 I am trying to pull the list of 98 and 101. The spreadsheet is a list of invoices paid vs invoices unpaid if it is paid there are two rows with the same data if it is unpaid there is only one row. I am trying to find the unpaid invoices. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
locating and deleting duplicate cell entries | Excel Discussion (Misc queries) | |||
Matching a name to a specified monetary amount | Excel Worksheet Functions | |||
Matching a name to a specified monetary amount | Excel Worksheet Functions | |||
Locating duplicate entries in Excel | Excel Discussion (Misc queries) | |||
Matching the same amount of records in one value to another column | Excel Worksheet Functions |