Can Vlookup do this?

themev

Board Regular
Joined
Aug 13, 2007
Messages
83
I was sent a spreadsheet with three year's of data. The document is broken down by accounts and within each account are rows with transactions that took place for all three years worth of data. For example:

Cash
2009 ... ... ... ... ... ...
2009 ... ... ... ... ... ...
2010 ... ... ... ... ... ...
2010 ... ... ... ... ... ...
2011 ... ... ... ... ... ...
and so on and so forth for multiple accounts. I would like to create a separate sheet with just the information from 2009. Is there any formula or method I could use to pull all of the rows for each account from the year 2009?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Filter the column containing the years for 2009
Copy the results and paste into the new sheet
 
Upvote 0
Try a pivot table with the year in the page by field and then just select 2009 to see only those results
 
Upvote 0
Thanks for the responses. The issue is the Account name is listed once in column B, say B4, followed by rows (sometimes thousands) with the information from columns F to V. Then the next account title is listed in cell B5000.

I think I need to clean up the data first before attempting one of these solutions.
 
Upvote 0
You can easily fill in the blank rows so that it will fit in a pivot friendly format.

In Column B highlight all the cells from B4 to the bottom of your list.

Hit Ctrl-G (Goto)
Alt-S Special
Alt-K Blanks

Click OK.

All the blank rows will now be highlighted.

Type = and hit the up arrow.

Hold Control and press Enter.

All of your blanks will now be populated with the account name.

Highlight this list and copy, then paste special and values to remove the formulas. If you sort while those forumlas are still in there it will mess up the name.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top