Pull every instance INDEX/MATCH criteria into consecutive rows

**PowerSchoolDude** · 01-11-2010, 05:57 PM

I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.

I've attached a mock report up so that maybe it would make more sense.

So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.

Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.

So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach.
Find the next instance Name & # matches, and pull that into rows 12-16, etc.

I tried to explain as best as I could, but hopefully someone understood that big jumble I've written up and could help. Any help would be greatly appreciated. Thanks!

**DonkeyOte** · 01-12-2010, 04:47 AM

PSD, I have had a quick look and though I'm sure you can streamline the DIR sheet I will concern myself only with Outreach sheet for now.

You make mention of Outreach No. as criteria for search yet I find no immediately obvious location where this criteria is set on Dir sheet so I am ignoring this and matching on name basis only for time being.

To avoid "expensive" formulae the best thing to do is to create a key on Paste Schedule sheet related to each value in Column A, eg:

If we assume data is not always sorted by Column A then

Code:

'Paste Outreach'!I2: 
=$A2&":"&COUNTIF($A$2:$A2,$A2)
copied down

You can use this key on the Outreach sheet to avoid Arrays...

Next thing to do on Outreach sheet would be to create some cells to hold various values which would allow us to avoid repetitive / needless calculations in the main report:

Code:

Outreach
C1: =COUNTIF('Paste Outreach'!$A:$A,DIR!$D$1)
'count of records to be returned...

C7: =MOD(ROWS(C$7:C7)-1,5)
'Mod identifer which can be used to determine column, string prefix etc...

D7: =IF($C7,$D6,MATCH(DIR!$D$1&":"&COUNTIF($C$7:$C7,0),'Paste Outreach'!$I:$I,0))
'establishes row in which record of interest appears on 'Paste' sheet

C7:D7 copied down to row 501

(note these formulae need not be in C:D - just for demo purposes - your existing D formula can be removed)

Remaining formulae become:

Code:

'Paste Outreach'!A7: 
=IF(COUNTIF($C$7:$C7,0)>$C$1,"",CHOOSE(1+$C7,"","Type: ","Reporter: ","Grades/Classes: ","Attendance Status: ")&TEXT(INDEX('Paste Outreach'!$A:$H,$D7,CHOOSE(1+$C7,5,4,2,7,8)),IF($C7,"@","dd/mm/yyyy")))
copied down to A501

'Paste Outreach'!B7:
=IF(COUNTIF($C$7:$C7,0)>$C$1,"",INDEX('Paste Outreach'!$F:$F,$D7))
copied down to B501

point of the above being to use one formula for all cells in same column - should ease burden of maintenance.

**PowerSchoolDude** · 01-12-2010, 06:11 PM

Thank you so much! it actually worked great.

I was playing around with it a bit and was trying to see if I can adjust the formula in the first box so that I can have it Count if A2 (the name) appears but also if it has a specific code. The outreach # is a way to seperate different types of logs in this report. In my workbook "90' indicates an outreach log, "92" indicates an intervention log. I have created an 'Intervention' tab similar to the 'Outreach' one (hidden in the workbook), but has completely different information on it.

So essentially what I would like to be to do is to seperate each row by student, but as well by the type of log. Would that be possible? This formula is amazing when I only have outreach logs, but I also have the intervention ones that would not fit in the 'Outreach' tab, but they do in the 'Intervention' tab.

I hope this makes sense. This formula alone you have provided has helped sooo much and I'm sure it's going to make many happy that this report is easier to run. Thanks!

**DonkeyOte** · 01-13-2010, 05:45 AM

You can easily adapt this such that the key is based on both name & outreach however per my note I could not see where you were setting this 2nd value on the DIR sheet ?

In terms of how... given use of XL2007 the simplest solution is to use COUNTIFS rather than COUNTIF, eg:

Code:

'Paste Outreach'I2: 
=$A2&":"&$C2&":"&COUNTIFS($A$2:$A2,$A2,$C$2:$C2,$C2)
copied down as required

Then simply modify Outreach sheet such that:

Code:

Outreach!C1
=COUNTIF('Paste Outreach'!$I:$I,DIR!$D$1&":"&90&":*")

Outreach!D7
=IF($C7,$D6,MATCH(DIR!$D$1&":"&90&":"&COUNTIF($C$7:$C7,0),'Paste Outreach'!$I:$I,0))
copied down to D501

the references to 90 should be to the cell in which this criteria is specified.. unknown

all other formulae remain as they are.

**PowerSchoolDude** · 01-13-2010, 06:42 PM

I played around with it and it worked out perfectly!

You have no idea how much time everyone is going to save with this simple formula you've given me. Thank you so so much!