+ Reply to Thread
Results 1 to 8 of 8

finding values from duplicates

  1. #1
    Registered User
    Join Date
    02-05-2006
    Posts
    11

    finding values from duplicates

    Hi guys..
    I got problem..
    I have a sheet with details of students with their textbook id and its details. Same sutdent can have different textbookid .
    I have to sort the student and remove the duplicates.. but while removing the duplicate student, i have to display all the textbook id in the textbookid column. say..
    If the student A got 3 different textbook id (Column say 'M') 32,35,36 in three different rows, while removing , the row of the student A should have column 'M' with the value 32,35,36.. how can o do that .. the function used for duplicate is simple:


    Sub RemoveDuplicates()

    For Row = 25 To 2 Step -1
    If Cells(25,”A”).Value = Cells(Row - 1, “A”).Value Then

    Rows(Row).Delete
    End If
    Next Row
    End Sub


    please help

  2. #2
    Tom Ogilvy
    Guest

    Re: finding values from duplicates

    Sub RemoveDuplicates()

    For Row = 25 To 2 Step -1
    If Cells(row,"A").Value = _
    Cells(Row - 1, "A").Value Then
    s = s & Cells(row,"M").Value & ", "
    Rows(Row).Delete
    Else
    if s <> "" then
    cells(row,"M").Value = s & ", " & _
    cells(row,"M").Value
    s = ""
    end if
    End If
    Next Row
    End Sub

    --
    Regards,
    Tom Ogilvy


    "bisjom" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi guys..
    > I got problem..
    > I have a sheet with details of students with their textbook id and its
    > details. Same sutdent can have different textbookid .
    > I have to sort the student and remove the duplicates.. but while
    > removing the duplicate student, i have to display all the textbook id
    > in the textbookid column. say..
    > If the student A got 3 different textbook id (Column say 'M') 32,35,36
    > in three different rows, while removing , the row of the student A
    > should have column 'M' with the value 32,35,36.. how can o do that ..
    > the function used for duplicate is simple:
    >
    >
    > Sub RemoveDuplicates()
    >
    > For Row = 25 To 2 Step -1
    > If Cells(25,"A").Value = Cells(Row - 1, "A").Value
    > Then
    >
    > Rows(Row).Delete
    > End If
    > Next Row
    > End Sub
    >
    >
    > please help
    >
    >
    > --
    > bisjom
    > ------------------------------------------------------------------------
    > bisjom's Profile:

    http://www.excelforum.com/member.php...o&userid=31206
    > View this thread: http://www.excelforum.com/showthread...hreadid=510448
    >




  3. #3
    Registered User
    Join Date
    02-05-2006
    Posts
    11
    You are brilliant Tom and pretty quick.. I want to do exactly this and i tried it with '+' and other operators... How stupid i am...

    I will try and let you know...
    Thank you

  4. #4
    Registered User
    Join Date
    02-05-2006
    Posts
    11

    Thumbs up

    Hi Tom..
    It done.. Fantastic...
    But there is a little problem.. the column value after removing is like::
    10, , 28, , 32. I have to check y this blanl space in between..

    But thats minor porblem... thanks very much,,

    Na.. i sort it out.. there was coma and a space twice.. its fine now...

    Can i ask another help..

    I want to print this values to a text box in a userform . that means each textbook id to each textbox. that means ,consider i have a limit of textbook of 10 . i have 10 textboxex , these values in the sheet cell say 10,28,32 should go to each text boxes say txtB1,txtB2,txtB3..

    Can i take two charactes each since the id should be of 2 character..

    what's your view//

    thanks for your help...

    you are wonderful...

  5. #5
    Registered User
    Join Date
    02-05-2006
    Posts
    11
    Hi tom,

    I was trying to fix the problem of displaying the data from sheet to form..

    it is working fine for one value since i am stucked half way..

    The below is the code i have used...
    Please Login or Register  to view this content.
    but I have a problem at(here) :USERFORM1.TXT&(boxno)=qsc(K)
    My plan is that give the text boxes names like: txt1,txt2 etc and it should take that value from variable boxno. but it shows error.
    If i give that line as USERFORM1.TXT1=qsc(K) , it is displaying the value in it , but all the values are in the same text box and so i will get only one value..

    I know its really a silly thing.. i am trying , but really slow..

    if you get sometime to do.. please help me..

    thanks for your help...

  6. #6
    Registered User
    Join Date
    02-05-2006
    Posts
    11
    Hi Tom...

    Its done...

    I changed the code in that line to:
    [code]
    UserForm1.Controls.Item(boxname + boxno).Text = qsc(k)
    [\code]

    where boxname is the variable which stored "TXT" and it is taking the textboxname as TXT1,TXT2,TXT3..

    thanks Tom...

  7. #7
    Registered User
    Join Date
    02-05-2006
    Posts
    11
    Hi guys...

    sorry I need your help again..

    I tried the code that i have given before ,it is working fine for one value , but if i put it into the loop, it will work for the first time and it shows 'type mismatch ' error.. do you have any idea,

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-05-2006
    Posts
    11

    Thumbs up

    Hi Guys...
    I got the answer..
    I changed the code where there was :
    Please Login or Register  to view this content.
    + to & and its fine,,
    Thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1