using vlookup when references are in multiple columns

[jrtaylor]

I've attached a workbook which has an example of the layout of one of my workbooks. On the left side of the worksheet, activity is sequential by date.

I want to rearrange the data so that activity is sequenced by ticket number. The desired outcome is shown on the right. (Don't worry about the math calcs. I will be doing these on another sheet)

I prefer not to use a pivot table because this is a very dynamic workbook in which most cells are formulas referencing each other.

Please let me know if I need to clarify anything.

Thanks!

[FDibbins]

1. Are your ticket numbers actual (real) numbers, or text?
2. Is there a need for F:H to be in their own columns? It would be much simpler if you just had the 4 columns A:D, with all data 1 below the other - you could then use LARGE() or SMALL() to do what you want

[jrtaylor]

Hi.

The ticket numbers are numbers, generated by a simple formula which makes each trade sequential, regardless of whether its a long or short trade. That's their only purpose.

If it would help to have some other way of identifying each trade, I'm fine with that as long as the outcome lays them out sequentially according to the dates they are opened.

Combining long and short trades into one set of columns would be a very difficult task for me, because I'd still need a way to determine which trade opening(s) match the trade closes.

Each "side" (long and short) requires about 25 columns of formulas. This is another reason they are separated.

Could I use two "sets" of vlookup to order the trades? Thanks!

[Glenn Kennedy]

OK. Here is 75% of what you wanted.

In J7, copied down:
=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$7:$H$22)*100+COLUMN($A$7:$H$22))/(($A$7:$H$22<>0)*(MOD(COLUMN($A$7:$H$22)-2,4)=0)),ROWS($J$6:J6)),"R0C00"),FALSE),"")

In K7, copied across to L7 and down:
=IFERROR(IFERROR(INDEX(C$7:C$22,MATCH($J7,$B$7:$B$22,0)),INDEX(G$7:G$22,MATCH($J7,$F$7:$F$22,0))),"")

I do not understand column M, especially what's meant to be happening in M10 and M11....

[jrtaylor]

Thanks! I'm going to see if I can come up with the formula for M1 and M2. But to answer the question, it works like this:

Ticket # 123459 opens with a Long at $978.00 Then we have another Long in ticket 123460 at $1020.00.

In other words, we bought some stock on 7/14/17 and then before we sell that stock, we buy some more on 7/15/17. The total amount we now own has cost us $978 + $1020 = $1998.

On 7/16/17 we close out those purchases (cell D16) for $2,019.00. In another part of the workbook, we will determine the profit/loss on each ticket. We do this by using a weighted average:

978/1998 = 48.95% of our total.
1020/1998 = 51.05% of our total.

So, the close price of $2019 X 48.95% = $998.28 (Cell M11)
And the close price of $2019 X 51.05% = $1030.72. (Cell M12)

To reconcile these calcs, add M11 + M12 and we get $2019.00

In any case, I'll work on this with the formula you provided and see if I can get the desired results. (Unless you have a suggestion to modify your formula)

Thanks so much!

[Glenn Kennedy]

I think the first 3 columns are fine. The 4th will require something different. I'm ruminating on it...

[Glenn Kennedy]

I have now got this to the stage where I have got all the results.... except for your "manual intervention". How easy this will be depends on a number of factors. Most notable amongst these will be the MAXIMUM number of open transactions that are closed all in one go. Can you supply a longer dataset.

