Extracting Data from a table

[Agbarker]

Hi all,

Looking for some help on extracting data, I have a relatively large table A1 to P272, columns are either names, value (£'s) or %'s

What i am looking to do is on another sheet have the top 30 for each category - snippet of example data below

Name	Stage 1 count	Stage 1 Total Value	Stage 1 Average Value	Conversion Rate Stage 1 to Stage 2	Stage 2 Count	Stage 2 Total Value	Stage 2 Average Value	Conversion Rate Stage 2 to Stage 3
Annabella Mendoza	4	£26,240,000	£6,560,000	100%	4	£26,240,000	£6,560,000	25%
Dominic Randolph	1	£2,000,000	£2,000,000	100%	1	£2,000,000	£2,000,000	100%
Kailey Moreno	1	£900,000	£900,000	0%	0	£0	£0	0%
Myles Stuart	1	£30,000,000	£30,000,000	100%	1	£0	£30,000,000	0%
Stormi Warner	1	£1,000,000	£1,000,000	0%	0	£0	£0	0%
Jaxton Maldonado	2	£850,000	£425,000	0%	0	£0	£0	0%
Elaina Golden	2	£2,500,000	£1,250,000	50%	1	£1,500,000	£1,500,000	0%
Amias Vu	1	£1,000,000	£1,000,000	0%	0	£0	£0	0%
Kimora Larsen	2	£26,500,000	£13,250,000	100%	2	£26,500,000	£13,250,000	100%

For Example

Top 30 - Stage one Count - Showing Name (Column A) and Stage one Count (Column B) - Results Sorted by Column B, High to Low - with a minimum of Stage one count being 2

Results would be

Name (Column A), Stage one Count (Column B)

another could be

Top 30 - Stage 2 Average value, when Stage 1 count is greater than 4 - Sorted by Stage 2 average value

Results would be

Name (Column A), Stage one Count (Column B), Stage 2 Average Value

There will be more for me to do but this should hopefully get me on the right track with your wonderful help

Many thanks

Ash

 

[JamesCanale]

I would do this:

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Stage 1 count	Stage 1 Total Value	Stage 1 Average Value	Conversion Rate Stage 1 to Stage 2	Stage 2 Count	Stage 2 Total Value	Stage 2 Average Value	Conversion Rate Stage 2 to Stage 3
2	Annabella Mendoza	4	£26,240,000	£6,560,000	100%	4	£26,240,000	£6,560,000	25%
3	Dominic Randolph	1	£2,000,000	£2,000,000	100%	1	£2,000,000	£2,000,000	100%
4	Kailey Moreno	1	£900,000	£900,000	0%	0	£0	£0	0%
5	Myles Stuart	1	£30,000,000	£30,000,000	100%	1	£0	£30,000,000	0%
6	Stormi Warner	1	£1,000,000	£1,000,000	0%	0	£0	£0	0%
7	Jaxton Maldonado	2	£850,000	£425,000	0%	0	£0	£0	0%
8	Elaina Golden	2	£2,500,000	£1,250,000	50%	1	£1,500,000	£1,500,000	0%
9	Amias Vu	1	£1,000,000	£1,000,000	0%	0	£0	£0	0%
10	Kimora Larsen	2	£26,500,000	£13,250,000	100%	2	£26,500,000	£13,250,000	100%
11
12
13
14
15
16	Name	Stage 1 count		Name	Stage 1 count	Stage 2 Average Value
17	Annabella Mendoza	4		Annabella Mendoza	4	£6,560,000
18	Jaxton Maldonado	2
19	Elaina Golden	2
20	Kimora Larsen	2
Sheet20

Cell Formulas
Range	Formula
A17:B20	A17	=TAKE(SORT(FILTER(A2:B10,B2:B10>=2),2,-1),30)
D17:F17	D17	=TAKE(SORT(FILTER(CHOOSECOLS(A2:I10,1,2,8),B2:B10>=4),3,-1),30)
Dynamic array formulas.

If you want to take more than 30 if the values are equal (a bunch tied for 30th place) - that could be sorted out too.

 

[Agbarker]

This is Amazing! thank you so much!.

QQ - with the formula =TAKE(SORT(FILTER(CHOOSECOLS(A2:I10,1,2,8),B2:B10>=4),3,-1),30)

how would i add another criteria, so B2:B10>=4 is the same & I2:I10>=25% for example?

 

[JamesCanale]

In the FILTER Expression where you have B2:B10>=4 - you can have a long string of conditions multiplied together for AND - you can't use the AND function though. That turns it into one answer instead a vector of answers.

So B2:B10>4 becomes "(B2:B10>4)*(I2:I10>=.25)".

You must know that TRUE in excel is '1', and false is '0'. Really any number that's not 0 is true. So multiplying them together serves as "AND".

You can do OR also - by adding. I use the SIGN function in that - its not necessary, but it makes me feel better.
So "SIGN((B2:B10>4)+(I2:I10>=.25))" is OR because the function results in 0 (if they are both false), 1 (if exactly one is true), or 2 (if they are both true). I use SIGN to make it 0 or 1. But it will work without that.

You can mix ANDs and ORs with judicious and often confusing use of parentheses.

 

[Agbarker]

Ah ok, that makes sense, so the final formula would be

=TAKE(SORT(FILTER(CHOOSECOLS(A2:I10,1,2,8),SIGN((B2:B10>4)+(I2:I10>=.25)),3,-1),30)

or is that too many brackets?

you have absolutely saved me a lot of time of time with this!!!! Thankyou

 

[JamesCanale]

It looks right, but keeping track of parentheses is hard - I use in-cell formula carriage returns (Alt-Enter) to see complicated formulas. See if it works.