You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- find and copy matching data

Advertisement

QUESTION: In sheet2 i have list of students and classes in Column B and their Average marks in column C such as:

Col. B Col.C

Jhonson

Class 1 220

Class 2 330

Class 3 340

Class 4 420

Class 5 320

Class 6 260

Class 7 230 so on upto class 12

Mark

Class 1 220

Class 2 330

Class 3 340

Class 4 420

Class 5 320

Class 6 260

Class 7 so on upto class 10 only

Jack lee

Class 1 220

Class 2 330

Class 3 340

Class 4 420

Class 5 320

Class 6 260

Class 7 so on upto class 12

In sheet1 i enter following data in Column B:

Jack Lee

Class 1

Class 3 promoted

Class 10 8th position

Class 11 1st position

Jhonson

Class 5

Class 6

Class 9

Class 10 3rd position

class 12

12x2000(School fee)

Mark

Class 1 Passed

Class 2

Class 3

Class 4 promoted

Class 10 Failed

Now i want to find the exact average marks of each Students of their own classes (as appeared in sheet2) and put it in column D on the corresponding row of each individual classes. Can you provide a VBA code for me to accoumplish my task?

Note:

1. Cells that contain text other than "Class" should be a name of student

2. If a cell contain text "x" preceded by number then ignore the text(i/c numbers)that sufixes "x". Numbers that precede "x" are the class of that particular student. As in case of Jhonson where it is entered to show that he didn't pay school fee amounting to 2000 in class 12. However his average marks for class 12 need to be shown in column D.

3. Ignore if matching name of student in sheet1 could not be find in sheet2.

Thanks.

ANSWER: Hi Mark,

Sorry about my slow response--just back from vacation and a lot of questions piled up.

I believe this VBA macro does what you describe. Please let me know if you have any problem with it.

___________________________________________________________________________

Sub GetAvgMarks()

Dim iRow1 As Long

Dim iRow2 As Long

Dim LastRow2 As Long 'last data row in Sheet2

Dim WS1 As Worksheet

Dim WS2 As Worksheet

Dim Student As String 'the student name

Dim Sstr As String 'the search string

Dim ClassNo As Integer 'the class number

Dim AvgMark As Single

Set WS1 = Worksheets("Sheet1")

Set WS2 = Worksheets("Sheet2")

LastRow2 = WS2.Range("B65536").End(xlUp).Row

With WS1

For iRow1 = 1 To .Range("B65536").End(xlUp).Row

Sstr = UCase(.Cells(iRow1, "B"))

If Sstr Like "CLASS #*" Then

ClassNo = CInt(Mid(Sstr, 7, 2))

'search Sheet2 for Mark for student in ClassNo

With WS2

For iRow2 = 1 To LastRow2

'first look for student

If UCase(.Cells(iRow2, "B")) = Student Then

Do

iRow2 = iRow2 + 1

If UCase(.Cells(iRow2, "B")) Like "CLASS*" Then

If Mid(.Cells(iRow2, "B"), 7) = ClassNo Then

'class number found. Store average mark

AvgMark = .Cells(iRow2, "C")

GoTo FoundMark

End If

End If

Loop Until iRow2 = LastRow2

GoTo RowDone

FoundMark:

End If

Next iRow2

End With 'WS2

'put average mark in column D

.Cells(iRow1, "D") = AvgMark

ElseIf Sstr Like "*#X*" Then

'skip this row

GoTo RowDone

Else

'must be student name since not "Class" and not school fee

Student = Sstr

End If

RowDone:

Next iRow1

End With 'WS1

End Sub

_________________________________________________________________________

Damon

---------- FOLLOW-UP ----------

QUESTION: Sir,

Sorry for late response. Can i replace Class 1, Class 2, etc. with 2mm dia, 35mm dia, 500mm dia etc.? Here marks will be now rates. Also there shall be a text with number like 60x45x40mm dia. in place of class. In this case, rate of higher number should be considered i.e. here rate of 60mm dia.

Thanks

ANSWER: Hi Nabam,

I am a bit confused by your question. When you mention putting "60x45x40mm dia." in place of class, I'm not really sure where that is in the data. The same question applies to the 2mm dia, etc. Is it in place of the word "class"? It would really help if you could give me the two data files as before, and even a third file showing me what the processed result should look like.

Damon

PS. If it would be easier you could email them to me at VBAexpert@myway.com.

---------- FOLLOW-UP ----------

QUESTION: Please see the image alongside. Thanks for your patience.

Hi again Nabam,

Okay, here is a user-defined function you can use to fill those cells:

Function LookupRate(GIsocket3 As String, RateTable As Range) As Single

'looks up the rate corresponding to the largest diameter number in the

'string of the form nnnxnnxnnx (x's used as delimiters between numbes)

'in RateTable, a two columm table where the first column contains diameter

'values in the form "80mm dia" and the corresponding rates are in column 2.

Dim Diam As Single

Dim nDchar As Integer 'the length of the first diameter string

Dim iRow As Integer

Dim LookDiam As Single

nDchar = InStr(1, GIsocket3, "x") - 1

LookupRate = 0

If nDchar < 1 Then Exit Function

Diam = CSng(Left(GIsocket3, nDchar))

For iRow = 1 To RateTable.Rows.Count

nDchar = InStr(1, RateTable(iRow, 1), "m") - 1

LookDiam = CSng(Left(RateTable(iRow, 1), nDchar))

If LookDiam = Diam Then

'socket diameter found--get rate from same row of table

LookupRate = CSng(RateTable(iRow, 2))

Exit Function

End If

Next iRow

End Function

Place this function code in a standard macro module. Then you can use this function in cell G11 like this:

=LookupRate(F11,B$12:C$16)

Now you can autofill this formula down through G15 (or to the end of data) and it should compute the desired values in G11:G15.

Let me know if you have any problems with this.

Damon

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.**Education/Credentials**

B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.