vlookup word in a column and count the # of rows between each use of the word

[jrtaylor]

In the attached workbook I'm showing a small trading ledger which has one row per calendar day. I'm creating a new table which condenses this data into a format which has no empty rows, and which also takes some "column" data and transposes it into "row" data.

Please note that I don't want to use a pivot table. I'd rather use formulas.

I have had success creating a new table which eliminates the empty rows. But I need assistance with the next step, and that is to
(a) locate each instance of the word "close" in Column C, then
(b) give me data in the rows above it, up to but not including the preceding row which has "close" in Column C.

As an example, when looking at the attached workbook the formula I need would give me row numbers 18,20, and 23. If I have those row numbers then I can create the formulas I need to insert the values in those rows into specific cells in the new table.

I'm assuming this would involve vlookup, hlookup, and/or match. Or something similar?

Any advice? Thanks so much!

[AliGW]

Please post your workbook again once you have manually added your expected outcomes.

[kvsrinivasamurthy]

In sheet2 in A6,Array Formula, then drag across

=IFERROR(INDEX(Sheet1!A$11:A$24,SMALL(IF(Sheet1!$C$11:$C$24="sell",ROW($C$11:$C$24),""),ROWS($A$6:$A6))-ROW($C$11)+1),"")

Array formula should be confirmed with Ctrl+Shift+Enter keys together.

[Noah101]

Could you also use a Sumifs for this one as well?

[kvsrinivasamurthy]

Update file showing what is expected result manually.

[jrtaylor]

Hi Ali, I've attached the workbook with the expected outcome. I also created a "check" to test the math on both worksheets.

If you look at the Desired Outcome tab, you'll see the layout I'm going for, and also that I've used VLOOOKUP
to get some of the numbers.


The challenge here is to match the "close" date with the transactions above these "closes." I was thinking that I could
somehow use a combination of row numbers, INDIRECT, HLOOKUP or other functions to fill this out.

If it helps, the solution for this problem could also involve adding helper cells to the first tab. That would be fine.

Please let me know if this makes sense.

Thanks!!

[AliGW]

Will there always be a 7-day interval between the first buy or sell and the close price?

[jrtaylor]

No, the intervals will be random. Would it help if I reworked the sample ledger to show more transactions?

[AliGW]

It needs to be properly representative. Anyone trying to help will be looking for patterns in the data.

[jrtaylor]

OK, I'll add some more rows and transactions.

[jrtaylor]

OK, here's version 3. I've added some rows to show a more random pattern of transactions, and also clarified how P/L is calculated.

Thanks.

[kvsrinivasamurthy]

ARRAY formulas , to be dragged down
In J11

=IFERROR(INDEX(Sheet1!$B$11:$B$25,SMALL(IF((Sheet1!$C$11:$C$25="close")*(ROW(Sheet1!$A$11:$A$25)>INDEX(ROW(Sheet1!$A$11:$A$25),MATCH('Expected Outcome'!$A11,Sheet1!$A$11:$A$25))),ROW(Sheet1!$A$11:$A$25),""),1)-ROW(Sheet1!$A$11)+1),"")

In K11

=IFERROR(INDEX(Sheet1!$I$11:$I$25,SMALL(IF((Sheet1!$C$11:$C$25="close")*(ROW(Sheet1!$A$11:$A$25)>INDEX(ROW(Sheet1!$A$11:$A$25),MATCH('Expected Outcome'!$A11,Sheet1!$A$11:$A$25))),ROW(Sheet1!$A$11:$A$25),""),1)-ROW(Sheet1!$A$11)+1),"")

Array formula should be confirmed with Ctrl+Shift+Enter keys together.

[dmg2016]

Hi jrtaylor,


Close Date Expected Outcome!J11:*

Please Login or Register  to view this content.

*if you do not have minifs this can be altered by using an Min-If array formula to get the same result as in the formula above.


Close Price Expected Outcome!K11:

Please Login or Register  to view this content.

DMG

[jrtaylor]

Thanks so much everyone! Looks like both solutions work. I do have MINIFS so I'll use that formula.
I really appreciate everyone's help