Returning the values of filtered items on another sheet

[tangcla]

So I have a sheet - say for example, like this:

Am Samoa	Manchester tanks 10*210kgs
Am Samoa	9kg & 4.5kg cylinders Apr order
Am Samoa	Mooring Bollard Part2
Fiji		Motor Vechicle Toyota Hilux for team leader in North
Samoa		Apia terminal
Samoa		Cylinder Piercer
Solomons	New IT SERVER
Solomons	New S&I Office Extension - WIP
Solomons	Solomons Pacific shipping hoses
Tonga		Stairways on tanks
Tonga		Air & gas cylinder filling scales
Vanuatu		ISO tank upgrade
PNG		Pacific shipping hoses
PNG		Feb'12 Cylinders orders + valves
Vietnam		12kg & 45kg cylinders
Am Samoa	Radio Equipment Aua Terminal
Am Samoa	Tafuna Warehouse
Fiji		Time Clock- need to be expense
Fiji		12kg cylinders*588

Is it possible to have a table of formulas, which will show me all the values in the second column of each country (e.g. Fiji)?

The end result will be like this - I'm hoping to be able to do it so I don't need to manually filter and copy rows each month.

Fiji		Motor Vechicle Toyota Hilux for team leader in North
Fiji		Time Clock- need to be expense
Fiji		12kg cylinders*588

[tangcla]

In my head I'm thinking it might be possible to do this with an initial lookup, and then subsequent rows with an index that starts at Motor Vechicle Toyota Hilux for team leader in North to continue with a second lookup. Clunky, but is there a better way for this?

[Tony Valko]

See this example:

http://www.excelforum.com/excel-form...ell-value.html

[tangcla]

See this example:

http://www.excelforum.com/excel-form...ell-value.html

Thanks Tony, I don't understand with your example, the use of column E- for me I want to return the text in column B if column A = "Fiji".

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E
2	Am Samoa	Manchester tanks 10*210kgs	-----	Fiji	Motor Vechicle Toyota
3	Am Samoa	9kg & 4.5kg cylinders Apr order			Time Clock- need to be expense
4	Am Samoa	Mooring Bollard Part2			12kg cylinders*588
5	Fiji	Motor Vechicle Toyota
6	Samoa	Apia terminal
7	Samoa	Cylinder Piercer
8	Solomons	New IT SERVER
9	Solomons	New S&I Office Extension - WIP
10	Solomons	Solomons Pacific shipping hoses
11	Tonga	Stairways on tanks
12	Tonga	Air & gas cylinder filling scales
13	Vanuatu	ISO tank upgrade
14	PNG	Pacific shipping hoses
15	PNG	Feb'12 Cylinders orders + valves
16	Vietnam	12kg & 45kg cylinders
17	Am Samoa	Radio Equipment Aua Terminal
18	Am Samoa	Tafuna Warehouse
19	Fiji	Time Clock- need to be expense
20	Fiji	12kg cylinders*588

This array formula** entered in E2 and copied down until you get blanks:

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$20=D$2,ROW(A$2:A$20)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[tangcla]

I'm not sure if we're on the same page... I want to extract the rows which are Fiji (or Am Samoa, or PNG, doesn't really matter) into a table, the Fiji label will be a lookup (say D2) so I want the formula, which will be in cells E1-E20, to populate with whatever is in country D2.

[Tony Valko]

Hmmm...

That's what the formula is doing except that it's not repeating Fiji (or whatever) for each row of data.

We already know the data is related to Fiji so why repeat it for each row of data?

[tangcla]

Can't get it working

FWIW - columns D and E in your example are on another worksheet.

For me, D1 is a fixed cell for the worksheet, and column E is what I am wanting the array/formula to generate for me. There's a sheet for each location (Am Samoa, Cooks, PNG etc).

[Tony Valko]

Can you post a SMALL file that shows how/where your data is and what result you expect?

A SMALL file is about 20 rows worth of data. We just need an accurate representative sample of what you have and what you want. Files with rows/columns full of irrelevant data just make understanding the problem more difficult.

[tangcla]

Can you post a SMALL file that shows how/where your data is and what result you expect?

A SMALL file is about 20 rows worth of data. We just need an accurate representative sample of what you have and what you want. Files with rows/columns full of irrelevant data just make understanding the problem more difficult.

Was just prepping the file

So in the "Am Samoa" sheet, I want the formula to populate in cells C31:C38, from "A - Data" columns A and C.

Asiapac.xlsx

[Tony Valko]

Sorry, but I can't download files >50kb.

[tangcla]

File above updated.
Had to remove all formatting to get it to be under 50KB

[Tony Valko]

Try this...

On the Am Samoa sheet...

Enter this array formula** in C31:

=IFERROR(INDEX('A - Data'!C:C,SMALL(IF('A - Data'!A$3:A$26=C$1,ROW('A - Data'!A$3:A$26)),ROWS(C$31:C31))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to C38.

[tangcla]

Try this...

On the Am Samoa sheet...

Enter this array formula** in C31:

=IFERROR(INDEX('A - Data'!C:C,SMALL(IF('A - Data'!A$3:A$26=C$1,ROW('A - Data'!A$3:A$26)),ROWS(C$31:C31))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to C38.

Thanks! Perfect, + rep.

[Tony Valko]

You're welcome. Thanks for the feedback!