Altering a Vlookup to shift cells up if text matches a certain value

Hi guys,

I have quite a complicated request which may not even be possible (requiring the use of VBA maybe)!

Basically, I've inherited a spreadsheet which runs a vlookup on an order number to pull out details of a trade (name the name of the stock, the quantity bought/sold and some additional info).

For example:

Order 1
Cell A1: AAPL US
Cell A2: 10000
Cell A3: Tech

Order 2
Cell B1: VOD LN
Cell B2: 20000
Cell B3: Telecoms

Etc.


For stocks that have a name ending in RR (the above two examples end in US and LN respectively), is there a way to get Excel to not only not pull the details of the trade but to also 'delete' that row (so that there are no blanks).

For example, if Order 3 and 4 are normally:

Cell C1: TOK RR
Cell C2: 10000
Cell C3: Food

Cell D1: HSBA LN
Cell D2: 12000
Cell D3: Bank


Can I get it so that Order 3 is ignored and Cells C1 - 3 populate as:

Cell C1: HSBA LN
Cell C2: 12000
Cell C3: Bank


I hope that makes sense!

Hi,

Thanks for the reply. Due to confidentiality issues, I can't attach a workbook. Basically, I currently have a normal vlookup that pulls out three bits of information on an order (the name of the stock being traded, the quantity traded and some additional info on the stock)

Basically, what I want done is when the name of the stock ends in "RR", e.g. "VOD RR" (instead of "VOD LN"), I don't want to pull information on that order. I don't think this part is that hard to accomplish.

However, normally, each row would have a different order. Obviously if I don't pull information on the order VOD RR, I'll have a blank row. Is there a way to get rid of the blank row (like by pulling the next order)?

Please let me know if this does not make sense.

Thanks!