If value in cell C is duplicate change value in cell B

%3CLINGO-SUB%20id%3D%22lingo-sub-3245797%22%20slang%3D%22en-US%22%3EIf%20value%20in%20cell%20C%20is%20duplicate%20change%20value%20in%20cell%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3245797%22%20slang%3D%22en-US%22%3E%3CP%3EHey%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWas%20hoping%20someone%20could%20help%20me%20with%20this%20issue%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22261%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%20height%3D%2220%22%3EEquipment%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E33%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EParent%20Bundle%20ID%3A%201747374%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EEquipment%3C%2FTD%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3EParent%20Bundle%20ID%3A%201747374%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3ECharts%3C%2FTD%3E%3CTD%3E33%3C%2FTD%3E%3CTD%3EParent%20Bundle%20ID%3A%201747374%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20the%20report%20I'm%20running%20is%20showing%20each%20activity%20as%20having%20taken%2033%20minutes%20but%20the%20reality%20is%20that%20is%20how%20long%20it%20took%20the%20entirety%20of%20the%20bundle.%20I'm%20hoping%20you%20guys%20can%20help%20me%20find%20a%20way%20to%20make%20it%20so%20that%20if%20cell%20C%20has%20a%20duplicate%20(ie%3A%20part%20of%20the%20same%20bundle)%20then%20the%20values%20in%20B2%20and%20B3%20would%20change%20to%200%20to%20reflect%20it%20taking%2033%20minutes%20to%20complete%20all%20three%20tasks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3245797%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3245906%22%20slang%3D%22en-US%22%3ERe%3A%20If%20value%20in%20cell%20C%20is%20duplicate%20change%20value%20in%20cell%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3245906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1323023%22%20target%3D%22_blank%22%3E%40gcjager%3C%2FA%3E%26nbsp%3BWith%20an%20extra%20column%20you%20could%20achieve%20it%20like%20in%20the%20picture%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-03-03%20at%2016.28.06.png%22%20style%3D%22width%3A%20485px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F352798i6B082F1574E4E93B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-03-03%20at%2016.28.06.png%22%20alt%3D%22Screenshot%202022-03-03%20at%2016.28.06.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hey,

 

Was hoping someone could help me with this issue:

 

Equipment33Parent Bundle ID: 1747374
Equipment33Parent Bundle ID: 1747374
Charts33Parent Bundle ID: 1747374

 

Basically the report I'm running is showing each activity as having taken 33 minutes but the reality is that is how long it took the entirety of the bundle. I'm hoping you guys can help me find a way to make it so that if cell C has a duplicate (ie: part of the same bundle) then the values in B2 and B3 would change to 0 to reflect it taking 33 minutes to complete all three tasks.

2 Replies

@gcjager With an extra column you could achieve it like in the picture below.

Screenshot 2022-03-03 at 16.28.06.png

@gcjager 

 

Here's a simple way to do it, a way that does rely on adding a column. The formula compares each row in column B with the next, as well as each row in column D with the next....when both are the same, it returns a 0, otherwise, the original value.  See the attached spreadsheet for a working copy.

=IF(AND(B2=B3,D2=D3),0,B2)

mathetes_1-1646321331514.png

A macro/VBA routine might be able to do this task without the additional column; I prefer an approach like the one I've shown because it retains your original data just in case there's any question.