On Mar 21, 12:07 pm, Please help in building a Vba in excel
<PleasehelpinbuildingaVbainex...@discussions.microsoft.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
> > <PleasehelpinbuildingaVbainex...@discussions.microsoft.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
Similar Threads