Where I have got to requires the use of a few helper columns. It might be possible to eliminate some of them... but at the moment, I'm not sure. So, for now, they're all in there. Where you have open-ended transactions, the formula is designed to generate an error. However, I do have the combined close out price available to me (as text, which I can readily convert back to a number. I think I need to know how many open transactions can be on the go in a column.

Someone else might be able to cut through my (now) confused thought processes and do it all without helpers... If not, I'll be back when you come back with a bigger dataset.

[FlameRetired]

I have been looking at this. Am still lost on the $988.28 $1,030.72 math in R10:R11 and weighted averages.

In the meantime I only have a suggestion for Ticket formula column O. Since the ticket numbers are sequential and contiguous this seems simpler.

=IF(ROWS($7:7)>COUNT($B$7:$B$22,$F$7:$F$22),"",MIN($B$7:$B$22,$F$7:$F$22)+(ROWS($7:7)-1))

Am I missing important detail?

[jrtaylor]

Glenn, I've attached a much longer example, and have added some columns which I hope make it easier.

For every trade opening, you'll see the opening date and the closing date on the same row.

I've left out your formulas so that you can paste them in where you see fit.

I appreciate your help very much.

[jrtaylor]

Hi Dave, thanks for looking at this. I'll let Glenn look at your formula and see what he has to say (since I don't understand it very well!).

I've left the formulas blank in the new worksheet (except for some manual entries to show the desired outcome.

Sofar as the weighted averages, the logic is this:

You buy a little stock at a time, say over three days. Day 1 is $100, Day 2 is $125, and day 3 is $110. Your total investment is $335.00.

So Day 1 is 29.8% of $335, Day 2 is 37.3% of $335, and Day 3 is $32.8% of $335. Total % = 100

A few days later, you sell all your stock for $400.00

Since each time you purchased the stock is represented by a ticket number, we have to break down that $400 sale into three parts, based on the weighted average of your costs.

Sale proceeds for the 1st buy = 29.8% of $400 = $119.41
Sale proceeds for the 2nd buy = 37.3% of $400 = $149.25
Sale proceeds for the 3rd buy = 32.8% of $400 = $131.34

Total = $400.000.

There are other ways to calculate this but for the sake of this workbook discussion, doing the weighted average will suffice.

And between you and Glenn, hopefully we can solve this! Thanks again!

[FlameRetired]

Edit Our posts crossed. I didn't refresh before posting. Will have a look at the new wb. Thank you.

In addition to more data that Glenn asked for ...

If it would help to have some other way of identifying each trade, I'm fine with that as long as the outcome lays them out sequentially according to the dates they are opened.

Yes.

could it also include

• an additional identifier which matches which stock goes with which ... or is this all the same stock?
• I noticed some of the activity happens on weekends (tickets 123457 and 123461). I don't know how relevant this might be for pairing solution although more realistic dating may help with that. If not then for now it is merely distracting.

[jrtaylor]

Dave, its all the same financial instrument. In my first workbook I was pulling dates and numbers out of the air. Workbook # 3 represents real-world activity. The instrument will vary, from stock to fx to cryptocurrencies (which trade 24/7).

I continue to think about this, and realize that if I can simply get a formula which gives me the date in column V (in workbook 3) which matches the ticket and date for the trade opening(s) then I will have the info I need to finish the job.

I hope I'm making sense!

Thanks again!

[FlameRetired]

I continue to think about this, and realize that if I can simply get a formula which gives me the date in column V (in workbook 3) which matches the ticket and date for the trade opening(s) then I will have the info I need to finish the job.

This will do that.

The Ticket numbers function as offset row headers. These have much in common with merged cells. Simplest way I know to deal with these is helper columns.

These helpers return repeating index numbers for those ticket numbers. In I:J

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

There is a pasted list (not posted here) of named ranges in the attached to make it easier to follow this formula. Array entered in X2 and filled down.

Please Login or Register  to view this content.

using vlookup when references are in multiple columns - Return Close Dates.xlsx

[FlameRetired]

Also try this modified version in X2. Array entered of course.

Please Login or Register  to view this content.

It's simpler in concept. The

ROW(Long_Ticket)-MIN(ROW(Long_Ticket))+1 and

ROW(Short_Ticket)-MIN(ROW(Short_Ticket))+1 parts replace the calls to named formulas L_Ticket_Indx and S_Ticket_Indx

One of them may perform better/faster than the other. All you can do is try.

[jrtaylor]

Thanks so much Dave. I'll test it out and let everyone know.

[FlameRetired]

You're welcome. Thanks for the feedback.