Need help organizing binance CSV data - Match Index IF?

[MrCameronExcel]

Hi,

Has anyone organized the Binance CSV file?

At the moment, I am trying to fill in the Ticker column on CONVERTED TABLE for transactions (Transaction Related), using the crypto ticker not GBP/USDT. The left table consists of the Binance CSV data. In th CONVERTABLE TABLE Timestamp column consists of UTC_Time data but with duplicates removed.

I've tried combinations of VLOOKUP and IF with no luck.

I need to vlookup the Timestamp, then if the operation that says "_______" or "______", and if the Coin says "_______" or "______", return coin "______" (column 4). Something along these lines.

Please comment if you have any questions. your help is appreciated!

BINANCE CSV DATA

CONVERTED TABLE

UTC_Time

Account

Operation

Coin

Change

Remark

USD Conversion

Timestamp

Exchange or Wallet

Ticker

Amount Purchased/Sold, Withdrawn/Deposited

Token Price at Purchase

Net Invested (USD)

Fees

19/03/2021 18:34

Spot

Deposit

GBP

20

27.4341

19/03/2021 18:34

Binance

24/03/2021 04:05

Spot

Transaction Related

ADA

24.6

Tokens

24/03/2021 04:05

Binance

24/03/2021 04:05

Spot

Transaction Related

GBP

-20

-27.4341

24/03/2021 04:07

Binance

24/03/2021 04:07

Spot

POS savings purchase

ADA

-24.6

Tokens

25/03/2021 06:36

Binance

25/03/2021 06:36

Spot

Deposit

GBP

150

205.75575

25/03/2021 06:43

Binance

25/03/2021 06:43

Spot

Transaction Related

VET

2426

Tokens

26/03/2021 00:35

Binance

25/03/2021 06:43

Spot

Transaction Related

GBP

-150

-205.75575

26/03/2021 20:44

Binance

26/03/2021 00:35

Spot

POS savings interest

ADA

0.00525

Tokens

26/03/2021 21:52

Binance

26/03/2021 20:44

Spot

Deposit

GBP

100

137.1705

27/03/2021 00:33

Binance

26/03/2021 21:52

Spot

Transaction Related

GBP

-100

-137.1705

27/03/2021 06:08

Binance

26/03/2021 21:52

Spot

Transaction Related

USDT

137.194

137.1936

28/03/2021 00:35

Binance

27/03/2021 00:33

Spot

POS savings interest

ADA

0.00525

Tokens

29/03/2021 01:21

Binance

27/03/2021 06:08

Spot

Buy

VET

1497

Tokens

30/03/2021 01:21

Binance

27/03/2021 06:08

Spot

Transaction Related

USDT

-137.13

-137.1252

31/03/2021 01:22

Binance

27/03/2021 06:08

Spot

Fee

VET

-1.497

Tokens

01/04/2021 01:21

Binance

28/03/2021 00:35

Spot

POS savings interest

ADA

0.00525

Tokens

02/04/2021 01:21

Binance

29/03/2021 01:21

Spot

POS savings interest

ADA

0.00525

Tokens

02/04/2021 08:22

Binance

30/03/2021 01:21

Spot

POS savings interest

ADA

0.00525

Tokens

02/04/2021 09:29

Binance

31/03/2021 01:22

Spot

POS savings interest

ADA

0.00525

Tokens

03/04/2021 01:22

Binance

 

[Sufiyan97]

Can you clarify

I need to VLOOKUP the Timestamp, (What will be the lookup value)

then if the operation that says "_______" or "______", and if the Coin says "_______" or "______", return coin "______"
(What do you mean by "_______" or "______")


(column 4). Something along these lines.
(What is the meaning of above line)

And can you post some expected results? for example

 

[MrCameronExcel]

Yes sorry if the question was so unclear.

I want to vlookup timestamp, for example "19/03/2021 18:34" or I3 in table. Have the table array be A3:G21.

Then the return value, if the respective Account column entry is "Coin-Futures" return blank (" "), if the entry is Spot then look in Operation column and if the respective entry is "Deposit" return blank, if the entry is "Transaction Related", then return value in Coin column except for entry's "GBP" and "USDT"

Does this help? Please let me know if further clarification is needed.
I recognise that I will need to expand upon the logic I wrote above, but I think if I can get some help with starting out, I can expand the formula to be fully inclusive of the 3000 rows of data I have to sort through.
Thanks

 

[Sufiyan97]

What you want if Coin column has entry's "GBP" and "USDT"?

 

[MrCameronExcel]

What you want if Coin column has entry's "GBP" and "USDT"?

I want it to search another row in the table array for the same vlookup value (eg "19/03/2021 18:34" or I3), and return the Coin ticker that isn't "GBP" or "USDT", but still matching the criteria specified for Account and Operation columns. There are duplicates of this exact Timestamp (ie "19/03/2021 18:34") which are indicating a trade - so less GBP to buy VET as an example. It's the cryptocurrency ticker I want to be found for that trade.

 

[Sufiyan97]

Not sure if I fully understand what you want but

Try

=IF(AND(C3="Transaction Related",OR(D3={"GBP","USDT"})),"",IF(VLOOKUP(I3,$A$3:$G$21,2,0)="Coin-Futures","",IF(AND(VLOOKUP(I3,$A$3:$G$21,2,0)="Spot",C3="Deposit"),"",D3)))