Build Summary (top10) from Raw Data - Sort and Filter

**exc4libur** · 03-09-2011, 12:11 PM

Hello,

I have a worksheet that is updated daily, containing:

Price Changes/Variation (%)
Financial Volume
Quantity Traded
etc..

Everyday I have to post top financial gainers and losers by Price (%), Financial Vol, and Quantity, listed in a different worksheet.

How can I do this using vba?

I`ve attached a workbook example, with only raw data and the summary sheet.

Thanks in advance for any help!

**JBeaucaire** · 03-09-2011, 12:26 PM

In B5 then copied down:
=INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!D:D,ROW(A1)),Sheet2!D:D, 0))

In D5 then copied down:
=INDEX(Sheet2!B:B,MATCH(SMALL(Sheet2!D:D,ROW(A1)),Sheet2!D:D, 0))

In B11 then copied down:
=INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!E:E,ROW(A1)),Sheet2!E:E,0))

...etc.

**exc4libur** · 03-09-2011, 12:44 PM

It works fine, but I need it in VBA

**exc4libur** · 03-09-2011, 08:57 PM

Originally Posted by exc4libur

Not working J.
Also, can I type this formula to my vba? Like a function - Create new wks then summarize top 10 columns from Raw Data Worksheet.

Yeyyy, it worked! I had to substitute "," for ";".

Anyhow, could you still help me, I would like to type this in a vba module!

For example: I want a rank containing top gainers/losers listing company name and what was the price change or quantity traded.. Is there somehow?

thks

**JBeaucaire** · 03-09-2011, 09:32 PM

If it's the exact sheet/layout you posted, just turn on the recorder and let it record you putting in the formulas. Why do you need VBA for something as simple as you've shown?

**exc4libur** · 03-09-2011, 10:21 PM

Originally Posted by JBeaucaire

If it's the exact sheet/layout you posted, just turn on the recorder and let it record you putting in the formulas. Why do you need VBA for something as simple as you've shown?

I wanted to make everything automated.. But nevermind, its sheer human curiosity!

Hehehe, thanks, youve helped me enough!

Best rgds

**exc4libur** · 03-09-2011, 10:27 PM

Thnks for all the help!

This formula below ranks data by
top gainers
=INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!E:E,ROW(A1)),Sheet2!E:E,0))
top losers
=INDEX(Sheet2!B:B,MATCH(SMALL(Sheet2!D:D,ROW(A1)),Sheet2!D:D, 0))