Excel 2007 : data extraction

Hello,

I am having trouble will excel. What i want to achieve,

(See attached file)

Our system at work does a data dump in excel (data tab). I want to be able to present the infornation in other tabs.

In the (Potential Incident tab) i would like to be able to extract data from the "data tab" and insert in, but i only what the rows with the words "Potential Incident" inserted.

I have played around with Vlookup, but to no success. I have 2007, but will also need to work in 2003

Thanks if any one can assist.

Cheers
Andrew

I am sure someone will offer a hefty array formula, but I prefer to avoid them. So, in your situation I would add an INDEXING KEY to the end of the data to make realtime display of the lists on the other pages fast and array-free. I opted to grab the first letter in column B.

In F2 and copied down I used:
=LEFT($B2,1)&COUNTIF($B$2:$B2,B2)+1

With the helper column in place you now have a unique value for each row. That makes a simple INDEX/MATCH formula possible on the other sheets. Again, using the key letter and row number, I construct a value to grab from "data" and bring over the matching value from other columns.

In A2 and copied over and as far down the chart as you'd like:
=IF(ISNUMBER(MATCH("P"&ROW(),data!$F:$F,0)),INDEX(data!A:A,MATCH("P"&ROW(),data!$F:$F,0)),"")

It returns the first.

First, it's not looking for a word starting with P. It's looking for "P"&Row(). If that formula is in row 2, then it's looking for P2.

The KEY I added to the data sheet creates as unique ID for each row using the first letter it finds in column B and pairs it with the first instance of that string in column B. So the first time it finds "Potential Incident" is returns P2 (we skip P1). The next time it sees "Potential Incident" it creates the value P3. Clear enough?

Now the formula on "Potential Incidents" sheet can start to grab all the P2, P3, etc values.

As to your question can anything be done if column B on data has more than one "P" string, of course. You just have to plan it.

The simplest answer is to change the string to start with a unique character.

The next simplest answer is to expand the Key to work universally. To do that, return the ENTIRE string instead of just the first letter.

Change the formula in cell Data!F2 to
=$B2&COUNTIF($B$2:$B2,B2)+1
...and copy it down.

Then put this formula in E1 on all the other sheets:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
...This will cause the sheetname to appear in the cell and it can be used in the next formula.

Replace the long formula on the other sheets starting at A2 and copying down and over:
=IF(ISNUMBER(MATCH($E$1&ROW(),data!$F:$F,0)),INDEX(data!A:A,MATCH($E$1&ROW(),data!$F:$F,0)),"")

Now, all you have to do is make sure the sheets are named EXACTLY the same thing as the strings used in column B, like you did on the first two sheets in this book. This change in approach has the added benefit of making the sheets templates of themselves. All you have to do is duplicate a sheet and give it an accurate name and it will immediately work with no other changes on the sheet itself.


Clear enough?

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Originally Posted by adedman

thanks every one, your advice has worked perfect. One more thing, what if i want pick up a row with a certin word (i have this formula from you guys above) and then sort again to narrow the field down.

No idea what that means. Start another thread and fully describe your new issue, or post a sample book demonstrating visually what you're after.