You are here:

Excel/find and copy matching data

Advertisement


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

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

Answer
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
About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

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.

Experience

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.

©2016 About.com. All rights reserved.