hello matt,
Let's call
the first column of data is 15023199 and the second column of data is Alpha
character.
i would like to look for all the rows in the column with 15023199, and if
any of the rows are equal, would like to delete the row with lower confidence
level
compare 15023199 with next row in that column, in the example above they are
equal ,so i would now compare the other alpha characters associated with
them, and choose the one with higher value and delete the row which has lower
alpha character. and loop this process so even if i find the number 15023199
way below the coloumn it would still compare and delete the row with lower
alpha value,
i think would need some kind of recursion..
look at the pseudo code here, you may get an understanding (Visual Basic )
iOffset = 0
curr_num = ""
do while !EOF{
trans_num = readline(iOffset)[0]
temp_num = trans_num
cBestGrade = 'F'
for(temp_num == trans_num){
if(cBestGrade <= readline(iOffset)[1]){
cBestGrade = readline(iOffset)[1];
}
iOffset++;
temp_num = readline(iOffset)[0]
}
write(new_file, trans_num, cBestGrade);}
write(new_file, trans_num, cBestGrade);
thanks for ur reply ...
looking forward for ur help
thanks
If I understand you correctly you have 2 columns of data. Let's call
the first column of data "Col 1" and the second column of data "Col
2." Each column of data has a number concatented with an alpha
character. If an item in "Col 1" equals an item in "Col 2," then you
want to delete both items from their respective columns? Or, do you
want to delete one of the items from one one of the columns? The
description of your problem is not very clear.
This being said, I'm not sure what you are looking for, so here is a
list of Excel funcitons (the Excel funcitons work in VBA as well) that
may be useful:
Right
Left
Len
VLookup
Find (or Search)
If you are unaware of how these work, search the Excel Help and work
through the examples.
- Show quoted text -
I'm not a programming expert, but it looks to me like you are writing
your code in some version of "C." I don't know "C" language, but I do
know a little bit about Excel VBA. I can write some code in Excel VBA
that will do what you are looking for, but I don't know if it will be
of help to you. Uh, why not? Below you will see one way of doing
what you are looking for.
For sake of easiness, this will work if you sort the two columns of
data. In Excel you can do this by selecting both columns and then
going to File Menu: Data/Sort. You have the option of doing three
sorts. Do two sorts, sort by Column 1 (i.e. the numbers) in ascending
or decending order, and then do a second sort on Column 2 (i.e. the
alpha character) in decending order. This will place all the numbers
that are the same next to one another and then it will place the alpha
characters in order from A to E. Also, the following code will work
if the data is contiguous and the data set starts in Range("A1").
You can then do something like the following (Note, I haven't tested
this, but it should work):
Sub deleteLowConfidence()
Dim a
Dim counter
Dim currentVal
Dim nextVal
Dim currentAlpha
Dim nextAlpha
counter = Range("A1").CurrentRegion.Rows.Count
For a = 1 To counter
If counter = a - 1 Then
Exit For
End If
currentVal = Range("a" & a).Value
nextVal = Range("a" & a).Offset(1, 0).Value
currentAlpha = Range("b" & a).Value
nextAlpha = Range("b" & a).Offset(1, 0).Value
currentAlpha = alphaToNumber(currentAlpha)
nextAlpha = alphaToNumber(nextAlpha)
If currentVal = nextVal Then
If currentAlpha < nextAlpha Then
Range("b" & a).Offset(1, 0).EntireRow.Delete
Else
Range("b" & a).EntireRow.Delete
End If
counter = Range("A1").CurrentRegion.Rows.Count
a = a - 1
End If
Next
End Sub
Private Function alphaToNumber(ByVal confAlpha As String) As Integer
Select Case confAlpha
Case "A"
alphaToNumber = 1
Case "B"
alphaToNumber = 2
Case "C"
alphaToNumber = 3
Case "D"
alphaToNumber = 4
Case "E"
alphaToNumber = 5
End Select
End Function
Matt
