Look-up specific text and return a list of the satisfied text

**careng** · 03-04-2013, 08:04 PM

Hello,

I am new here, so please forgive me if this post is not what you are used to seeing.

Here is my problem:

In Sheet 1, I have a list of 200 road names and their corresponding maintenance scheduled for that year. Sheet 1, A1 has the text "Road Names". And the names of each road are from A2 to A201. Sheet 1, B1 has the text "Maintenance Type". And the type of maintenance is listed in each cell from B2 to B201. These types are as follows: CF, CS, SS, PO, D or "-" if there is no maintenance required.

What I am trying to do is summarize this data on another sheet.

I have Sheet 2 with a separate column for each maintenance type, CF (in Sheet 2 A1), CS (in Sheet 2 B1), SS (in Sheet 2 C1), PO (in Sheet 2 D1), D (in Sheet 2 E1)
Currently what I have done for example, under the CF column in Sheet 2, in cell A2 is enter the following formula:
=IF(Sheet 1!$B2="CF",Sheet 1!$A2,"-")

I have copied this formula down to A201, therefore it has returned the road name in that column for any road that has the maintenance type of "CF" and put a "-" in the cells that do not. This is all fine, but what I want to try and accomplish is eliminate all the "-" and just have a summary or list.

Where I can have a column in Sheet 2 of "CF" and under this column, only the road names that are have a "CF" next to them in Sheet 1 will be displayed.

For example, let's say there are 18 roads that have a CF, A1 of Sheet 2 would have "CF" and A2 to A19 of Sheet 2 would list the road names that have a "CF" next to them from Sheet 1.

I hope that isn't too confusing.

Anyone have any idea if this is possible, or a function/formula that could make that work?

I have attached an example of what I'm working on. Sheet 3 has what I am looking for. I manually typed that up, but am trying to determine if there is a formula/function that can automatically do that.

Thanks much for your help.

**Tank997** · 03-04-2013, 08:13 PM

Have you tried converting the data on sheet 1 to a table and use the filter feature to obtain your lists for each maintenance type?

**alansidman** · 03-04-2013, 09:09 PM

Have you considered doing this with a pivot table?

**bebo021999** · 03-04-2013, 09:39 PM

First, Place the cursor at any cell of A column (i.e A1), press Ctrl-F3 (defined name)
Name: Data
Refer to:

Please Login or Register  to view this content.

Type in A2:

Please Login or Register  to view this content.

Confirm with holding Ctrl-Shift, then hit Enter.
Fill down and accross.

**careng** · 03-06-2013, 01:13 PM

Wow, Thanks bebo021999! That is exactly what I was looking for. It took me some time to figure out how to incorporate that into my actual "working" spreadsheet, but I figured it out using your instructions.

Thanks again.

Amazing what Excel can do.