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

 

[mianmithu]

I want to check If the Gatepass is duplicate, or if the Voucher is Duplicate, or if the Gatepass and Voucher both are Duplicates

 

[Snakehips]

@mianmithu
The below may help with the Gate Pass duplicates.

Book1
	D	E	F	G	H	I	J	K	L
1	Gate Pass	Sale	Return	Production	Prev		Quality	Date	GP Issue
2	9	2500	20	34655	500		دھنک "45 - 54x52	01/01/2020
3	10	2500		436182	200		سوس لان 100/2	01/01/2020
4	11	600	2	397	300		اسٹیپل "44 - 46x44	01/01/2020
5	12	4000		4160	800		کھدر "46 - 69x26	01/01/2020
6	13	15000		15377	900		کاٹن "61 - 96x88	01/01/2020
7	11	1000		5045	1,100		کاٹن "47 - 80x64	01/01/2020	DUP-GP
8	18	9000		10297	2,500		46" - 80x80/80x80	01/01/2020
9	19	50000	400	65604	2,600		بوسکی	01/01/2020
10	20	1800		2023	100		سوک لان "50 105x105	01/01/2020
11	18	170		12120	50		کاٹن "61 90x70	01/01/2020	DUP-GP
12			60	540	5		کاٹن "49 90x70	01/01/2020
13				68888	18		کاٹن52 "47 78x78	01/01/2020
14
March

Cell Formulas
Range	Formula
L2:L14	L2	=IF(IFERROR(AND(COUNTIF($D$1:$D$2000,D2)>1,ROW(D2)<>MATCH(D2,$D$1:$D$200,0)),FALSE),"DUP-GP","")

The Voucher checking is nowhere near as straight forward. I will have a look and see if I can come up with something.
I think you may need to have seperate columns to check each since, any single formula will likely get big and ugly.

Question: Might you have multiple duplicates of the same VC ?

 

[mianmithu]

Sir, I dnt want to check the whole column but till the row i am working on
As just for the Gatepass, I used =IF(COUNTIF(D$2:D2,D3)>=1,"Dup-GP","") in L3 and copied it toward down and its ok.
The Same way i want to check if there is any duplicate voucher being put twice

Ans: Sir, unit is sending me reports on daily basis and my responsibility is to check every activity if workers of unit are not doing any fraud with the organization
So in case they send me the same voucher number with tempered date in hundreds of documents and i put the voucher number without noticing that same voucher number was provided already... Then how could i find the fraud activity. So i am trying to use function to find duplicates

A	B	C	D	E	F	G	H	I	J	K	L
Remarks	Remaining Stock	Voucher	Gate Pass	Sale	Return	Production	Prev		Quality	Date	Issues
	32,675	141	9	2500	20	34655	500		Fabric 1	01-01
	4,33,882	142-143-144	10	2500		436182	200		Fabric 2	01-01	=IF(COUNTIF(D$2:D2,D3)>=1,"Dup-GP","")
	99	145-146-147-148	11	600	2	397	300		Fabric 3	01-01
	960	149-150	12	4000		4160	800		Fabric 4	01-01
	1,277	151	13	15000		15377	900		Fabric 5	01-01
	5,145	152	11	1000		5045	1,100		Fabric 6	01-01	Dup-GP
	3,797	153	18	9000		10297	2,500		Polyster 1	01-01
	18,604	145-154-156	19	50000	400	65604	2,600		Polyster 2	01-01	Dup-V
	323	147	20	1800		2023	100		Polyster 3	01-01	Dup-V
	12,000	141-155	18	170		12120	50		Polyster 4	01-01	D-GPV
	605				60	540	5		Polyster 5	01-01
	68,906					68888	18		Polyster 6	01-01

 

[Snakehips]

I'm struggling to do Voucher without use of helper columns.
Helpers can be hidden but you would need as many helpers as the max number of vouchers per cell.
The below has 4. The formula in N2 will drag across and down as required.

Book1
	B	C	D	E							L	M	N	O	P	Q
