# Formula to extract value from 2 rows below.

#### Theocharis

##### New Member
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:

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.

Theocharis

#### Attachments

• sheet2.PNG
27 KB · Views: 5
• sheet 1.PNG
5.2 KB · Views: 5

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### earthworm

##### Active Member
In column I input this formula :
Excel Formula:
``=IF(COUNTIF(B\$2:B2,B2)=1,1,"-")``
In column J input this formula :
Excel Formula:
``=IFERROR(IF(I2="-","-,"-"",SUM(I2:I2)),"-")``
In second sheet input serial number 1-100 whatever your length of data is
In second sheet use vlookup / index function to extract data to another sheet using the column J to extract

Replies
0
Views
117
Replies
20
Views
303
Replies
8
Views
516
Replies
5
Views
78
Replies
6
Views
155

1,118,213
Messages
5,570,921
Members
412,350
Latest member
Alimarie