• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Student Poll

S

Syed Tanveer

New Member
Moved by Mod from Introduction thread
-------------

I need help to solve this problem in excel.
The data pertains to students writing examinations.
sheet1 contains data (10 digit roll numbers of students) in columns A & B.
This roll numbers of students are to be copied into sheet2 (in specific rows) based on certain criteria.
Ex: If the 3, 4 digits (represent college code)of the roll number are "2N" and 7, 8 digits (representing branch code)are "PQ", then the roll number should be copied to some cell in sheet2.
This should be done for all the roll numbers in sheet1 and the roll numbers in each row are to be separated by commas.

Pl see the attached file for more clarity.

Thanks in advance for your sparing your time and efforts.
 

Attachments

Last edited by a moderator:
Before attempting to solve this, I would point out that storing multiple bits of data within the same cell is NOT a good idea within XL. It's much better to keep each unique bit of information in it's own cell, whether as a hard entered data or as the result of a formula.

I'd also propose that if we stack the original data into a single column, we can calculate College Code and Branch quickly using some formulas. In the attached, I modified sheet1 and then produced a Pivot report on Sheet3. The PivotTable shows you the quick count for each college/branch, and you can drill down if needed to see the individual names
 

Attachments

Thank you very much Luke.
Really appreciate your efforts !!!
The sheet2 is actually the format for displaying the hall wise distribution of students writing exam.
I will go through it and see how I can modify it to match my needs.
 
Pl help me solve this problem that I am handling from quite some time.

In the attachment, sheet1 contains data (roll numbers of students).
In fact it is a pdf file converted to excel.
In sheet2, I need all the roll numbers in a single column (as shown in sheet2).

I have been doing this manually (copying each row, transposing it and pasting in sheet2) which consumes a lot and lot of time.
Please show me some shortcut/formula to do this.
 

Attachments

This macro will extract the deisred information. Runs almost instantly. :cool:
Code: 
Sub GrabNumbers()
'How many characters in a student ID?
Const numLength As Long = 10
Dim searchCells As Range
Dim c As Range
Dim recRow As Long
Dim destWS As Worksheet
Dim strName As String
Dim myArray As Variant

'Where does output go?
Set destWS = Worksheets("Sheet2")
'Clear any previous data
destWS.Cells.ClearContents

'Starting output
recRow = 1

Application.ScreenUpdating = False

'Limit our search to only cells with values, to improve speed
Set searchCells = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants)
myArray = destWS.Range("A1:A" & searchCells.Cells.Count).Value
For Each c In searchCells
    strName = c.Value
    'Check if string matches desired length
    If Len(strName) = numLength Then
        'Build an array of all our values
        myArray(recRow, 1) = strName
        recRow = recRow + 1
    End If
Next
'Output the records
destWS.Range("A1:A" & UBound(myArray)).Value = myArray

Application.ScreenUpdating = True
End Sub
 
Thank you very very much Luke !!
This saves hours of my time.
Thanks a lot.
One more request,
could you please explain me the macro step by step in detail.
(This is my first macro !)
 
Macro with extended explanation in comments.
Code: 
Sub GrabNumbers()

'How many characters in a student ID?
'Since we don't need to change this during the run of the macro
'we define it as a constant. Benefit is that this uses less memory
Const numLength As Long = 10

'Define all the rest of our variables, and what type of variable they are
Dim searchCells As Range
Dim c As Range
Dim recRow As Long
Dim destWS As Worksheet
Dim strName As String
Dim myArray As Variant

'Where does output go?
'Since this variable is defined by you, we fill this in now
Set destWS = Worksheets("Sheet2")

'Now that we know what specific sheet we are working with, we use
'that variable name when referring to it. Here we clear the contents of
'all the cells on the destination sheet
'Clear any previous data
destWS.Cells.ClearContents

'Define the starting row where we will begin to do our output
'Starting output
recRow = 1

'Turn off screen updating. This prevents the screen from "flashing"
'as the macro makes changes, and lets the code run 10x faster since
'it doesn't have to spend time updating the screen
Application.ScreenUpdating = False

'Limit our search to only cells with values, to improve speed
'The UsedRange is a subset of all the cells on a worksheet. As you
'might guess from the name, it refers to the range starting in A1 to the last used
'cell in the worksheet
'The SpecialCells then is a subset of that group, in which we select only the
'cells that have a constant value. This way we don't have to waste time
'searching through all the blanks cells
Set searchCells = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants)

'We are going to populate an array with values and then export that
'array to the worksheet. For now, we just need to build one big enough
'to hold all the values. So, we make it equal in size to max number of cells
'we are going to look at
myArray = destWS.Range("A1:A" & searchCells.Cells.Count).Value

'Read this as "for each cell in this range..."
For Each c In searchCells
   
    'Put the cell's value into a variable, so we don't have to read
    'from the worksheet multiple times. Again, this helps speed things up
    strName = c.Value
   
    'Check if string matches desired length
    'Read this as "If the length of the cell's value matches the length we
    'are looking for, then...
   If Len(strName) = numLength Then
   
        '...put that value into our array for record keeping
        'Note that the recRow variable helps us keep track of how many
        'records we have found
        myArray(recRow, 1) = strName
       
        'Be sure to increase this variable so our code knows we added
        'a record to the array
        recRow = recRow + 1
    End If
Next

'Output the records
'Now that we are all done, we send the values in the array to the worksheet
'We do this in one line of code, which makes it very fast. The UBound method
'takes an array and returns the Upper Boundary, aka how big is the array
destWS.Range("A1:A" & UBound(myArray)).Value = myArray

'Make sure we turn the screen updating back on!
Application.ScreenUpdating = True
End Sub
 
You must log in or register to reply here.
Back
Top