Drag and drop a formula down a single column

**rhudgins** · 07-22-2010, 01:41 PM

In B4 is the word "T Equity". I need a macro to isolate "T" and copy it from A6:T217. Then do the same for the next example "MSFT Equity" in B219 and isolate MSFT and copy it from A221:A256. Do the same form WMT Equity in B258 ect... (More examples will be added over time).

In column A, I have the formula =LEFT($B$4,SEARCH(" ",$B$4)-1) that demonstrates exactly how I would like the data to look. Eventually I am going to have hundreds of these examples and need a macro to perform this task easily.

Can anyone help me out with this? Thanks

**ChemistB** · 07-22-2010, 01:52 PM

This formula can be entered into A6 and dragged down through infinity.

=IF(OR(ISBLANK(B6),ISTEXT(B6)),"",IF(AND(ISNUMBER(B6),ISTEXT(B5)),LEFT(B4,SEARCH(" ",B4)-1),A5))

Does that work for you?

**rhudgins** · 07-22-2010, 02:00 PM

Thanks this is perfect!

On more question. I need a macro to scan through column I, and if there is a "1" then copy the row with the "1" in it and also copy one row below and paste both rows into a new sheet. Is it possible for this to be done?