Can I do this in Excel?

[Henry@Guild]

I've laid out a sheet below. I hope the layout isn't lost when I post. I am setting up an Excel 2000 sheet for a tv prod. co. They want to assign serial #s (ET001, ET002,...) to clips, assign values in the column below the ser #s (an X in the cell corresponding to the row headings below - Day, Night, etc ?) & search based on those values by putting another X in the column B cell to the right of the relevant row headings cell.


For instance the following ser.#s/clips are marked as:


ET001 Night, Sad and News

ET002 Night, Funny and Sports

ET003 Day and Weather

ET004 Night, Funny and News


The values required are Night & Funny. The srch values are inputted using an X in the column B cell to the right of the row headings. As srch crit are entered the # of relevant matches would start totaling in the cell directly below the ser. #s. When done you would see which clips have the most matches. In this case the relevant matches would be ET002 & ET004 with 2 hits each and ET001 with 1 hit.


Serial Number ET001 ET002 ET003 ET004

Relevant Matches

Day

Night

Funny

Sad

News

Weather

Sports


Thanks in advance.

 

[Hui]

Henry

This could possibly be done,

But I would recomend that you would be better to seperate the criteria

Don't allow people to have combined criteria,


ie Have Columns as

Serial Day Night Funny Sad News Weather Sport etc

ET001___y__________y

ET002___y____________________Y_____Y

ET003________Y__________Y_________________Y


It would then be easy to use a Sumproduct to extract and sum up the cells as you input different x's Or you could possible setup a pivot table to do the same