Here is the quick explaination.

I work as a trainer where each person in my class is assigned up to 40
classes in an online university program. I can produce an excel sheet from
within the program, that will list everyone for a set range of time. Problem
with this is that it can display around 40000 lines of data.

My goal is to have the excel sheet that I pulled on one worksheet, and be
able to type in the persons name on a seperate worksheet that will tell me if
they have completed all of the courses. I have :

=SUMPRODUCT(--(report!$A$2 A$41625=C2),--(report!$B$2 B$41625="Agent -
Dealing with Angry Customers: Techniques 1 and 2")) in each cell where C2
would be the cell that the persons name is and Agent - Dealing with Angry
customers: Techniques 1 and 2 is the name of the course.

I want the programing perferably a macro to search column A for the name,
when found, see if Column B has the name of course in it, then post either an
x or the score in a cell. keep in mind that I have 40 courses so want this
done by pressing one button. I am not afraid to use a text box to type the
name, then hit search.

thanks a head of time.

--
Message posted via http://www.officekb.com

Darrell
I'll try to help you with this. I read what you wrote a number of times
and I'm still somewhat confused as to what you have.
You say that you have a sheet that lists everyone's name in Column A. You
also said that Column B may or may not have the name of a course. If it has
a course name, what is the significance of that course name? What is the
significance if Column B is blank? Is there data (course names perhaps) in
subsequent columns?
You say you want an "x" or the course score in a cell. What cell? On what
sheet? Where is the score found?
You say that the second sheet will tell you if the person has completed all
the courses. What is the layout of this sheet, and how do you want it to
tell you if the person has completed all the courses?
Do the following and it might help in getting you some help with this.
Pretend that you are MANUALLY doing exactly what you want this macro to do.
Write down every step in the process, describing in detail what you are
seeing in each step. This is important because the person reading your post
has only your eyes to go by.
Don't leave out any steps. Remember that you are the only one who
understands what you have and what you want.
Post back with what you wrote.
Making a wild guess at what you have and want, have you tried AutoFilter?
HTH Otto
"Darrell_Sarrasin via OfficeKB.com" <u33691@uwe wrote in message
news:7ba8dad0d4cfa@uwe...
Here is the quick explaination.

I work as a trainer where each person in my class is assigned up to 40
classes in an online university program. I can produce an excel sheet
from
within the program, that will list everyone for a set range of time.
Problem
with this is that it can display around 40000 lines of data.

My goal is to have the excel sheet that I pulled on one worksheet, and be
able to type in the persons name on a seperate worksheet that will tell me
if
they have completed all of the courses. I have :

=SUMPRODUCT(--(report!$A$2 A$41625=C2),--(report!$B$2 B$41625="Agent -
Dealing with Angry Customers: Techniques 1 and 2")) in each cell where C2
would be the cell that the persons name is and Agent - Dealing with Angry
customers: Techniques 1 and 2 is the name of the course.

I want the programing perferably a macro to search column A for the name,
when found, see if Column B has the name of course in it, then post either
an
x or the score in a cell. keep in mind that I have 40 courses so want
this
done by pressing one button. I am not afraid to use a text box to type
the
name, then hit search.

thanks a head of time.

--
Message posted via http://www.officekb.com

sorry wrote this on the fly a few times. Basically what it is I have class
of 41 people. Each of them is assigned 32 courses in an online university
program. I can pull a report but not just for that one person only for a
time period.

Example my class ran last week from monday to friday, so I can pull that
period. problem is that my class is not the only one running so it pulls
everyone during that week, leaving me with about 40000 lines of data. my
goal is to write a macro that searchs the sheet I can pull, and in a seperate
worksheet, make a report card for that person that shows a list list of the
courses and if they completed those courses. The agents name is in column A
and the courses taken are in column B. so the macro would have to find the
name in A then once the name is found, it would search column B beside the
name, and locate the course. if it finds the course and puts an "X" beside
or under the course title on the seperate sheet on the report card.

Easiest way I can think of is to do it from a text box and search but a
little stranded. I have hard coded it in the past using sumproduct, put it
causes excel to run really slow cause I had to enter it into 32 cells across
and 41 cells down.


Thanks.

Otto Moehrbach wrote:
Darrell
I'll try to help you with this. I read what you wrote a number of times
and I'm still somewhat confused as to what you have.
You say that you have a sheet that lists everyone's name in Column A. You
also said that Column B may or may not have the name of a course. If it has
a course name, what is the significance of that course name? What is the
significance if Column B is blank? Is there data (course names perhaps) in
subsequent columns?
You say you want an "x" or the course score in a cell. What cell? On what
sheet? Where is the score found?
You say that the second sheet will tell you if the person has completed all
the courses. What is the layout of this sheet, and how do you want it to
tell you if the person has completed all the courses?
Do the following and it might help in getting you some help with this.
Pretend that you are MANUALLY doing exactly what you want this macro to do.
Write down every step in the process, describing in detail what you are
seeing in each step. This is important because the person reading your post
has only your eyes to go by.
Don't leave out any steps. Remember that you are the only one who
understands what you have and what you want.
Post back with what you wrote.
Making a wild guess at what you have and want, have you tried AutoFilter?
HTH Otto
Here is the quick explaination.

[quoted text clipped - 25 lines]

thanks a head of time.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200711/1

Darrell
From what you say, I get the following:
You have a list.
In Column A are names of people.
In Column B are course names.
The relationship is that the person in Column A took the course listed in
Column B.
You want, in another sheet, the name of the person you entered somewhere and
the list of courses that person has taken.
So let's say that you enter "John".
"John" appears in Column A one or more times, maybe not at all.
You want, in another sheet, the name "John" and a listing of all the courses
in Column B that have "John" in Column A.
Is that correct?
If so, then I recommend that you use Auto Filter.
Post back if you want help with Auto Filter.
VBA (macro) can be used to automate the Auto Filter process if you wish.
HTH Otto
"Darrell_Sarrasin via OfficeKB.com" <u33691@uwe wrote in message
news:7bcb353d725d2@uwe...
sorry wrote this on the fly a few times. Basically what it is I have
class
of 41 people. Each of them is assigned 32 courses in an online university
program. I can pull a report but not just for that one person only for a
time period.

Example my class ran last week from monday to friday, so I can pull that
period. problem is that my class is not the only one running so it pulls
everyone during that week, leaving me with about 40000 lines of data. my
goal is to write a macro that searchs the sheet I can pull, and in a
seperate
worksheet, make a report card for that person that shows a list list of
the
courses and if they completed those courses. The agents name is in column
A
and the courses taken are in column B. so the macro would have to find
the
name in A then once the name is found, it would search column B beside the
name, and locate the course. if it finds the course and puts an "X"
beside
or under the course title on the seperate sheet on the report card.

Easiest way I can think of is to do it from a text box and search but a
little stranded. I have hard coded it in the past using sumproduct, put
it
causes excel to run really slow cause I had to enter it into 32 cells
across
and 41 cells down.


Thanks.

Otto Moehrbach wrote:
Darrell
I'll try to help you with this. I read what you wrote a number of
times
and I'm still somewhat confused as to what you have.
You say that you have a sheet that lists everyone's name in Column A. You
also said that Column B may or may not have the name of a course. If it
has
a course name, what is the significance of that course name? What is the
significance if Column B is blank? Is there data (course names perhaps)
in
subsequent columns?
You say you want an "x" or the course score in a cell. What cell? On
what
sheet? Where is the score found?
You say that the second sheet will tell you if the person has completed
all
the courses. What is the layout of this sheet, and how do you want it to
tell you if the person has completed all the courses?
Do the following and it might help in getting you some help with this.
Pretend that you are MANUALLY doing exactly what you want this macro to
do.
Write down every step in the process, describing in detail what you are
seeing in each step. This is important because the person reading your
post
has only your eyes to go by.
Don't leave out any steps. Remember that you are the only one who
understands what you have and what you want.
Post back with what you wrote.
Making a wild guess at what you have and want, have you tried AutoFilter?
HTH Otto
Here is the quick explaination.

[quoted text clipped - 25 lines]

thanks a head of time.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200711/1

hey otto. I dont believe I am getting my self across. currently what I have
is as you described, its a sheet that has their names and courses on it.

on sheet 2 it has the 30 courses across the top, and down the left i have the
40 students names
I want to create a macro that will basically fill an x in on the chart when
it finds the name and the course complete.

Otto Moehrbach wrote:
Darrell
From what you say, I get the following:
You have a list.
In Column A are names of people.
In Column B are course names.
The relationship is that the person in Column A took the course listed in
Column B.
You want, in another sheet, the name of the person you entered somewhere and
the list of courses that person has taken.
So let's say that you enter "John".
"John" appears in Column A one or more times, maybe not at all.
You want, in another sheet, the name "John" and a listing of all the courses
in Column B that have "John" in Column A.
Is that correct?
If so, then I recommend that you use Auto Filter.
Post back if you want help with Auto Filter.
VBA (macro) can be used to automate the Auto Filter process if you wish.
HTH Otto
sorry wrote this on the fly a few times. Basically what it is I have
class
[quoted text clipped - 62 lines]

thanks a head of time.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200711/1

Darrell
Now I have an idea of what you have and what you want. The following
macro will do what you want. As written, this macro works with 2 sheets
named "One" and "Two". Sheet "One" has the names in Column A starting in A2
and the courses in Column B starting in B2. Sheet "Two" has the names in
Column A starting in A2 and the courses in row 1 starting in B1. Change the
code as needed to work with your actual sheet names and data locations.
Watch out for line wrapping in this post. View this post in full screen.
HTH Otto
Sub CourseCompletion()
Dim rColAOne As Range, rColATwo As Range, rRow1Two As Range
Dim i As Range, TheRow As Long, TheCol As Long
Application.ScreenUpdating = False
Sheets("One").Select
Set rColAOne = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Two")
Set rColATwo = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set rRow1Two = .Range("B1", .Cells(1,
Columns.Count).End(xlToLeft))
For Each i In rColAOne
If Not IsEmpty(i.Offset(, 1).Value) Then
TheRow = rColATwo.Find(What:=i.Value,
LookAt:=xlWhole).Row
TheCol = rRow1Two.Find(What:=i.Offset(, 1).Value,
LookAt:=xlWhole).Column
.Cells(TheRow, TheCol).Value = "X"
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
"Darrell_Sarrasin via OfficeKB.com" <u33691@uwe wrote in message
news:7bcdce07474c7@uwe...
hey otto. I dont believe I am getting my self across. currently what I
have
is as you described, its a sheet that has their names and courses on it.

on sheet 2 it has the 30 courses across the top, and down the left i have
the
40 students names
I want to create a macro that will basically fill an x in on the chart
when
it finds the name and the course complete.

Otto Moehrbach wrote:
Darrell
From what you say, I get the following:
You have a list.
In Column A are names of people.
In Column B are course names.
The relationship is that the person in Column A took the course listed in
Column B.
You want, in another sheet, the name of the person you entered somewhere
and
the list of courses that person has taken.
So let's say that you enter "John".
"John" appears in Column A one or more times, maybe not at all.
You want, in another sheet, the name "John" and a listing of all the
courses
in Column B that have "John" in Column A.
Is that correct?
If so, then I recommend that you use Auto Filter.
Post back if you want help with Auto Filter.
VBA (macro) can be used to automate the Auto Filter process if you wish.
HTH Otto
sorry wrote this on the fly a few times. Basically what it is I have
class
[quoted text clipped - 62 lines]

thanks a head of time.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200711/1