Search/Find, Pull information into one cell

[PowerSchoolDude]

So I'm working on a report and I can't seem to find a formula to meet what I want.

I have a huge export that contains the following information
Column A: Student Name
Column B: Entry Date
Column C: Type of Entry

The only problems I'm having is having Excel search a huge export and put the dates in a cell for every instance the student has a certain type of update. For example, in the Wave Advocates Update column, I want the date to pull up every single date that a certain student has had a "06-Wave Updates". The students have about 8 different type of entries and the total number of entries is about 1700 for 60 students.

The Students Name is manually entered in column A of the "reports" tab, so essentially I want to look at the name in that row and go to the "export" tab where all the information is and look for every row with that students name & "06-Wave-Updates", then take the Entry Date from those rows and combine them into one cell in the report.

Sorry I repeated myself twice, but I have a hard time explaining things without being confusing. ><


Please Help!! Thanks

- Santana

 

[Ziggy12]

Think I'd pivot the data.

Add a new col A to flag instances of Wave Updates

Insert a pivot table on the reports tab or where ever with the students name and the wave update flag in the page field and wave update date in the row field. Put anyhting else that each student has in the data field and change that to count if it isn't already.

Select the flag and the students name and the pivot should return the dates of each wave update.

Then concantenate the dates returned by the Pivot table into one cell on your reports worksheet.


cheers
ziggy

 

[PowerSchoolDude]

Pulling data from "C" when "A" & "B" match specific criteria

There was a similar question asked earlier and I was wondering if I can get help with this as I haven't been able to tweak the response from the other poster enough for it to work with my report.

Here's is what I'm hoping to do:

If student name match on column A of "Tab 1" and "Tab 2", then look for "Meeting" in column B of "Tab 1" and pull over "Entry Date" next to it in column C of "Tab 1" into column B of "Tab" 2

This is how Tab 1 would look:
A B C
Name 1 Meeting 09/08/2008
Name 1 Detention 09/09/2008
Name 1 Meeting 10/01/2008
Name 2 Meeting 10/02/2008
Name 1 Detention 11/03/2008
Name 1 Meeting 12/15/2008

What I want Tab 2 to look like:
A B C
Name | Meetings (Date) | Detentions (Date)
Name 1 | 09/08/2008, 10/01/2008, 12/15/2008 | 11/03/2008
Name 2 | 10/02/2008 |


What I want is for every instance that a student has a "Meeting", then to put those dates into one cell.

The name of the students will be manually entered into "Tab 2" and each student will have just one row.

Please help. I'm bad at explaining things so if I was confusing I can try and clarify.

Thanks!

- Santana

 

[xenou]

What's wrong with just sorting the list by name, type, and date? You don't have it "all in one cell", but you do have it 1) all in order and 2) easily viewed and summarized.

<img alt="worksheet" src="http://northernocean.net/etc/mrexcel/20090711_students.png" />

 

[PowerSchoolDude]

The format I'd like is our standard report format. This report I'm creating is to audit what information we have in PowerSchool, which is the program we use to keep track of student progress and info. It's easier to spot cells with missing information when all the student information is in one row.

Also, it's more printer friendly & easier to read for some who are not Excel literate.

 

[xenou]

Hmmm. I see. It appears this would require some advanced Excel skills, then - which is basically why I asked - the report format you desire seems easier to you but it definitely appears harder to me! Are these "06-Wave Updates" part of this report?

 

[xenou]

Ridiculously awkward, but I came up with something here using a few CSE formulas together with helper columns, concatenation -- and a bit of determination. A least, the final values can be copied and pasted into the final report (you'd filter out the blanks).

<a href="http://northernocean.net/etc/mrexcel/20090712_foo.xls">Sample workbook</a>

Hopefully someone can show me how to pare this down into something more manageable - I'm not the best with this kind of formula creation.

<img alt="picture of worksheet" src="http://northernocean.net/etc/mrexcel/20090712_foo.png" />