I Want To Retrive The Duplicate Contacts In The Columns And Also Want To Know That In Which Cell Or Column The Duplicate Is Existing.
My Workbook Is Attached.
I Want To Retrive The Duplicate Contacts In The Columns And Also Want To Know That In Which Cell Or Column The Duplicate Is Existing.
My Workbook Is Attached.
Aaj it dunya pa search karte huwe apke thread ko parha. Aap ne tu shayad is ko hal kar leya hoga. main ne bhe try ki ha. Aap deekh kar bataye ga ke kis jagha kame ha ( for my own intrest and experience). Thanks.
solution sheet par solution No.1 column "C4" k leye Array formula ha Jis ko tpe karne k baad aap "control+shift+enter" aik saath press karain ge. Ye formula aap ko sirf aik cell ka address return kare ga mazeed cell address ke leye is formula ko cross aur down copy karain aap ko tamam ka cell address mil jae ge.
=IF(COLUMNS($C$4:C4)<=COUNTIF($C$4:$C$148,$C4),CEL L("address",INDEX($C$4:$C$148,SMALL(IF($C$4:$C$114 8=$C4,ROW($C$4:$C$1148)-ROW($C$4)+1),COLUMNS($C$4:C4)))),"")
solution 2(i) and 2(ii) jis main tamam cell adress aik he cell main hain is k leye zaroori ha ka aap ko morefunc.xll add-in k zarorat pare ge. jo k aap is link se download kar sakte hain. Kiyon ke mconcat is ka part ha. jo app free download kar ke install kar lain.
http://xcell05.free.fr/morefunc/english/index.htm
Alternate download site:
http://www.download.com/Morefunc/300...-10423159.html
formula 1.
=SUBSTITUTE(TRIM(MCONCAT(IF($C$4:$C$148=C4,ADDRESS (ROW($C$4:$C$148),3,4),"")&" "))," ",",")
formula 2.
=SUBSTITUTE(MCONCAT(IF($C$4:$C$148=C4,","&ADDRESS( ROW($C$4:$C$148),COLUMN($C$4),4),"")),",","",1)
"is formula ko attached file main change kar legeye ga. kyonke file me main ranges absolute karna bhool gaya hon."
Last edited by syar; 24th September 2009 at 06:55 PM. Reason: forgot to make ranges absolute in file
If you don't want to install xlmorefunction add-in then you can use below
user defince function work like mconcat.
Open your workbook. Press Alt+F11. A Vb editor winow open.
Then in menu==> insert====>module
paste this code and close vb editor.
-----------------------------------------------------------------------
Function aconcat(a As Variant, Optional sep As String = "") As String
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
---------------------------------------------------------------------------
now you cane replace mconcat with aconcat like
1.=SUBSTITUTE(TRIM(ACONCAT(IF($C$4:$C$148=C4,ADDRE SS(ROW($C$4:$C$148),3,4),"")&" "))," ",","
2.=SUBSTITUTE(ACONCAT(IF($C$4:$C$148=C4,","&ADDRES S(ROW($C$4:$C$148),COLUMN($C$4),4),"")),",","",1)
copy down.
Regards,
Bookmarks