Sorting Excel rows

[Saher Naji]

Hello,

I'm trying to make the formula working on each 2 rows respectively, but I couldn't

FX SWP product is displayed in 2 rows. I want to separate the two rows, and put one in the Short sheet and one in the Long sheet. Each FX SWP has the same Swap Number, with two Trade Number, one will have to go to the Short term page, the other to the Long term page. Both leg have same trading date, but different tenor > here is where to get long vs short tenor. Sometimes the Short term leg is displayed as first row of the FX SWP, sometimes as second. I would like to see very simple formulas, that I can then replicate by myself, most likely only conditionals (=if, or, and). I displayed the final outcome, that I did manually.

this is the formula I'm trying to update, but maybe I'm on the wrong track, =IF(DATA!K3=SMALL(DATA!K3:K4,1),DATA!K3,"")

this is the main sheet:

Jan2020_Swp.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	FX_SWP	Buy/Sell	Call/Put	Ccy	Ccy Amnt	Amnt 1				Trading date	Tenor
2																	Swp Nub	Trade Nub
3	FX_SWP	Buy	0	EUR/USD	EUR	100				21 Jan 2020	22 Jan 2020					11:44:14	20325968	21905207
4	FX_SWP	Sell	0	EUR/USD	EUR	100				21 Jan 2020	27 Jan 2020					11:44:14	20325968	21905208
5	FX_SWP	Buy	0	EUR/USD	EUR	2000				22 Jan 2020	25 Feb 2020					13:53:56	20344143	21926095
6	FX_SWP	Sell	0	EUR/USD	EUR	2000				22 Jan 2020	24 Jan 2020					13:53:56	20344143	21926096
7	FX_SWP	Sell	0	EUR/USD	EUR	300				23 Jan 2020	26 Feb 2020					10:03:01	20356613	21940695
8	FX_SWP	Buy	0	EUR/USD	EUR	300				23 Jan 2020	27 Jan 2020					10:03:01	20356613	21940696
9	FX_SWP	Buy	0	EUR/USD	EUR	15				23 Jan 2020	27 Jan 2020					10:03:03	20356619	21940705
10	FX_SWP	Sell	0	EUR/USD	EUR	15				23 Jan 2020	26 Feb 2020					10:03:03	20356619	21940706
DATA

and the short sheet should be appear like the attached screen-shot


Thanks in advance

♥

 

[Saher Naji]

I tried to do something like this, but I edited the formula manually
but the problem I have many rows, it's just a an example

Jan2020_Swp.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	FX_SWP	Buy/Sell	Call/Put	Ccy	Ccy Amnt	Amnt 1				Trading date	Tenor
2																	Swp Nub	Trade Nub
3											22 Jan 2020
4
5
6											24 Jan 2020
7
8											27 Jan 2020
9											27 Jan 2020
10
Short

Cell Formulas
Range	Formula
K3,K9,K7,K5	K3	=IF(DATA!K3=SMALL(DATA!K3:K4,1),DATA!K3,"")
K4,K10,K8,K6	K4	=IF(DATA!K4=SMALL(DATA!K3:K4,1),DATA!K4,"")