finding values from duplicates

[bisjom]

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

 

[Tom Ogilvy]

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

 

[bisjom]

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

 

[bisjom]

Hi Tom..
It done.. Fantastic...
But there is a little problem.. the column value after removing i
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 fin
now...

Can i ask another help..

I want to print this values to a text box in a userform . that mean
each textbook id to each textbox. that means ,consider i have a limi
of textbook of 10 . i have 10 textboxex , these values in the shee
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..

 

[bisjom]

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

Code:
--------------------

Dim a, k As Integer
Dim qsc As Variant
qsc = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
acell=RANGE("A1")
If acell = "" Then
msgbox " nothing in it"

boxno=1
Else
a = Split(acell, ",")
cellsum = 0
For k = LBound(a) To UBound(a)
'MsgBox LBound(a)

If a(k) <> "" Then
qsc(k) = a(k)
USERFORM1.TXT&(boxno)=qsc(K)
boxno=boxno+1
endif
Next k


--------------------


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

 

[bisjom]

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

 

[bisjom]

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,


Code:
--------------------


Sub Button1_Click()

Dim a, k As Integer
Dim qsc As Variant
qsc = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)


boxno = 1
boxname = "TXT"
For i = 1 To 3


acell = Range("A" & i)
If acell = 1 Then
MsgBox " nothing in it"



Else
a = Split(acell, ",")
cellsum = 0
For k = LBound(a) To UBound(a)
'MsgBox LBound(a)

If a(k) <> "" Then
qsc(k) = a(k)
UserForm1.Controls.Item(boxname + boxno).Text = qsc(k)
boxno = boxno + 1
End If

Next k
End If


UserForm1.Show

Next i

End Sub

 

[bisjom]

Hi Guys...
I got the answer..
I changed the code where there was :

Code: