Vlookup using strings

[armagan]

Hi,

I am trying to process some stock calculations; what I want to do is process for January 2011(Mth) all stock trades on the Trades Worksheet using a macro.

so I have 2 worksheets Trades and Holdings.

Excel Workbook
	A	B	C	D	E
1	Stock	date	price	quantity	cost
2	Rio Tinto	01/12/2010	5	2	10
3	Shell	23/12/2010	3	1	3
4	Barclays	01/01/2011	2	4	8
5	Shell 2	13/01/2011	3	3	9
6	Shell 3	17/01/2011	2	4	8
7	Rio Tinto 2	23/02/2011	5	1	5
8	Shell 4	03/03/2011	3	1	3
trades

Excel 2007

Excel Workbook
	A	B
1	stock	industry
2	Rio Tinto	commodities
3	Barclays	financial
4	Shell	industrial
holdings

Excel 2007


In order to do this I will;
manually prompt for the month you want to process=Mth in this case 2.
for each record on Trades!Column B which is equal to Mth, do a vlookup on the Holdings worksheet and return the value from column B (on the holdings worksheet)
For all the records where the date =Mth copy Commodity types to the commodity worksheet, and stock to the stock worksheet.

so the end result should be on the Trades worksheet Record 5 and 6, get copied to the industrial worksheet, record 4 (on trades) gets copied to the financial worksheet.

I am finding it difficult using the Vlookup as the column I'm looking up is not a simple value. The value has to extract from Column A on Trades the stock name (ignoring the number in the cell) before doing the vlookup and copying value in worksheet Holdings column B to the dedicated worksheet

any help or advice would be much appreciated as I've been stuck on this for about a month!

Armagan

 

[shift-del]

Hello Armagan

To get the desired results, the chosen month has to be 1 (january) and not 2.
<table valign="middle" colspan="7" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: bold; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="66pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="7" align="middle">Worksheet 'trades'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td style="font-family: Verdana; font-size: 10px;" align="left">Stock</td><td style="font-family: Verdana; font-size: 10px;" align="left">date</td><td style="font-family: Verdana; font-size: 10px;" align="left">price</td><td style="font-family: Verdana; font-size: 10px;" align="left">quantity</td><td style="font-family: Verdana; font-size: 10px;" align="left">cost</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Rio Tinto</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">01.12.2010</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">5</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">2</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">10</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Shell</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">23.12.2010</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">3</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">1</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">3</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Barclays</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">01.01.2011</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">2</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">4</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">8</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">financial</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Shell 2</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">13.01.2011</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">3</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">3</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">9</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">industrial</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Shell 3</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">17.01.2011</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">2</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">4</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">8</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">industrial</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Rio Tinto 2</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">23.02.2011</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">5</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">1</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">5</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="left">Shell 4</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">03.03.2011</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">3</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">1</td><td style="font-family: Verdana; font-size: 10px; font-weight: normal;" align="right">3</td><td style="color: rgb(255, 0, 0); font-weight: normal;" align="left">
</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>F2</td><td>=IF(MONTH(B2)=$F$1,VLOOKUP(IF(ISNUMBER(--RIGHT(A2,1)),LEFT(A2,LEN(A2)-2),A2),holdings!A:B,2,FALSE),"")</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

<table valign="middle" colspan="6" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: bold; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="6" align="middle">Worksheet 'industrial'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td style="font-family: Verdana; font-size: 10px;" align="left">Stock</td><td style="font-family: Verdana; font-size: 10px;" align="left">date</td><td style="font-family: Verdana; font-size: 10px;" align="left">price</td><td style="font-family: Verdana; font-size: 10px;" align="left">quantity</td><td style="font-family: Verdana; font-size: 10px;" align="left">cost</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="font-weight: normal;" align="left">Shell 2</td><td style="font-weight: normal;" align="right">13.01.2011</td><td style="font-weight: normal;" align="right">3</td><td style="font-weight: normal;" align="right">3</td><td style="font-weight: normal;" align="right">9</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="font-weight: normal;" align="left">Shell 3</td><td style="font-weight: normal;" align="right">17.01.2011</td><td style="font-weight: normal;" align="right">2</td><td style="font-weight: normal;" align="right">4</td><td style="font-weight: normal;" align="right">8</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td style="font-weight: normal;" align="left">
</td><td style="font-weight: normal;" align="left">
</td><td style="font-weight: normal;" align="left">
</td><td style="font-weight: normal;" align="left">
</td><td style="font-weight: normal;" align="left">
</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: fixed; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>A2</td><td>{=IF(COUNTIF(trades!$F$2:$F$10,"industrial")<ROWS(A$2:A2),"",INDEX(trades!A$2:A$10,SMALL(IF(trades!$F$2:$F$10="industrial",ROW(trades!A$2:A$10)-ROW(trades!A$2)+1),ROWS(A$2:A2))))}</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Contains array formula!</td></tr><tr><td>Do not enter the curly brackets {}.
</td></tr><tr><td>Enter the formula with CTRL-SHIFT-ENTER instead of just ENTER.</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[armagan]

Hi Mate,

sorry you have realised my mistake, I meant month 1.

the second part are certain the cell to enter it is A2? overwriting my cell?

Whilst it works to do the vlookup what I was after was to actually do it in macro, but using your calculation I can use it as a stepping stone to build the macro.

 

[shift-del]

the second part are certain the cell to enter it is A2? overwriting my cell?

It's a different worksheet (industrial) - as you requested.
And you never specified wether any columns in this worksheet are already filled.

 

[armagan]

no it (the industrial worksheet) will be empty other than first row which has headings.