How to count unique or the last occurrence of duplicate values based on another column?

Question

I've attached an example below. I have a large sheet similar to Raw Data table from the image. My goal is to count unique occurrences based on the first column (ID) of the Results table.

In the Raw Data table, the ID consists of the main ID and its sub ID. I want to use the main ID from the Results table as a reference to essentially count how many occurrences of that main ID there are from that Raw Data table.

However, there are some duplicate IDs in the Raw Data table. I only want to count the ones in the green (i.e. the last occurrence) and not count the first occurrence in red. In this example, there are only 2 duplicates, but there might be 3 duplicates as well.

Lastly, I want to add the Price from the Raw Data table with the same main ID as shown in the Total column of the Results table.

Example Data

score 2 · Answer 1 · answered Jun 17 '20 at 22:11

use:

=ARRAYFORMULA(QUERY(REGEXREPLACE(""&SORTN(ARRAY_CONSTRAIN(SORT({A3:B, 
 ROW(A3:A)}, 1, 1, 3, 0), 99^99, 2), 99^99, 2, 1, 0), "-\d+", )*1, 
 "select Col1,count(Col1),sum(Col2) 
  where Col1 <> 0 
  group by Col1 
  label Col1'ID', count(Col1)'Count',sum(Col2)'Total'"))

score 0 · Answer 2 · answered Jun 17 '20 at 22:08

Sorry, the question has been formulated rather unclearly to me so I am not exactly sure what you want to achieve, but the main thing as far as I understand would be getting the last value of the table for a set of duplicates.

One of the approaches to this would be reading your table upside down to enable usage of Vlookup for it (because Vlookup searches for upper values), and then apply the Vlookup on the results. It can all be done in one formula, but to help you modify it easier, I've split it into different stages.

I created this sample spreadsheet to show the process. Hope you can use it for further adjustments.

Also, for future questions, I'd highlight that posting data as text is better than posting it as an image because it is super tedious to type it if someone wants to do anything with it to help you. I'll also post it as an image though for easier preview of the final result:

score 0 · Answer 3 · answered Jun 18 '20 at 13:46

Try this:

=QUERY(
  FILTER(
    {REGEXEXTRACT(A3:A, "^0*(\d+)-"), B3:B},
    MATCH(
      ROW(A3:A),
      QUERY(
        FILTER({A3:A, ROW(A3:A)}, A3:A <> ""),
        "SELECT MAX(Col2)
         GROUP BY Col1
         LABEL MAX(Col2) ''",
        0
      ),
      0
    )
  ),
  "SELECT Col1, COUNT(Col1), SUM(Col2)
   GROUP BY Col1
   ORDER BY Col1
   LABEL Col1 'ID', COUNT(Col1) 'Count', SUM(Col2) 'Total'",
  0
)

How to count unique or the last occurrence of duplicate values based on another column?

3 Answers3