1	Remaining Stock	Voucher	Gate Pass	Sale							GP Issue	Voucher Issue	Helper1	Helper2	Helper3	Helper4
2	32,675	141	9	2500									141
3	4,33,882	142-143-144	10	2500									142	143	144
4	99	145-146-147-148	11	600									145	146	147	148
5	960	149-150	12	4000									149	150
6	1,277	151	13	15000									151
7	5,145	152	11	1000							Dup-GP		152
8	3,797	153	18	9000									153
9	18,604	145-154-156	19	50000								Dup-V	145	154	156
10	323	147	20	1800								Dup-V	147
11	12,000	141-155	18	170							Dup-GP	Dup-V	141	155
12	605
13	68,906
March

Cell Formulas
Range	Formula
N2:Q2, N3:Q13	N2	=TRIM(MID(SUBSTITUTE($C2,"-",REPT(" ",100)),(100*(COLUMNS($N:N)-1))+1,100))
L3:L13	L3	=IF(COUNTIF(D$2:D2,D3)>=1,"Dup-GP","")
M3:M13	M3	=IF((COUNTIFS(N$2:Q2,N3,N$2:Q2,">""")+COUNTIFS(N$2:Q2,O3,N$2:Q2,">""")+COUNTIFS(N$2:Q2,P3,N$2:Q2,">""")+COUNTIFS(N$2:Q2,Q3,N$2:Q2,">"""))>0,"Dup-V","")

Hope that helps.

 

[mianmithu]

This is a very good idea until the voucher numbers are not too many in the same cell. Its perfectly working for the time being i managed for the maximum 6 vouchers per cell. Thanks a bunch Sir... Salute to you Have a very great day with a lot of best wishes

I'm struggling to do Voucher without use of helper columns.
Helpers can be hidden but you would need as many helpers as the max number of vouchers per cell.
The below has 4. The formula in N2 will drag across and down as required.

Book1
	B	C	D	E							L	M	N	O	P	Q
1	Remaining Stock	Voucher	Gate Pass	Sale							GP Issue	Voucher Issue	Helper1	Helper2	Helper3	Helper4
2	32,675	141	9	2500									141
3	4,33,882	142-143-144	10	2500									142	143	144
4	99	145-146-147-148	11	600									145	146	147	148
5	960	149-150	12	4000									149	150
6	1,277	151	13	15000									151
7	5,145	152	11	1000							Dup-GP		152
8	3,797	153	18	9000									153
9	18,604	145-154-156	19	50000								Dup-V	145	154	156
10	323	147	20	1800								Dup-V	147
11	12,000	141-155	18	170							Dup-GP	Dup-V	141	155
12	605
13	68,906
March

Cell Formulas
Range	Formula
N2:Q2, N3:Q13	N2	=TRIM(MID(SUBSTITUTE($C2,"-",REPT(" ",100)),(100*(COLUMNS($N:N)-1))+1,100))
L3:L13	L3	=IF(COUNTIF(D$2:D2,D3)>=1,"Dup-GP","")
M3:M13	M3	=IF((COUNTIFS(N$2:Q2,N3,N$2:Q2,">""")+COUNTIFS(N$2:Q2,O3,N$2:Q2,">""")+COUNTIFS(N$2:Q2,P3,N$2:Q2,">""")+COUNTIFS(N$2:Q2,Q3,N$2:Q2,">"""))>0,"Dup-V","")

Hope that helps.

 

[mianmithu]

Still, You are really right by
"I'm struggling to do Voucher without use of helper columns.
Helpers can be hidden but you would need as many helpers as the max number of vouchers per cell."

So waiting for the best without using helper columns so in future if vouchers are more than 10 then it will be too ugly for me to use many columns and their references...

I'm struggling to do Voucher without use of helper columns.
Helpers can be hidden but you would need as many helpers as the max number of vouchers per cell.

 

[Akuini]

So waiting for the best without using helper columns so in future if vouchers are more than 10 then it will be too ugly for me to use many columns and their references...

How about:
1. Using UDF (User Defined Function), it means using vba.
2. In col L, it shows the duplicated item (e.g 145) not just saying there's a duplicate.

You don't need any helper column.
Let me know if you're interested in this method.

 

[mianmithu]

How about:
1. Using UDF (User Defined Function), it means using vba.
2. In col L, it shows the duplicated item (e.g 145) not just saying there's a duplicate.

