Results 1 to 3 of 3

Thread: Excel Help

  1. #1
    sajidbagi is offline Senior Member+
    Last Online
    1st February 2023 @ 06:54 PM
    Join Date
    04 Jan 2009
    Posts
    225
    Threads
    20
    Credits
    1,200
    Thanked
    5

    Default Excel Help

    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.
    Attached Files Attached Files

  2. #2
    syar is offline Advance Member
    Last Online
    14th March 2024 @ 07:49 PM
    Join Date
    31 Jul 2009
    Location
    Nowshera(KP)
    Age
    47
    Gender
    Male
    Posts
    882
    Threads
    9
    Credits
    548
    Thanked
    340

    Post

    Quote sajidbagi said: View Post
    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.
    .


    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."
    Attached Files Attached Files
    Last edited by syar; 24th September 2009 at 06:55 PM. Reason: forgot to make ranges absolute in file

  3. #3
    syar is offline Advance Member
    Last Online
    14th March 2024 @ 07:49 PM
    Join Date
    31 Jul 2009
    Location
    Nowshera(KP)
    Age
    47
    Gender
    Male
    Posts
    882
    Threads
    9
    Credits
    548
    Thanked
    340

    Default



    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,

Similar Threads

  1. Excel tip (Hidde formula in formula bar in excel)
    By Honour12 in forum Urdu Tutorials & Designing
    Replies: 14
    Last Post: 31st May 2016, 10:06 AM
  2. MS EXCEL - Al-Quran in Excel
    By Mubashir Aziz in forum General Discussion
    Replies: 45
    Last Post: 16th March 2015, 08:59 PM
  3. Not Working Zaara's Excel Tutorial In Pdf & 300 Excel Tips
    By iftikhar01 in forum General Discussion
    Replies: 94
    Last Post: 14th May 2012, 04:28 PM
  4. Need Help in excel
    By nsm1870 in forum Solved Problems (IT)
    Replies: 4
    Last Post: 16th March 2010, 11:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •