Hello All ,

here is some data i have in Excel in just one row

15023199 C
15023199 D
15023200 A
15023200 D
15023199 E
15023199 B
........
....
....
The Number Here is the sequence number and the Alphabet here is the
confidence level.
I need to compare data of each row with other and delete the row with same
sequence number and lowerconfidence level (A --high confidence----E-- high
confidence)

so inthe example above
15023199 should get B and 15023200 Should have an A.

thanks

On Mar 21, 9:57 am, Please help in building a Vba in excel
soft.com wrote:
Hello All ,

here is some data i have in Excel in just one row

15023199 C
15023199 D
15023200 A
15023200 D
15023199 E
15023199 B
.......
...
...
The Number Here is the sequence number and the Alphabet here is the
confidence level.
I need to compare data of each row with other and delete the row with same
sequence number and lowerconfidence level (A --high confidence----E-- high
confidence)

so inthe example above
15023199 should get B and 15023200 Should have an A.

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.

Matt

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


"matt" wrote:

On Mar 21, 9:57 am, Please help in building a Vba in excel
soft.com wrote:
Hello All ,

here is some data i have in Excel in just one row

15023199 C
15023199 D
15023200 A
15023200 D
15023199 E
15023199 B
.......
...
...
The Number Here is the sequence number and the Alphabet here is the
confidence level.
I need to compare data of each row with other and delete the row with same
sequence number and lowerconfidence level (A --high confidence----E-- high
confidence)

so inthe example above
15023199 should get B and 15023200 Should have an A.

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.

Matt

On Mar 21, 12:07 pm, Please help in building a Vba in excel
soft.com wrote:
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



"matt" wrote:
On Mar 21, 9:57 am, Please help in building a Vba in excel
soft.com wrote:
Hello All ,

here is some data i have in Excel in just one row

15023199 C
15023199 D
15023200 A
15023200 D
15023199 E
15023199 B
.......
...
...
The Number Here is the sequence number and the Alphabet here is the
confidence level.
I need to compare data of each row with other and delete the row with same
sequence number and lowerconfidence level (A --high confidence----E-- high
confidence)

so inthe example above
15023199 should get B and 15023200 Should have an A.

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.

Matt- Hide quoted text -

- 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