Sorting Excel rows

Saher Naji

New Member
Joined
Dec 19, 2019
Messages
41
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1FX_SWPBuy/SellCall/PutCcyCcy AmntAmnt 1Trading date Tenor
2Swp NubTrade Nub
3FX_SWPBuy0EUR/USDEUR10021 Jan 202022 Jan 202011:44:142032596821905207
4FX_SWPSell0EUR/USDEUR10021 Jan 202027 Jan 202011:44:142032596821905208
5FX_SWPBuy0EUR/USDEUR200022 Jan 202025 Feb 202013:53:562034414321926095
6FX_SWPSell0EUR/USDEUR200022 Jan 202024 Jan 202013:53:562034414321926096
7FX_SWPSell0EUR/USDEUR30023 Jan 202026 Feb 202010:03:012035661321940695
8FX_SWPBuy0EUR/USDEUR30023 Jan 202027 Jan 202010:03:012035661321940696
9FX_SWPBuy0EUR/USDEUR1523 Jan 202027 Jan 202010:03:032035661921940705
10FX_SWPSell0EUR/USDEUR1523 Jan 202026 Feb 202010:03:032035661921940706
DATA




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


Thanks in advance

 

Attachments

  • short.png
    short.png
    17.4 KB · Views: 3

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Saher Naji

New Member
Joined
Dec 19, 2019
Messages
41
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1FX_SWPBuy/SellCall/PutCcyCcy AmntAmnt 1Trading date Tenor
2Swp NubTrade Nub
322 Jan 2020
4 
5 
624 Jan 2020
7 
827 Jan 2020
927 Jan 2020
10 
Short
Cell Formulas
RangeFormula
K3,K9,K7,K5K3=IF(DATA!K3=SMALL(DATA!K3:K4,1),DATA!K3,"")
K4,K10,K8,K6K4=IF(DATA!K4=SMALL(DATA!K3:K4,1),DATA!K4,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,444
Messages
5,624,809
Members
416,055
Latest member
EJGAJG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top