PDA

View Full Version : [SOLVED:] Use a formatting cell property to count duplicate



RIC63
10-04-2021, 06:43 AM
Hi

I need to highlight in different areas - here I have reported two for convenience each one consisting of 3 columns by 15 rows- some numbers that I insert from time to time in the range J2: M2 in order to then count the duplicates that occur.

The range J8: O22 contains conditional formatting so that cells containing numbers equal to those present in J2: M2 is assigned a background color equal to that used in the range J2: M2.

The goal is to count the sum of any duplicates - for each set of 3 columns - as reported in row 30 (there can be 2 or max 3 duplicates per value) through a function or other.

I searched throughout the db for something that would help me in that direction, without success
I appreciate every suggestion, thanks

see xlsm file attached

Paul_Hossler
10-04-2021, 09:01 AM
I'd use as user defined function (CountDups)


29033


Option Explicit

Sub drv()


MsgBox CountDups(Range("J2:M2"), Range("J8:L29"))
MsgBox CountDups(Range("J2:M2"), Range("M8:O29"))


End Sub




Function CountDups(Matches As Range, Data As Range) As Long
Dim n As Long
Dim rCell As Range, rMatch As Range

CountDups = 0

n = 0

With Data
For Each rMatch In Matches.Cells
If Application.WorksheetFunction.CountIf(Data, rMatch.Value) > 1 Then
n = n + 1
End If
Next
End With


CountDups = n
End Function

p45cal
10-04-2021, 09:39 AM
In cell K30:
=SUMPRODUCT(--(COUNTIF(J8:L22,$J$2:$M$2)>1))
copy to cell N30
Works here in O365.
Each value in J2:M2 should be different.

RIC63
10-05-2021, 12:43 AM
Morning Paul

that's exactly what I wanted


thanks again for your help

RIC63
10-05-2021, 12:44 AM
Hi p45cal

thank you too
for this version more ..smart
I just had to adapt it to my Italian installation:

