Find / Search Duplicate Values in slash or (-) character Data in one cell

[mianmithu]

I want to find a value among the multiple values in one cell, I hope the given picture will help understanding the actual issue.

Hope to get some solution from some Mr. Excel

P.S :- Please i need some formula instead of VB Code

 

[Snakehips]

For what it's worth, I've just done the below. Just doing the voucher dupes in M since formula will do GP ?

Book1
	C	D	E							L	M
1	Voucher	Gate Pass	Sale							GP Dupes	Voucher Dupes
2	141	9	2500
3	142-143-144	10	2500
4	145-146-147-148	11	600
5	149-150	12	4000
6	151	13	15000
7	152	11	1000							11
8	153	18	9000
9	999-888-141-666-555-444-333-222-111-123-234-456-567-789-926-537-882-663-145-154-156	19	50000								141 145
10	147	20	1800								147
11	141-155	18	170							18	141
12
13
14
15
March

Cell Formulas
Range	Formula
L3:L15	L3	=IF(COUNTIF(D$2:D2,D3)>=1,D3,"")
M3:M15	M3	=VDupes(C3)

Function VDupes(Vcell As Range) As String
Dim Arry As Variant
Dim Crng As Range
Dim rw As Long

rw = Vcell.Row
Set Crng = Range("C2:C" & rw - 1)
Arry = Split(Vcell, "-")
For i = LBound(Arry) To UBound(Arry)
    For Each c In Crng.Cells
        If c Like "*" & Arry(i) & "*" Then
            VDupes = VDupes & Arry(i) & "  "
        End If
    Next
Next i
Debug.Print "Y"
End Function

 

[Akuini]

Nice & simple code, Snakehips

But I hope all the voucher has the same digit otherwise it won't work correctly. For example, say C6 = 14, then it will be considered duplicate.

 

[Snakehips]

** ^^^^If by any chance you test the above then please delete the last line

 Debug.Print "Y

". as it is irrelevant.

 

[Snakehips]

But I hope all the voucher has the same digit otherwise it won't work correctly. For example, say C6 = 14, then it will be considered duplicate.

@Akuini Good spot!

 

[Snakehips]

Can be resolved as below....

Book1
	B	C	D	E							L	M
1	Remaining Stock	Voucher	Gate Pass	Sale							GP Dupes	Voucher Dupes
2	32,675	141	9	2500
3	4,33,882	142-143-144	10	2500
4	99	145-146-147-148	11	600
5	960	149-150	12	4000
6	1,277	151	13	15000
7	5,145	152	11	1000							11
8	3,797	153	18	9000
9	18,604	999-888-141-666-555-444-333-222-111-123-234-456-567-789-926-537-882-663-145-154-156	19	50000								141 145
10	323	147	20	1800								147
11	12,000	141-155-14-1	18	170							18	141 141
12	605	1										1
13	68,906
March

Cell Formulas
Range	Formula
L3:L13	L3	=IF(COUNTIF(D$2:D2,D3)>=1,D3,"")
M3:M13	M3	=VDupes(C3)

Function VDupes(Vcell As Range) As String
Dim Arry As Variant
Dim Crng As Range
Dim rw As Long

rw = Vcell.Row
Set Crng = Range("C2:C" & rw - 1)
Arry = Split(Vcell, "-")
For i = LBound(Arry) To UBound(Arry)
    For Each c In Crng.Cells
        If "-" & c & "-" Like "*-" & Arry(i) & "-*" Then
            VDupes = VDupes & Arry(i) & "  "
        End If
    Next
Next i

End Function

 

[mianmithu]

I don't know your workbook so I can't guarantee it. So I think you can try it to see how it works.

1. In case If i dnt feel comfortable with vba
Can i remove the module and come back to simple xls file as it was earlier?
2. Changing this file's extension and recording macro... will it affect other workbooks?

Is there a possible way to send my workbook so you can guess if there could be an issue or not

 

[Snakehips]

Dare I say, do not be afraid? Using one or the other of the above UDFs may be the answer to your prayers?
I'm assuming that your current workbook is an independent, stand-alone, workbook? If it is not in, any way, linked to or, from any other workbook then I cannot envisage a change of extension/type having any adverse affect whatsoever.
Surely, the sensible thing would be for you to create a copy of your workbook, saving it as .xlsm and test it?
If all goes wrong, ?, you can forget it and revert back to the original.

You make reference to your being 'heavy' by which I assume you mean resource hungry and slow ?
The introduction of the small amount of UDF will not impact greatly upon that, other than when the UDF related formulas are calculating. I get the impression that you just need to copy down these formulas, once a month, to help you analyse a new set of data? If after that, the voucher data will not change so there will not be a need for the UDF formulas to need to recalculate, you could if you so wish, get rid of the formulas and retain the values, you could Copy > Paste Special >Values on columns L and/or M. ?

Only you will know! Have a play on a copy workbook.

 

[mianmithu]

You make reference to your being 'heavy' by which I assume you mean resource hungry and slow ?

I am sorry i was away and its pretty late replying to you.
Being honest let me say, I actually don't use anything until or unless i have a grip on it. Your provided VBA might be the perfect one but in future, if incase i needed to amend or change i ll be in trouble once again. with Formulas i am much more familiar so i wish to have hints regarding it.

Yes, I ended up for the time being with it,
in Helper Column

=MID($C356,COLUMN(A$1)*4-3,3) ( and Dragged horizontal until )
and in the ISSUES Column

=IF(AND((COUNTIFS(N$8:S355,N356,N$8:S355,">""")+COUNTIFS(N$8:S355,O356,N$8:S355,">""")+COUNTIFS(N$8:S355,P356,N$8:S355,">""")+COUNTIFS(N$8:S355,Q356,N$8:S355,">""")+COUNTIFS(N$8:S355,R356,N$8:S355,">""")+COUNTIFS(N$8:S355,S356,N$8:S355,">""")>0),COUNTIF(D$8:D355,D356)>=1),IF(A356="AllowGPV","","D-GPV"),IF(COUNTIF(D$8:D355,D356)>=1,IF(A356="AllowGP","","Dup-GP"),IF((COUNTIFS(N$8:S355,N356,N$8:S355,">""")+COUNTIFS(N$8:S355,O356,N$8:S355,">""")+COUNTIFS(N$8:S355,P356,N$8:S355,">""")+COUNTIFS(N$8:S355,Q356,N$8:S355,">""")>0),IF(A356="AllowV","","Dup-V"),"")))

its working just if i will put vouchers in 3 digits, just want more help regarding helper column function if voucher number is put in 4 digits or 2 digits pls guide