WallStreetOasis.com » Forums » Industry Specific » I-Banking Bullpen
wiseguy's picture

Excel question

Lets say I have a table with data. I want to create a sub table that picks rows from the source table depending upon criteria.

What is the best way to create the sub table (so that it does not have empty lines in between, if we were to follow a brute force method of creating)

thanks

No votes yet
yesman's picture

vlookup

vlookup

junkbondswap's picture

or h-look up if you're nasty

or h-look up if you're nasty

wiseguy's picture

hmm

Guess, I am a bit lost - Well Lets say a table is:

A Q1 2005
B Q2 2005
C Q1 2005
D Q4 2005

Now I want to create a table with only Q1's so that it is only two rows:
A Q1 2005
C Q1 2005

How do I use VLOOKUP for this?
Appreciate it

FreeCashFool's picture

RE:

if it's in order so that Q1 rows repeat every 5 lines use the offset function

WxOnWallStreet's picture

Im sure there's a function

Im sure there's a function to do what you need, such as the above mentioned ones, but a pretty simple macro would also do the trick.

captk's picture

VLOOKUP is your answer. Are

VLOOKUP is your answer. Are you really too lazy to Google that?

- Capt K

oversold's picture

you could make a pivot table

you could make a pivot table too

thegeneral101's picture

pivot table would work, but

pivot table would work, but for this VLOOKUP works great...i actually have the VBA book sitting in front of me :)

jmcfadden's picture

=SUMPRODUCT(($A$1:$A$4=$A10

=SUMPRODUCT(($A$1:$A$4=$A10)*($B$1:$B$4=$B10)*C10)

This assumes your new table starts in row 10 and the combinations in columns A and B are unique.

Or you can concatenate columns A and B, then use vlookup.

untilted's picture

there;s something called

there;s something called MVLOOKUP.

google it, put it in the macro. it's like vlookup but it lets you return multiple entries.