http://www.vbaexpress.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAagAAAATCAYAAADFyJnCAAAJWElEQVR4Ae1c7ZEj Kwy84ByEc5gcHINTcApOcK4kJGgJwYA93vJu8eOV1yD00WpJzOze /d8Pvf138JgcWBxYHFgceDbOPDv2xxa/qwiWRxYHFgcWBwgDqwBtZ4g1xP04sDiwOLAV3JgDahFzK8k5rpBrxv04sDiwBpQa0CtAbU48Lc4 8Ljt1 0 HtOsfIsvZ kh/Wfqavn7C9abA q XfbL5bJv9zXF103ud3Hgcbvu19tjvEFBob5z9q/x5Ndicd/2y8yAmpUHvpicn6WH9E/puu/b5brfHmfUaaQrWKMBernsl ttf7TwOGG9MaACh5rGHvvt2hlmBHQjkHgIku2kj8 Zn7f9/iz2yj6td5KjPoiuaugq2GDLNrhiszpLdlV/TtasfPE9xqTsY0GobIxD8SHeF50TsRc9jYtLV1cvr6180zrktqtfMZrhrp7Rz8ZZbzfneRTD566 5srx67jQEUjPt4QMYPIuukIu DpSbFfcjflxgsMdYFH8ldqffx1f4muxFPrNOqD2U6e0V3a08WNwq331egWvn2C0YY0zZb8UO fSiT RvG3vlt/3UGP25SFe09nzGHMnxeS6 8L0zoGxy20ZTEq7XazhNqTC3bQv2UnDbpgVqwWN7nCx/MxB7cEPm4m/dmCThhSD3fUNCCEnKvjxeX7BYZ2OcldfYBzCBJFPcSK5EOM3bAE4zsQPeOpDRtq71cZQ4w7we7A 35KpcFzC/g1eaw2CYb/izbtRw0xTroF eKagSaIPnDOfPc7eEjTaFbNxpzl/uJHyZfeo4 Iywqf12D6vrdsEdnAHPD4d4e so/ky/24sS9B94AWaw78qfZne0Db/jk88W81F6g/UU/k53r7c4PF6aOW7n3 jPmtjYOa6zKm/oUf542oLY7Be2cZcJu z0KTtdUJnKc95xOeYIyoKquYR0KRt3EM8CkMzcTLa7RGGflxR No4cJxOgHVLrRaJEGsal 1hHsq 4gdoM3yY3IVHISZ5jX3t6or/J04Ru xnXwSXjaV0N1w8j8YF1zfmlDQBu2afYweIEjPZyljloDqsYi2Tf Vjy19cE68vBJWLXsZVx7Prs9o9/tZX2Q89p331sEYzjDepzucbsas8Wl6HS90dmJYshrlWxggy8z7i2Eia3F35Yuj1ck18DQ2B2XiQ eUaWTajILXMEw TUJqDtjIMiG8PnlNkWRbIOmTjAfFy6fvWPQ5iQyK7PvhSXtVkhE4BD/poOIich7HOCuf7Bbd6TzasTF5efHbxON1pO8ZJyOLcdPPdey1LyQjw3BYV Snsx3pitYy4Ud8dTbyWVz3eCk/WrdQ3ff8VJ3oF/DGxZJrBH1yMpj7OY5IziPudwdUgIX45/3lZs0XufoM780OKHPxUSzl0 1F i9hrOm89b2HzVl2k43ZvtGLIXPBYfHs5ZP5FA2qOmdJf/E72wv1R3IOO T1Cz/XAyoYJsXJyDg4aUCDAvU6XQEycTKRwYaTQ/Dw9yF18wQdAkoqJEoSNBTW32pA0IAxOUMxfhATSDLFhLHHBVsuFiibhvN47OYs wD4DOMoeQnz2tkb1g8 AU59/ipXgrMm1yoHn8N wYDS3yEJ323T7GBA8TjcmnXjYg 5L5zGOipvDAIsRGfkb9Yf1XD2BWoirwGWvJZkaq6RXG v6GH/9PdZ7kk68r0nj/0m9unILsRsuET4Sh/yvVFfozZi6Pnk 0HFe NDH9NIV7RGNlrrlf1m3guO/kw9oEhJAJo/WL5DEuSRkh/jUQf rAlAMrvCy7rDdUfUCvR2sKQ3EVLIEerX80AiHFBDMX4QE0gyEcM0GMTUF7THaTL2ukChiQ3rEmx 78tFetJZxwDyBT7JvGpC8so3W7OtR8dNjlm0OxGGeQn0RJz8JU/bFNVI/iLQeWBZz3MVFeVw U9x6QUOOFplkq8bR JD9tXJWv9fZs5dkmc8YH Dd21Pf7GfyLb8x0NrPvnv/annS955djBmwwn6IP0O8KZY5n8jXuk71YjPzBOX5WvLj9TdtVrF4vMe xwOKiwtu11yorhnShGcyYRLKL32N4yYJSd40Vrkt ODjQk3ni6z/fhQ4 guk8YByzIoBnhmPUd 3axM6BRPw0 iD9URuj4v/Phd7wVvwNfiM6pKzvcYa7o3q78hV HieRGejNTzX2Tf4BAUv 7f8V3ygN8Qg5W obpqxIo/xZ7DNZ9txKZeJY/yzGSieY6i3Z68/CIjn1R vNGMsNr1/6HuqkSIbxfO XRuz2jd1SzwwGL7uk9ELeSxPxlZ376m01hVwmC/BeuHxus/5PjagumSwSUhDxU1rTAIXZh0UJy//UYIE1ylU0zCl2NOfmid/9OZEemvZYj/ZLd ZuGwXbyOTMZonrsYNZhoTGxf5GZGoFF6Sr2PXoeueJjXHjdiNnkpmVFcvr/29mTwZXzWuw88AL23C jpGdCCnxvyKciX5pMuZv9VHvB qG8sR9JN5YXQkWb1EFd5QHmIslHPZX1N3heeqk/hZmm/LnvgcNunenhuQ5IvBUc7CGvuj37vyfbsmrgE9isdQb1T/mGsYQ98nzVm25XMT8j/prOslrWdd4Itdo4uM652hHantF/bOH1BCbkMWAksIaEhiHA5ubVGhhsWDZzGppdjKzTMAlJOJT4hexifM2/CvRefkxzCpbdK5mlxKhiRv9xEnkRuMveBHOHl8RnWJXJjXgb1DX Umbopc9BquxWutPKQhBPzwzXTQL5uL0tBNrZCfAT4t3 yrSc8R a6/zzB583spPvXR2ku8SRwoF5w8sLJ y0fWkbGK7F33203 nSToYDuUwwpXyYHkl/PS089yLd8Df5Q3B3YP41I9/qJ60Bt1yJhaU10HPtk/bIr5XS4hiEnhteY 1kVnfN1Ha0e25/Y7A8o7M6e4gLHOLSx kANUyLlpTdp95 zAAPxVPPh6LNKAsTd6uShqUx/JCcU5I9/SeZYe0v KLjrj30C1fD1aj3RFa 73rJ/gd2NAyU20mpiTBX8ExNp/6X/H8wki/B2d79zq3jn712rjy7FoNPHqteZBj5mVb9XJWXpI/yu67BPve1yMdFVr/JSPr3Dfs9nCtTmgWgfW mcSsXA9D9dXClzxf es6vgrnwuL8zj5WU6ceZmIdEVrP4PNGlAHN6zPEutnkrxiWDgvDiwO/EYO/AcHXF1GRZdWyQAAAABJRU5ErkJggg==


Thanks again
a good day