Index Match Match????

[jo15765]

I have created a behavior chart for a few students of mine, and on All worksheet I place an X in the applicable items under the applicable date. Then on the Count Worksheet I sum the "X" for the day for each student. I have been doing this manually but it is becoming quite arduous a task now that the data has grown exponentially. Can someone please provide a formula to accomplish such with my attached workbook?Book1.xlsx

[TMS]

This works for your test data, assuming that there is one row for each student and the order is the same:

Please Login or Register  to view this content.

Maybe a short term solution until you get something better


Regards, TMS

[mehmetcik]

Enter this formula into B2 and fill down and to the right.

Please Login or Register  to view this content.

[jo15765]

And which element would I need to alter to show the date of 01/02/2015?

[mehmetcik]

Enter this into B1 and fill right

Please Login or Register  to view this content.

[TMS]

To whom are you addressing your question?

[jo15765]

To whom are you addressing your question?

Oh sorry I didn't realize I had 2 responses so promptly. It was originally addressed towards you @TMS

[jo15765]

Enter this formula into B2 and fill down and to the right.

Please Login or Register  to view this content.

This does not appear to be working properly for me. If I enter this on sheet2 B3 it returns 4 where that is not accurate. If I input it into B2 and it returns 4 which is accurate.

[José Augusto]

Hi
Use these formula in Count!B1 and copy right
=OFFSET(All!$B1,0,(COLUMN()-2)*7)

Please Login or Register  to view this content.

see the file Book1 (12).xlsx

Regards

[jo15765]

Hi
Use these formula in Count!B1 and copy right

Please Login or Register  to view this content.

see the file Attachment 429815

Regards

So I can understand, how does this formula work? Can you break it out for me?

[mehmetcik]

You must put the formula in B2 and fill it right

The action of filling it modifies it.

Please Login or Register  to view this content.

you want the formula in your second sheet to move its focus 7 columns on the all sheet with every 1 column moved on the results sheet

so if your the first column you are putting your formula in is B ie 2

then Column()-2 =0 then * 7 = 0


the second column you have a formula in is C or 3
then Column()-2 =1 then * 7 = 7


In the first example we offset or move 0 columns to the right of B1

In the second we move 7 columns to the right of B1

in each case that gives us the date.

[TMS]

Please see the attached example.


Regards, TMS

[José Augusto]

So I can understand, how does this formula work? Can you break it out for me?

Hi jo15765

OFFSET shifts the cell reference a specified number of rows and columns to obtain a new reference cell. So we can move from [B1], 0 rows and 7 columns to have the reference to [I1] cell cell.
COL () is the number of match cell column in which case 2. The product (COL () - 1) * 7 produces the desired displacement as when copied to the side of the column switch to C, i.e. 3, and then to D (4), ...
Sorry for my bad English.

[jo15765]

UGHHHH....So sorry for this mates, I just was informed the requirements for this changed. I am adding an updated workbook of the output I need it to be...TEst.xlsx

[TMS]

Suggest you still have just one sheet to summarise the counts. Add a column for Teacher and use a VLOOKUP to populate that. Then use AutoFilter to filter on Teacher. That way, you can add whatever other criteria you want, maybe Classroom, and do the same thing.

If you start having separate sheets for each teacher, it's going to get messy and very difficult to manage. First problem, list students in the sheet based on teacher, then adapt the formulae to use some sort of MATCH to get the row number.

I can't help you with this. It would be a mistake, IMO, and just lead to more threads as circumstances change.

[mehmetcik]

Try this sample.

I used four formulas.

A1 =

Please Login or Register  to view this content.

B1 and fill to Right

Please Login or Register  to view this content.

A2 and Fill Down

Please Login or Register  to view this content.

B2 and Fill Down and to the right

Please Login or Register  to view this content.

If you are allowed to use Macros then I could get rid of the blank rows for you

Otherwise you would need to use array formulae which can get really complicated

[TMS]

The formula in cell A1 is volatile. When you select a sheet and press F9 to calculate, the value will change to that of the current sheet. If you select the other sheet, it will have the last value calculated.

Regards, TMS

[mehmetcik]

Try this version

I included a small macro in the teachers sheets that forces a recalculation

Please Login or Register  to view this content.

[jo15765]

Try this version

Both worksheets show data for Teacher B?

[TMS]

Please see the updated example

Regards, TMS

[RedSummer]

I did this a bit differently but as requested, let me know what you think.

Only downside is that you shouldn't merge the dates, but write them on each column (not too bad of a day-to-day task).

The rest is automatic.
Only will do 20 students before needing to change the formulas and can read the added columns indefinitely.

CHeers,
Red

[jo15765]

I did this a bit differently but as requested, let me know what you think.

Only downside is that you shouldn't merge the dates, but write them on each column (not too bad of a day-to-day task).

The rest is automatic.
Only will do 20 students before needing to change the formulas and can read the added columns indefinitely.

CHeers,
Red

YES YES AND YES!!!!! That is what I was needing. How can I alter to hold >20?

[JohnTopley]

Try

=IFERROR(INDEX(All!$A$3:$B$200,MATCH(0,COUNTIF($A$1:$A3,All!$A$3:$A$200)+(All!$B$3:$B$200<>$A$1),0),COLUMN(All!A3)),"")

[jo15765]

Try

=IFERROR(INDEX(All!$A$3:$B$200,MATCH(0,COUNTIF($A$1:$A3,All!$A$3:$A$200)+(All!$B$3:$B$200<>$A$1),0),COLUMN(All!A3)),"")

Will this replace the current formula in cell A2 down in worksheets 2 and 3?

[jo15765]

Try

=IFERROR(INDEX(All!$A$3:$B$200,MATCH(0,COUNTIF($A$1:$A3,All!$A$3:$A$200)+(All!$B$3:$B$200<>$A$1),0),COLUMN(All!A3)),"")

Will this replace the current formula in cell A2 down in worksheets 2 and 3?

[RedSummer]

Yes, changing all the 20s in the formula to 200 will allow you to read down 200 rows per teacher.
The formulas and format I made also happen to allow you to use an identical formula for both sheets so yes, this will work for 2 and 3.
Also,
Remember to us ctrl+shift+enter to add the {} brackets around the formula, turning it into an array formula and allowing it to work properly.
The other formulas that count just need to be dragged down and will work no matter the amount of information you are using.

Red