Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    my workbook has a pivot table which totals the number of students on a course. the table shows the course code and the total of students. i need to copy the total to the corresponding row in another sheet, but the course code column in the target may have more than one course code in it, or it may be formatted differently. ie the codes may have leading / trailing periods, be separated by slashes, or other arrangements. is there a way for a formula or macro to lookup a partial match and then paste into another cell in that row? the target cell's column will be the one for the current month. any help gratefully accepted. Frank

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='frankwalsh1962' post='792971' date='11-Sep-2009 09:35']my workbook has a pivot table which totals the number of students on a course. the table shows the course code and the total of students. i need to copy the total to the corresponding row in another sheet, but the course code column in the target may have more than one course code in it, or it may be formatted differently. ie the codes may have leading / trailing periods, be separated by slashes, or other arrangements. is there a way for a formula or macro to lookup a partial match and then paste into another cell in that row? the target cell's column will be the one for the current month. any help gratefully accepted. Frank[/quote]
    Are we correct in assuming that:
    • The course code as found on the target sheet will always be identical to the course code on the source sheet; although the course code on the target sheet may be wrapped in other text?
    • The course code as found on the source sheet will never be inadvertently created on the target sheet as a consequence of:
      • a shorter code being found within a longer code eg: BACD and GBACDF; or
      • two adjacent codes and their separators creating a valid third code?
    • Valid course codes contain only alphanumeric characters?
    Can you post a stripped down sample of the workbook, explaining what is being copied to where?
    Regards
    Don

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This looks to be turning into a big (for me) project.

    I've broken it down into several chunks, the first is to locate a row where col A contains the course code, then find the column for the current month.

    Next, locate a specific column in another workbook and count the number of times that course code appears - in a column containing many different codes.

    This total then should appear in the current month column of the first workbook.

    I'll create some dummy wsheets over the weekend and post them if that would help folk get a handle on things.

    Frank

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, it would certainly help to see sample data, otherwise we have no idea what you're working with.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='793852' date='18-Sep-2009 10:28']Yes, it would certainly help to see sample data, otherwise we have no idea what you're working with.[/quote]

    I hope this is helpful. I’ve attached a workbook with dummy data, in real life two workbooks with multiple sheets are used. Don Well’s assumption hold true - every module code is of the form 9ABC?123, (the ? is either a digit or a letter). So here goes with the problem:

    My university offers courses for healthcare professionals working in hospitals in England. The hospitals are called Trusts, and buying a place on a course is called ‘commisioning’. Each course is called a Module.

    I have a list of module applications, from each Trust.
    For each module code, I need the the number of people wanting a place from each Trust
    This number goes into the corresponding row of the ‘places per module’ sheets, one sheet for each Trust.

    ‘Applicants for places’ is one workbook, with a sheet for each of the (several dozen) Trusts. We get a new workbook every month, with all that month’s new applications.
    ‘Places per module’ is a separate workbook, with a sheet for each Trust, the list of modules is the same for each Trust. Here we log the new applications in the column for that month, and it keeps the running total.

    By hand, we can run down the applications list by eye, and tally the rows with any given module code. The total goes into the appropriate cell in the sheet for that Trust, ie row for the module, column for the current month. We have to do that for each module code, for each Trust in turn, every month. There are over a hundred modules, several tens of trusts, and many hundreds of students over the course of a year.

    The relevant column in ‘applicants for places’ is CF Module Code, the other column are not of interest here.
    The match is with the value in ‘Code’ (col A). The total goes into the current month column.
    Sometimes the col A value is the module code, sometime we find more than one code as some courses have two code attached to it. It would be possible to create a separate row for each in these cases if that made the VBA easier to create.

    Thanks in advance, Frank.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The attached version is not intended as a definitive solution, but as a starting point. Could you take a look at it and test where it fails?

    Code:

    Code:
    Function CountPlaces(Code As String) As Integer
      Dim wbk As Workbook
      Dim wsh As Worksheet
      Dim r As Long
      Dim m As Long
      Dim i As Long
      Dim arrCodes
      ' Force recalculation
      Application.Volatile
      ' Split Code string
      arrCodes = Split(Code)
      ' Clean up the individual parts
      For i = LBound(arrCodes) To UBound(arrCodes)
    	arrCodes(i) = Left(arrCodes(i), 8)
      Next i
      ' Replace with reference to other workbook
      Set wbk = ActiveWorkbook
      ' Replace with reference to relevant worksheet
      Set wsh = wbk.Worksheets("applicants for places - October")
      ' Loop through rows
      m = wsh.Cells(wsh.Rows.Count, 14).End(xlUp).Row
      For r = 2 To m
    	' Compare to each part of Code
    	For i = LBound(arrCodes) To UBound(arrCodes)
    	  If wsh.Cells(r, 14) = arrCodes(i) Then
    		CountPlaces = CountPlaces + 1
    		Exit For
    	  End If
    	Next i
      Next r
    End Function
    [attachment=86115:example.xls]
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, how do you do this so quickly?
    I'll see my customer and let you know how we get on.
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •