Combining several duplicate rows, summing one column, and removing original duplicates

Hi. There are several threads on this forum with similar keywords (e.g., this one) but they do not have exactly what I need.

I am looking to simply de-dupe the rows that are repeated -- duplicates being recognized based on two columns and then if the rows are duplicate, folding them into one row but with "Qty" column being incremented.

Attached is a simple example. In this file:

1. Duplicates are recognized by two columns: Title (C) + Images (E). Currently there may be duplicate rows with exactly the same value across these two columns, each row having Quantity (column M) as 1.

2. I want to merge these separate rows into unique rows, and correspondingly increment the Qty column based on how many original duplicate rows existed.

3. These de-duped rows should be in a new worksheet.

Just FYI, I am on Excel on Mac 2011 -- latest version 14.2.3, which can run exactly the same macros and VB functions as Windows. Would appreciate any pointers or function writing help.

Thanks!

Try this formula method,

In Desired sheet!C2,

=IFERROR(INDEX(Current!C$2:C$17,MATCH(TRUE,INDEX(ISNA(MATCH(Current!C$2:C$17,C$1:C1,0)),0),0)),"")

then copy down.

In A2,

=IF($C2="","",INDEX(Current!A:A,MATCH($C2,Current!$C:$C,0)))

then copy down & across in other columns.

In M2, Qty olumn,

=IF($C2="","",COUNTIF(Current!C:C,C2))

then copy down.

How you are getting data pkiula? Importing from a Database, website or from some where else?
Is there any blank rows in original sheet (Current)?

If actual data doesn't have any blank rows, a quick question..

=IFERROR(INDEX(Current!C$2:C$17,MATCH(TRUE,INDEX(ISNA(MATCH(Current!C$2:C$17,C$1:C1,0)),0),0)),"")

Did you adjust the ranges in the red highlighted part? If you have 3000 rows, it should be like C$2:C$3002

You can use Dynamic named range, here is a link,

http://blog.contextures.com/archives...dynamic-range/

You can Google it for more