How can I extract filtered values from a table?

[Bpuad]

Well I have a list with names and countries:

Country Name
US Ben
UK John
UK Liza
FR Karen
US Carl
FR Sophie
US Anders

Now I sort this list according to coutry. In another sheet I want to extra the data in this list, but only the data that is sorted. So let's say I sort the list on US I only want the names that is filtered/US to show in the other list?

Can this be done. Have problems with this, help would be highly appreciated.

/Per

 

[Akashwani]

Hi,

Can you use a "Helper" column?
If so, try this.....

Sample Data...

Excel Workbook
	A	B	C	D
1	Country	Name	Helper	*
2	US	Ben	1	*
3	UK	John	1	*
4	UK	Liza	1	*
5	FR	Karen	1	*
6	US	Carl	1	*
7	FR	Sophie	1	*
8	US	Anders	1	*
9	*	*	*	*
Data2

The formula in C2 needs to be copied down.

Example Results1....

Excel Workbook
	A	B	C	D
1	3	Country	Name	*
2	3	US	Ben	*
3	*	US	Carl	*
4	*	US	Anders	*
5	*	*	*	*
Results2

Example Results2...

Excel Workbook
	A	B	C	D
1	2	Country	Name	*
2	2	FR	Karen	*
3	*	FR	Sophie	*
4	*	*	*	*
Results2

Results without filtering being applied....

Excel Workbook
	A	B	C	D
1	7	Country	Name
2	7	US	Ben
3		UK	John
4		UK	Liza
5		FR	Karen
6		US	Carl
7		FR	Sophie
8		US	Anders
9
Results2

The formula in B2 needs to be entered with ctrl shift enter NOT just enter, you can then copy iy across and down.
You will obviously need to change the sheet name and cell references to suit your layout.
The formula in A2 is not required as I used this as a check.

If this is not suitable, then you may want to look at using Advanced Filter.

I hope that helps.

Good luck.

Ak

 

[Bpuad]

Awesome AK, thanks a lot! You're my hero today

/Per

 

[Akashwani]

Hi,

You are welcome, I'm pleased it worked for you and thanks for the feedback.

Ak