0

I am trying to get a row by row count of unique invoices in a spreadsheet. I want excel to do this by reading either 1 for unique or zero for duplicate. I have had success with =IF(COUNTIF($C$3:C3,C3)>1,0,1).

This has given me an accurate count based on one specific column, but I have not had any luck advancing this beyond the one column. I would like this formula to be based on three criteria, not just two. 

  A  B C  D  E  F  G
  Vendor ID Name 1 Invoice Number Inv Date Sum Amount Acctg Date Unique#
  00001  A 0000001  3/16/2015 5.00  5/11/2016 1
  00010  M 0000001  9/14/2015 10.00  5/24/2016 1 
  00010  M 0000001  9/4/2015 15.00  5/24/2016 0
  00005  K 0000285  4/8/2016 20.00  4/18/2016 1
  000106 O 000042  6/7/2016 30.00  6/21/2016 1
  000107 H 006333  4/5/2016 6.00  4/11/2016 1
  000107 H 006333  4/5/2016 6.00  4/12/2016 1​

There are duplicates in all the columns because of how I needed to pull the report. I would like a pull down formula that would give me unique values of A, C, F in a 1,0 format on each row line by comparing each of them against a total combination of each of three columns. Please note vendor M having a duplicate invoice number vs vendor H which has two distinct invoices based on the criteria.

This will be a large drain on resources because of the size of the data. I am looking at around 20-90k lines, but maybe someone can show me a better mousetrap? VBA macro? Match Index? Anyway, onwards to the failures!

Please feel free to explain why they didn't work, or how they could. Also please ignore column locations compared to my example as I was moving things around quite frequently.

=A&C&F then use If(countif('ColumnX')), but this didn't work correctly as I found data that was listed as a repeat when it was actually unique. I think the root problem with doing this was combining the date and general formats into one cell. 

=SUMPRODUCT((1/COUNTIFS(E3:E1000,E3:E1000,J3:J1000,J3:J1000,G3:G1000,G3:G1000)))

Multiple versions of AND with IF(CountIF)

Multiple versions of =A&C AND CountIF (Date)

I have also looked at the following questions in SE and found them helpful, but ultimately not what I specifically needed, or I failed at implementation.

Simple Pivot Table to Count Unique Values I tried this unsuccessfully based on unique invoices, need three criteria not just one.

Count unique values in Excel See above.

Excel Formula: Count Unique Values in a Row Based on Corresponding Value in Another Row This looks like it should work, but I tried and failed to correctly adapt to my problem.

Excel - Return Count of Unique Values Based on Two Columns This also should work perfectly with addition of third column. Formula yelled at me and called me names. Mentioned something about can't fix stupid.

Please let me know if any parts of the question are unclear. I did my best to not duplicate and trim the information down. Thanks in advance!

Community
  • 1
  • 1
Jester799
  • 3
  • 1
  • 5
  • 2
    Perhaps adding a few expected results to your table would help clarify what you're seeking here. – XOR LX Aug 26 '16 at 16:53
  • 1) Do you have many rows? As a newbie+ I might think of something in VBA with nested loops, but it will be slow and resources-consuming. For a few rows (100) might work, but not for big tables. 2) Also, as @XORLX said, do you want to sum up the sum amounts of each combination? – CMArg Aug 26 '16 at 17:04
  • As @XORLX implied, without examples of your desired results, I cannot help you. – Ron Rosenfeld Aug 26 '16 at 17:24
  • "I tried this unsuccessfully based on unique invoices, need three criteria not just one." Pivot tables can handle three criteria. – CMArg Aug 26 '16 at 17:59
  • @XOR LX and Ron Rosenfeld please see edited example along with edited text for clarity. – Jester799 Aug 26 '16 at 18:11
  • What are you going to do with those results? Could your purpose be served merely be using the `RemoveDuplicates` method on the `Data` tab? – Ron Rosenfeld Aug 26 '16 at 18:18
  • @CMArg See edited text for clarity. I want the ones and zeroes so that I can use that in the pivot table for the total number of invoices broken down by X criteria. So in answer to your question, No I do not want the sum amounts for each combination. I want excel to read 1 for unique values and zeroes for duplicates and go row by row. – Jester799 Aug 26 '16 at 18:18
  • @CMArg I got the pivot table to work for the unique items in the Invoice column, but how do you combine the other two criteria? – Jester799 Aug 26 '16 at 18:24
  • @ Ron Rosenfeld Sadly no I can not use remove duplicates. That would have been a lot easier. – Jester799 Aug 26 '16 at 18:57
  • @Jester799 in office365, drop Vendor ID, Invoice Number and Acctg Date in the Rows mini-window, and any other on Values one. Select Count. Anyway, John Bustos code is doing what you asked for. – CMArg Aug 26 '16 at 19:05

1 Answers1

0

If I am understanding your problem correctly, basically you want column G to check if the current row is a duplicate (based on columns A, C and F) of any rows above it. If it is, return a 0, else return a 1.

If that is what you are looking to achieve, you can do so using the COUNTIFS() function to know if there are any duplicates above the row and then simply check if the count = 0 or is > 0 (=0 means it's unique, >0 means it is a duplicate).

Your formula for column G would look as follows:

G2: 1 (obviously we know it is unique since there are no values above it to be a duplicate of)
G3: =IF(COUNTIFS($A$2:A2,A3,$C$2:C2,C3,$F$2:F2,F3)=0,1,0)

then, drag G3 downwards.

Hope this is what you were looking for.

John Bustos
  • 19,036
  • 17
  • 89
  • 151