Populating third sheet with particular values from first and second sheets

Question

Not sure how to go about doing this (or if it can be done). I have a spreadsheet in Excel with three sheets. The third one is currently empty. The first two sheets have stock trades from the past 10 years. One with about 50 entries and the second with about 70. Each entry has columns like this: Stock Symbol, Company, Quantity, Purchase date, Purchase price, Total Buy $, Sell date, Sell price, total Sale $, Loss/Gain.

What I am looking to do is dynamically build the third sheet (just for reporting/viewing) with only entries in the first two sheets that have a purchase date but no sell date. IE: just show current stock holdings. So as a stock is sold and a sell date is entered into one of the first tw sheets, then it will disappear from the third sheet.

And the holdings aren't all at the bottom of the list. For instance, of the 50 entries in sheet 1, rows 5, 21, 23, 43, 46, 47, and 48 have a purchase date but no sell date.

In my searching, I am seeing that maybe VLOOKUP function would be of service, but I just learned of it today and not very familiar with it.

This would be much easier done by using VBA. Do you feel this is something you could do? — martin
And some clarifying questions: does every stock appear at most twice on your sheets? That is, there is only one buy date and possibly one sell date? And the two sheets are equal (of course they have different data, but other than that)? — martin
Hello Martin! I have no VBA experience nor currently know how to integrate with Excel. . . Yes, the two sheets are equal (all things considered) just contain different data. And also yes, each entry (row) is unique and only appears once - every buy date has only one sell date (no partial sales). — Tyler D.

martin martin · Accepted Answer · 2015-04-21T20:31:02

In your place, I would just add a column to each sheet with formula like

= countif($A$1:$A$50, A1)

Copy this formula on the first row and then select the column and press Ctrl-D to copy it across all rows. The formula will count number of times the stock symbol (I assume it is in the column A) is on the sheet. You can then use the Excel built-in filtering to display only rows when this number is 2.

If you absolutely need to copy the data to the third sheet, the above is a good first step in any case. On your third sheet you can use something like this:

In the first row:

=MATCH(2,Sheet1!$F$1:$F$50,0) in column A
=INDIRECT("Sheet1!A" & A1) in column B

In the second row (and then copy down as many times as necessary):

=MATCH(2;INDIRECT("Sheet1!F" & SUM($A$1:A1)+1):$F$50;0)
=INDIRECT("Sheet1!A" & A2+SUM($A$1:A1))

(this assumes that the first sheet is named Sheet1 and that the countif column is F)

In the A column you will have relative position of the stock symbol with count 2 (relative to the last position) and in the B column you use indirect function to retrieve the stock symbol itself.

The drawback of this solution is that you are going to have every symbol twice (once for buy, once for sell), this can be easily fixed by adjusting the formula on the Sheet1.

Populating third sheet with particular values from first and second sheets

1 Answers