Search 2 columns to find content and if found place an X in a cell. 1

[tdellaporta]

I have a report which I run every morning. It shows what students completed what classes. The columns are: class id (a 6 digit #), then the students name.

My challenge is this, the Class number has NOTHING to do with the order the classes are taken. Therefore on a second spreadsheet (My "Master Progress" sheet, I have created a vertical list of the classes in the leftmost column, and a horizontal list by student along the top. As students complete each class, the intersecting cell will show an X. Yes, i could do it daily by hand but i'd like to automate it by copying the daily report data into another worksheet in my Master Progress excel file and then using that data to place X's in the proper cell.

I want to assign each cell of that Master Progress spreadsheet to search the daily data confirming a satisfied test completion. ie, in the intersecting cell for class123456 (vertical list) and student Joe Smith(horiz list) there should be a cell which will scan the Daily report looking for a row showing 123456 in the first column (class#) and "Joe Smith" in the second column (name). If found, that means joe passed that class and the cell in the corresponding cell of my master progress sheet should display an "X". Maybe this is a visual basic issue, maybe not. Please hekp. Thanks.

 

[SkipVought]

hi,

assuming that your Master Progress table starts in A1, where A2 is the first Class Number and B1 is the first Student Name, using Named Ranges...
[tt]
B2: =SUMPRODUCT((Class_number=$A2)*(students_name=B$1))
[/tt]
copy down/across thru all rows/columns of data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

Hmmm. OK, I'm not 100% sure I understand. I Will make the 2 named ranges as you suggest. Could you possibly check the cell content as I think you had a typo. Is it possible you could answer in a bit more detail since I would love this to work and I'm not very good with excel. What should the ranges be called and where do you reference them?

 

[SkipVought]

[tt]
Class number students name
123456 Joe Smith
1234 John Smith
1234 Joe Smith
[/tt]
Select data and headings.

Insert > Name > Create > Create name in TOP row.

Adjust this range using the above process, each time your source data changes.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also FYI, faq68-1331

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

Skip,
You are a saint. Please see my mediafire page and look at the excel file called "what I am trying to do". That may make more sense. Thanks a mil

 

[SkipVought]

Do to company network security, I am unable to access your file.

As you see, I posted a short example of data on my sheet. Can you do likewise?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

This is the data I get from my daily report:
001150 Cheryl
001150 Louis
001150 Mary
001200 Cheryl
001200 Fred
001300 Cheryl
001300 Mary
001300 Wanda
001350 Cheryl
001350 Loius
001350 Mary

This is what Id like the progress sheet to look like:

Cheryl Fred Louis Mary Wanda
001150 X X X
001200 X X
001250
001300 X X X
001350 X X X
001400
001450



I'm trying to format the cells such that the X's appear automatically. If this can be dome, please be very clear and detailed. As it is I will need my IT guru to help me put your reply into action. Thanks!

 

[SkipVought]

My results using the formula I posted...
[tt]
Cheryl Fred Louis Mary Wanda
001150 1 0 1 1 0
001200 1 1 0 0 0
001250 0 0 0 0 0
001300 1 0 0 1 1
001350 1 0 0 1 0
001400 0 0 0 0 0
001450 0 0 0 0 0
[/tt]
use a simple IF function to return an X rather than a 0 or 1


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

Skip,
I consider myself pretty swift but I am lost. Yes, it seems that you solved my problem but I have no idea what you did to get there.

I can find no "Insert, Name" option

I understand if at this point you give up on me. The other option is to take a moment and tell me step by step what to do. Can you send an email to tdellaporta at customgroupusa dot com

I know its frustrating to soar among turkeys but it stinks to be this close and not get it.

 

[SkipVought]

What version of Excel, please?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

i GUESS THE BIG PROBLEM IS THAT i DONT SEE THE FORMULA YOU ARE REFERRING TO. Yes, The results look perfect!

 

[tdellaporta]

Excel 2007

 

[SkipVought]

In the post of 28 Jul 10 13:41
[tt]
B2: =SUMPRODUCT((Class_number=$A2)*(students_name=B$1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

skip,
So is it a typo that you say $A2 but B$1 ?
Also, Using that formula from the = in all the target cells should help huh? What about ranges? Which cells and what range names?

 

[SkipVought]

So is it a typo that you say $A2 but B$1 ?

It is NO typo.

The formula is in B2.

A2 is the class nbr IN THAT ROW. No matter what cell will reference column A

B1 is the student name IN THAT COLUMN. No matter what cell will reference row 1.

Also, Using that formula from the = in all the target cells should help huh?

It's NOT a formula unless it starts with =.

Which cells and what range names?

Not sure what you mean, but past the forumla in B2, then COPY B2 and PASTE into ALL data cells to right of column A and below row 1.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tdellaporta]

Skip,
Thanks very much for your help. I see what you are trying to do.