Find something in column a then find if column B matches criteria

[Darrell_Sarrasin via OfficeKB.com]

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.

 

[Otto Moehrbach]

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]

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.

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.

 

[Otto Moehrbach]

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

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.

 

[Darrell_Sarrasin via OfficeKB.com]

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.

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]

 

[Otto Moehrbach]

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

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.

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.

 

[Darrell_Sarrasin via OfficeKB.com]

K starting to understand, but where would I enter the name that I want it to
search for? The first sheet is a one week pull of information and has a lot
of people on it that are not part of my class.

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

hey otto. I dont believe I am getting my self across. currently what I
have

[quoted text clipped - 32 lines]

 

[Darrell_Sarrasin via OfficeKB.com]

Is it possible to send you what I have to get assistance? or for you to email
me this? I can not seem to figure it out.

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

hey otto. I dont believe I am getting my self across. currently what I
have

[quoted text clipped - 32 lines]

 

[Otto Moehrbach]

Darrell
Yes, it would be easier if you just email me what you have. My email
address is (e-mail address removed). Remove the "nop" from this address. Tell
me what version of Excel you have. Otto

Is it possible to send you what I have to get assistance? or for you to
email
me this? I can not seem to figure it out.

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

hey otto. I dont believe I am getting my self across. currently what I
have

[quoted text clipped - 32 lines]

thanks a head of time.