+ Reply to Thread
Results 1 to 12 of 12

Formula to extract value from 2 rows below.

  1. #1
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Formula to extract value from 2 rows below.

    Hello to everone in the forum.

    I would like to kindly ask for the expertise and help of someone regarding an important project that I work on in my company. The problem that I am facing is the following: Imagine that I have a dataset similar to sheet1 to the workbook that I attached. I am always receiving bond transactions for the same company always twice indicating one time "Sell" and one time "Buy". The dataset is large with each week around 300 observations coming out. What I need to do is to build a formula so that ONLY ONE of the two cases for each deal either "Sell" or buy but NOT both of them are copied in sheet1 (imagine that it is similar to what I have attached). This means that the formula needs to include only one row of the two that refer to the same company, omit the next one, and move for the next company where it does the same etc. In sheet 2 I also need to extract not the name but the category of the company that issues the bond, which I specify in columns K & L. The formula that I have written in Sheet 2 which apparently doesn't work is the following:

    =if(Sheet1!D2="Sell";Vlookup(Sheet1!C2;Sheet1!$K$12:$L$16;2;False);Vlookup(Indirect(Address(Row(Sheet1!C2)+1;Column(Sheet1!C2));Sheet1!$K$12:$L$16;2;False)).

    Effectively my intuition is the following: If the cell in column D is "Sell" then go to the table and find me the category, otherwise if the cell in column D is buy, then ommit that row, go to the next one and again take the value from the table specified in columns K and L.

    I really struggle to find out what I am doing wrong. Can someone please help me with that one? It would be much appreciated.

    Thanks in advance,

    Theocharis
    Attached Files Attached Files
    Last edited by theocharis; 09-15-2020 at 05:29 AM.

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Formula to extract value from 2 rows below.

    Try this using a helper column (to simplify)

    In Sheet2!L2 use the following formula and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To get the table use in sheet2!A2 and drag down and forward the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the file for clarification
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    Jose Augusto thank you so much!!!! This is amazing feedback. God bless you!!!

  4. #4
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    Jose Augusto,

    I just understood how the formula works but I still have one question. On sheet 2 column A i do not want the name of the company but rather its category which I specified in sheet1 on the table. How is it possible to exctract that value instead of the name in the formula?

  5. #5
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    518

    Re: Formula to extract value from 2 rows below.

    Is it like this?

    worksheet sheet2 , A2 cell array formula , Drag down
    HTML Code: 
    =IFERROR(INDEX(Sheet1!$L$13:$L$16,MATCH(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$D$2:$D$50="Sell",ROW($2:$50),4^8),ROW(1:1))),Sheet1!$K$13:$K$16,)),"")

    worksheet sheet2 , B2 cell array formula, Drag down and accross
    HTML Code: 
    =INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$D$2:$D$50="Sell",ROW($2:$50),4^8),ROW(1:1)))&""
    Attached Files Attached Files

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,160

    Re: Formula to extract value from 2 rows below.

    Alternative non - array solution
    A2:

    Please Login or Register  to view this content.
    B2:
    Please Login or Register  to view this content.
    Copy B2 to H2

    Drag all down
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    Thank you very much. The indexed one works perfectly fine, for the non-index when i include a new data and paste the formula below in sheet A2 it leaves the row blank and does not make any changes.

  8. #8
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    both formulas work perfectly!!!!! thank you so much... just one question though for my personal understanding. I do not understand the purpose of the iferror in the beginning. So the formula is looking if there is an error at which exactly cell? What is the overall intuition of this function for the specific purpose? Once again, it works perfectly fine i just want to understand it more in depth that is why i ask

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,160

    Re: Formula to extract value from 2 rows below.

    IFERROR(FORMULA,"") gives blank instead of error sign (#N/A, #VALUE"...)
    Try to remover leading "IFFERROR(" and traling ","")" to see what happens without IFERROR

  10. #10
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    Oh yeah i got it completely understood now. Thanks once again.

    All the best!!

  11. #11
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    Dear Bebo,

    while this formula works perfectly for what I asked i am trying it right now for something kinda different and it does not give me the value that I want. As a further step what I need is in the column M of sheet 2 to specify a new variable called PnL. In this column what I need to do is from sheet 1 is the following: From the G column of sheet 1 I need to substract the value of buy from the value of sell and then multiply with the value in Column C. so for instance for the first once i need to do: (101.2-101.9)*100,000. Then I need to do again the same but not between rows 3 and 4 but in rows 4 and 5. I need to make the calculation for two rows and then omit the second of this rows and go directly to make the calculation for rows 4 and 5 and multiply with the value of the corresponding cell in column c. Effectively, for the second calculation that would be: (102.6-103.1)*50.000. Could you please help me with that?

    I know what you already provided me with amazing formulas which I understood and will be able to implement in the future. However, it is the first time that I am working with such a case that I need to omit rows and thus I struggle.

    For intance column G in sheet 1 would be the following (perfectly alligned with the other variables that are already there)

    Column G
    G2 = 101.2
    G3 = 101.9
    G4 = 102.6
    G5 = 103.1
    G6 = 104.2
    G7 = 105.6
    G8 = 100.4
    G9 = 103.6


    Thanks in advance!!!

  12. #12
    Registered User
    Join Date
    08-08-2020
    Location
    Frankfurt
    MS-Off Ver
    2013
    Posts
    17

    Re: Formula to extract value from 2 rows below.

    I deleted the bid and ask columns and replaced them with the column G that I specified above*

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to extract unique values/distinct values in rows in excel
    By bjnockle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-07-2020, 05:35 PM
  2. [SOLVED] Need a formula to extract name of fathers and place them in the same rows with mothers
    By sir_quattro in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-13-2020, 06:11 PM
  3. [SOLVED] Formula to extract certain rows (based on a single criteria) for a dashboard
    By The_Snook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2020, 05:18 AM
  4. Formula to extract data from rows in two worksheets
    By Naval11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-01-2019, 05:11 PM
  5. formula to extract multiple rows based on reference
    By Sam D in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 06-21-2018, 02:21 AM
  6. Replies: 2
    Last Post: 11-24-2017, 03:51 PM
  7. [SOLVED] Use a formula within a macro to extract data and copy down x number of rows
    By rdowney79 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-15-2013, 12:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1