CODEWITHSUNDEEP
excelduplicatesexcel-formulamultiple-columns
user2971917
user2971917

Reputation: 25

Excel formula in case of multi-column duplicates, finding them and changing values in results

Well, I'm not an advanced user, but I will try to be specific.
Does anyone know how to do this: I have some values in column A and some values in column B. If the values in A,B of two rows are duplicates, I need as result in column C: value of column B +1 for each row that is duplicate.

Let me explain in an example.

COLUMN A ---- COLUMN B ---- COLUMN C (preferred result)
PD3221 ----------2012-01-04 -------- N/A ----- (because A1,B1 and A2,B2 aren't duplicates)
PD3221 ----------2012-04-25 -------- N/A ----- (because A1,B1 and A2,B2 aren't duplicates)
PD3484 ----------2010-04-27 ---- 2010-04-28 (because A3,B3 and A4,B4 are duplicates)
PD3484 ----------2010-04-27 ---- 2010-04-29 (because A3,B3 and A4,B4 are duplicates)
PD3484 ----------2010-05-27----- 2010-05-28 (because A5,B5;A6,B6;A7,B7;A8,B8 are duplicates)
PD3484 ----------2010-05-27----- 2010-05-29 (because A5,B5;A6,B6;A7,B7;A8,B8 are duplicates)
PD3484 ----------2010-05-27----- 2010-05-30 (because A5,B5;A6,B6;A7,B7;A8,B8 are duplicates)
PD3484 ----------2010-05-27----- 2010-05-31 (because A5,B5;A6,B6;A7,B7;A8,B8 are duplicates)
PD4880 ----------2010-04-27----- 2010-04-28 (because A9,B9 and A10,B10 are duplicates)
PD4880 ----------2010-04-27----- 2010-04-29 (because A9,B9 and A10,B10 are duplicates)

Okay, if anyone has any idea, or needs more explanation, please answer, I have over 13,000 rows to arrange like this. Thanks a lot!

Upvotes: 0

Views: 3614

Answers (1)

barry houdini
barry houdini

Reputation: 46401

You can achieve that by using this formula in C2

=IF(COUNTIFS(A:A,A2,B:B,B2)=1,"N/A",COUNTIFS(A2:A$2,A2,B2:B$2,B2)+B2)

format C2 in required date format and fill down the column

To fill down put cursor on bottom right of C2 until you see a black "+" sign - that's the "fill handle" - double click left mouse button and the formula will populate the whole column as far as you have continuous data in the adjacent column

The formula works because the first COUNTIFS function counts rows where col A and col B are the same as the current row - if there is only 1 (the current row itself) then the formula returns "N/A", otherwise there must be duplicates so the second COUNTIFS function counts instances so far and adds that number to the date, thus incrementing the date for each duplicate as required

Upvotes: 2

Related Questions