You don't need any helper column.
Let me know if you're interested in this method.

I would love to get the duplicated digit instead getting just a hint as the workbook already been little bit heavy due to helper column formulation

But i dnt know the way to use vba and meanwhile i have many worksheets in this workbook i am afraid if these other sheets will be ceeating issues or if the workbook will be heavy

To be honest i dnt knw much about vba and its affects and the way it works

I have few questions abt it plz increase my knowledge as well about vba

I have alot of other workbooks with alot of formats and the mixture of mutliple formulas more than 5 rows long in each cell
So according to my fear i have a thought if i use macro in one sheet
The whole workbooks will create disturbance for me as whenever i ll try to save 'em they will ask for something to do

While working on some workbook
A pop up comes within the passage of time after every distance of time
I have to change formats, formulas, make new sheets etc
Will vba or macro create any disturbance for other workbooks or sheets in same workbook?

 

[Akuini]

Will vba or macro create any disturbance for other workbooks or sheets in same workbook?

Well, VBA can create problems if it's too complicated or flawed. But the UDF I'm offering is simple, so I don't think it will be a problem. But I don't know your workbook so I can't guarantee it. So I think you can try it to see how it works.

Note:

• You need to change your file to .xlsm format to work with vba.
• I changed the data a bit to show how it works.
• I use col L & M to show duplicate items in Voucher & Gate Pass
• The UDF are getDup1 & getDup2

How to use UDF (User Defined Function):
1. Insert a Module in the workbook.
2. Copy the code then paste into the module.
3. Back to your sheet, now you can use the function.

or you can read this link:
how-to-create-custom-excel-functions

The workbook:
example

Function getDup1(c As Range, z As Range) As String
Dim i As Long
Dim tx As String
Dim va
Dim d As Object

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

If c.Cells.Count = 1 Then
    d(CStr(c.Value)) = Empty
Else
    va = c
    For i = 1 To UBound(va, 1)
        For Each x In Split(va(i, 1), "-")
        d(CStr(x)) = Empty
        Next
    Next

End If
    For Each x In Split(z.Value, "-")
        If d.exists(x) Then tx = tx & "," & x
    Next
getDup1 = Mid(tx, 2)

End Function


Function getDup2(c As Range, z As Range) As String

Dim i As Long
Dim tx As String
Dim va
Dim d As Object

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

If c.Cells.Count = 1 Then
    d(CStr(c.Value)) = Empty
Else
    va = c
    For i = 1 To UBound(va, 1)
        d(CStr(va(i, 1))) = Empty
    Next

End If
    
        If d.exists(CStr(z.Value)) Then tx = tx & "," & z.Value
    
getDup2 = Mid(tx, 2)

End Function

UDF - find-search-duplicate-values-in-slash-or-character-data-in-one-cell.1120530.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Remarks	Remaining Stock	Voucher	Gate Pass	Sale	Return	Production	Prev		Quality	Date	Voucher dup	GP DUP
2		32,675	142	9	2500	20	34655	500		Fabric 1	01-Jan
3		4,33,882	142-143-144	10	2500		436182	200		Fabric 2	01-Jan	142
4		99	145-146-147-148	11	600	2	397	300		Fabric 3	01-Jan
5		960	149-150	12	4000		4160	800		Fabric 4	01-Jan
6		1,277	153	13	15000		15377	900		Fabric 5	01-Jan
7		5,145	152	11	1000		5045	1,100		Fabric 6	01-Jan		11
8		3,797	145-148	18	9000		10297	2,500		Polyster 1	01-Jan	145,148
9		18,604	145-154-156	19	50000	400	65604	2,600		Polyster 2	01-Jan	145
10		323	147	20	1800		2023	100		Polyster 3	01-Jan	147
11		12,000	141-155	18	170		12120	50		Polyster 4	01-Jan		18
12		605				60	540	5		Polyster 5	01-Jan
13		68,906					68888	18		Polyster 6	01-Jan
Sheet1

Cell Formulas
Range	Formula
L3:L13	L3	=getDup1(C$2:C2,C3)
M3:M13	M3	=getDup2(D$2:D2,D3)