Excel Formula Multiple if then

L

Lisa P NYC

New Member
Joined
Feb 26, 2020
Messages
16
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I would greatly appreciate it if someone could help me. I am feeling not so smart today. LOL

I need to create a formula based on several criteria. The first step is to find all of the matches in one column, then I need to take all of the items that match and tell it to look at another column, to check for matches there. Not the same matches as in the first column.

For example, the first column contains a list of 50 to 100 products. I need to locate all of the same products. The second column says buy or sell, nothing else. I need to find all of the buys of the same product and all of the sells of the same product. Once they are found I need to check if it occurred within a 5 minute time frame. That data is in a third column.

I realize the explanation is super confusing so I have attached the sheet.

This is my Goal

1 - Find all of the matches in Column G

2 - If there are matches look at Column E and A - Look for Buys of the same products and B - Look for Sells of the same products

3 - Take all Buys (same product) look at Column D and see if they occurred in 5 or less minutes of each other. Same for Sells (same product) D is in hours, minutes, and seconds. 22:30:25 = 22 hundred hours 30 minutes and 25 seconds.

4 - If true say yes or whatever trigger anyone thinks is the easiest.

At this point I am not even sure it is possible??

Thank you in advance to anyone who may be able to help.
Lisa

Book1.xlsx
ABCDEFGHIJKLMNO
1AcctNameDateTime openTypeLotAssetPriceSLTPTime dateTime ClosePrice ClosexyzProfit
22011100#REF!2024.05.140:28:32buy0.01USDJPYc156.2250157.6552024.05.145:11:11156.461Mobile1.51
32011100#REF!2024.05.141:02:04buy1GBPJPYc196.201002024.05.1416:03:00196.632Mobile275.18
42011100#REF!2024.05.141:02:18buy1.5GBPJPYc196.201002024.05.1416:03:00196.632Mobile412.76
52011100#REF!2024.05.141:02:26buy2.5GBPJPYc196.2010197.392024.05.147:54:38196.337Mobile217.41
62011100#REF!2024.05.131:10:17buy10GBPJPYc195.056195.917200.3582024.05.1411:14:27195.221Client1 058.08
72011100#REF!2024.05.141:15:44buy1.5XAUUSDc2337.622333.8523482024.05.145:37:582342.69Mobile760.5
82011100#REF!2024.05.141:15:59buy1.5XAUUSDc2337.652333.52348.042024.05.145:38:032342.69Mobile756
92011100#REF!2024.05.141:17:37sell0.1USDJPYc156.188156.271155.4792024.05.141:37:23156.23Client-2.69
102011100#REF!2024.05.141:18:27buy1.5DJ30.s39462.1739292.7302024.05.143:43:2839470.73Client128.4
112011100#REF!2024.05.131:19:45buy5USTEC.s18148.6618079.4418260.462024.05.1415:30:0518118.62Client-1 502.00
122011100#REF!2024.05.141:37:27buy0.1USDJPYc156.230156.7882024.05.148:10:26156.411Client11.57
132011100#REF!2024.05.141:42:19sell0.1DJ30.s39459.7339469.3439417.662024.05.142:13:5339469.34Client-9.61
142011100#REF!2024.05.141:48:36sell5XAUUSDc2338.352338.6502024.05.142:48:142338.65Mobile-150
152011100#REF!2024.05.141:49:08sell0.4XAUUSDc2338.312341.6423342024.05.144:56:112341.64Mobile-133.2
162011100#REF!2024.05.141:49:46sell0.2XAUUSDc2338.172338.912334.732024.05.142:48:172338.91Client-14.8
172011100#REF!2024.05.141:53:21sell0.1GBPJPYc196.169196.236196.0042024.05.142:50:00196.236Client-4.29
182011100#REF!2024.05.141:56:34sell0.1DJ30.s39463.7339469.4839417.142024.05.142:13:5339469.48Client-5.75
192011100#REF!2024.05.142:04:20buy2.5DJ30.s39467.1739427.44396562024.05.143:35:3139473.73Mobile164
202011100#REF!2024.05.142:04:39buy1DJ30.s39467.1739409.5539677.432024.05.143:35:2739472.73Mobile55.6
212011100#REF!2024.05.142:10:34sell1XAUUSDc2338.09234323292024.05.143:45:262335.5Mobile259
222011100#REF!2024.05.142:19:41sell1XAUUSDc2338.07234323292024.05.143:44:492335.24Mobile283
232011100#REF!2024.05.142:32:21sell0.05XAUUSDc2337.72002024.05.143:22:532339.6Client-9.4
242011100#REF!2024.05.142:34:03sell0.2XAUUSDc2337.73002024.05.143:22:352339.57Client-36.8
252011100#REF!2024.05.012:38:22sell0.5GBPAUDc1.928441.93202024.05.1418:00:221.90046Mobile926.74
262011100#REF!2024.05.132:43:23buy0.2XAUUSDc2361.13232223582024.05.1419:55:002354.49Mobile-132.8
272011100#REF!2024.05.142:45:24sell1XAUUSDc2337.84234323292024.05.143:44:532335.29Mobile255
282011100#REF!2024.05.142:49:36sell0.5XAUUSDc2338.2002024.05.1423:44:162357.88Client-984
292011100#REF!2024.05.142:49:44sell0.5XAUUSDc2338.61002024.05.1423:44:092357.93Client-966
302011100#REF!2024.05.142:50:49sell1XAUUSDc2338.92234323292024.05.143:45:562335.89Mobile303
312011100#REF!2024.05.143:16:15sell5USDJPYc156.259156.293156.1042024.05.143:17:13156.293Client-108.77
322011100#REF!2024.05.143:17:39sell5USDJPYc156.263156.31156.0492024.05.143:27:32156.31Client-150.34
332011100#REF!2024.05.103:25:00sell1.51USDJPYc155.525002024.05.1418:51:41156.481Expert-922.51
342011100#REF!2024.05.143:30:18buy3GBPJPYc196.3110197.0332024.05.143:30:59196.264Client-90.19
352011100#REF!2024.05.143:30:22buy3USDJPYc156.359002024.05.143:31:00156.332Client-51.81
362011100#REF!2024.05.143:31:36sell3GBPJPYc196.269196.31195.9822024.05.143:34:34196.31Client-78.67
372011100#REF!2024.05.143:31:36sell3USDJPYc156.328156.368156.0942024.05.143:34:52156.358Client-57.56
382011100#REF!2024.05.143:37:44buy5USDJPYc156.362156.17160.1542024.05.145:58:15156.427Mobile208.4
392011100#REF!2024.05.143:42:53buy10USDJPYc156.381156.171157.2822024.05.145:58:10156.427Mobile295.35
402011100#REF!2024.05.143:45:44buy0.24XAUUSDc2335.67023372024.05.143:58:262336.84Mobile28.08
412011100#REF!2024.05.143:46:01buy1XAUUSDc2335.66002024.05.144:01:192337.32Mobile166
422011100#REF!2024.05.143:46:35buy1XAUUSDc2335.5002024.05.144:01:242337.59Mobile209
432011100#REF!2024.05.143:50:23buy1XAUUSDc2335.42002024.05.144:01:282337.8Mobile238
442011100#REF!2024.05.144:01:36sell1XAUUSDc2337.83235323362024.05.149:37:512337.91Mobile-8
452011100#REF!2024.05.144:01:54sell1XAUUSDc2337.73235323362024.05.149:38:012337.76Mobile-3
462011100#REF!2024.05.144:02:05sell1.01XAUUSDc2338.3002024.05.144:05:432338.11Mobile19.19
472011100#REF!2024.05.144:02:43sell1XAUUSDc2338.88234323302024.05.144:40:482338.62Mobile26
482011100#REF!2024.05.144:07:34buy0.24XAUUSDc2337.8002024.05.144:11:442339.29Mobile35.76
492011100#REF!2024.05.144:09:36sell0.1XAUUSDc2338.54002024.05.144:27:052338.51Mobile0.3
502011100#REF!2024.05.144:10:17sell11.1XAUUSDc2338.732338.7302024.05.144:27:472338.73Mobile0
512011100#REF!2024.05.144:11:49buy0.02USDJPYc156.394156.28602024.05.1412:29:59156.286Mobile-1.38
522011100#REF!2024.05.134:12:21buy8GBPJPYc194.834195.465200.4472024.05.1411:17:18195.586Client3 864.24
532011100#REF!2024.05.144:12:38sell1XAUUSDc2339.85234323302024.05.144:40:542338.69Mobile116
542011100#REF!2024.05.144:17:25sell1GBPUSDc1.25551.258251.247322024.05.147:15:111.25585Client-35
552011100#REF!2024.05.144:17:29sell1GBPUSDc1.255471.25821.247322024.05.147:15:091.25585Client-38
562011100#REF!2024.05.144:17:31sell1GBPUSDc1.255471.25821.247322024.05.147:15:081.25585Client-38
572011100#REF!2024.05.144:17:31sell1GBPUSDc1.255471.258151.247322024.05.147:15:071.25585Client-38
582011100#REF!2024.05.144:18:05sell5XAUUSDc2339.862341.802024.05.144:24:092339.16Mobile350
592011100#REF!2024.05.144:27:49sell0.22XAUUSDc2338.81234623332024.05.145:00:262342.69Mobile-85.36
602011100#REF!2024.05.144:30:52sell1GBPUSDc1.255561.26751.247522024.05.147:14:571.25585Client-29
612011100#REF!2024.05.144:31:06sell1GBPUSDc1.255511.26751.247522024.05.147:14:561.25585Client-34
622011100#REF!2024.05.144:36:06sell0.1XAUUSDc2339.862339.8602024.05.144:45:482339.3Mobile5.6
632011100#REF!2024.05.144:36:31buy0.34XAUUSDc2340.222336.323482024.05.144:56:142341.22Mobile34
642011100#REF!2024.05.084:40:31buy0.5CHFJPYc170.40617002024.05.1418:00:19172.575Mobile693.38
652011100#REF!2024.05.144:42:06buy0.1XAUUSDc2338.22002024.05.144:45:322339.22Mobile10
662011100#REF!2024.05.144:52:56sell1XAUUSDc2340.23235323362024.05.149:33:062340.74Mobile-51
672011100#REF!2024.05.144:53:39sell1XAUUSDc2340.22235323362024.05.149:32:252340.07Mobile15
682011100#REF!2024.05.144:55:25sell0.7XAUUSDc2340.62002024.05.1415:30:582339.61Mobile70.7
692011100#REF!2024.05.144:55:52buy0.34XAUUSDc2341.05023432024.05.144:59:342341.72Mobile22.78
702011100#REF!2024.05.144:56:03sell0.01XAUUSDc2340.85002024.05.144:57:022341.53Mobile-0.68
712011100#REF!2024.05.144:56:39buy1.5XAUUSDc2341.5523372345.62024.05.147:42:312344.14Mobile388.5
722011100#REF!2024.05.144:58:02buy0.34XAUUSDc2341.69002024.05.144:59:562341.78Mobile3.06
732011100#REF!2024.05.144:59:04buy0.42XAUUSDc2342.14233923462024.05.145:01:302342.69Mobile23.1
742011100#REF!2024.05.144:59:31buy0.21XAUUSDc2341.99233723762024.05.148:10:222343.26Mobile26.67
752011100#REF!2024.05.145:00:35buy0.22XAUUSDc2342.56233923482024.05.145:11:112343.01Mobile9.9
762011100#REF!2024.05.145:00:52buy0.2XAUUSDc2342.892341.523682024.05.148:02:162343.4Mobile10.2
772011100#REF!2024.05.145:01:12sell1XAUUSDc2342.57023342024.05.145:48:372342.38Mobile19
782011100#REF!2024.05.145:02:10buy1.5XAUUSDc2342.2623372345.32024.05.147:42:312344.14Mobile282
Test
Cell Formulas
RangeFormula
B2:B78B2=XLOOKUP(A2,#REF!,#REF!)
 
Sort by date Sort by votes
Stephen I am soooo sorry to do this...but I am getting a #value error. I have triple-checked the formula and it is correct. Do you think I have a column formatted differently than you? I think it might be coming from the ABS section of the formula, so maybe my date and time are formatted differently.

Thought? I know...I have moved to the thorn category and I apologize. You are sorry that you helped now !! LOL
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
It's no problem at all that you have follow up issues/questions ...

I suspect the issue is with the dates. To check for <5 minute differences, we add dates and times together. This relies on dates being numeric, rather than text.

Where my post shows 2023.05.14, for example, the cell actually contains the number 45060, formatted as yyyy.mm.dd

If your cell contains the text value 2023.05.14, the formula will return an error. If so, you will need to convert text to numeric, which you can do like this:

ABC
1TextNumber dd/mm/yyyyNumber yyyy.mm.dd
22023.05.1515/05/20232023.05.15
34506145061
Sheet1
Cell Formulas
RangeFormula
B2:C2B2=DATE(LEFT($A2,4),MID($A2,6,2),RIGHT($A2,2))
B3:C3B3=B2

Or you can build it all into one big formula, which using my Post #2 layout would be:

Excel Formula: 
Col Q: =IF(SUMPRODUCT(($A$2:$A$78=$A2)*($G$2:$G$78=$G2)*($E$2:$E$78=$E2)*(ABS(DATE(LEFT($C$2:$C$78,4),MID($C$2:$C$78,6,2),RIGHT($C$2:$C$78,2))+$D$2:$D$78-DATE(LEFT($C$2,4),MID($C$2,6,2),RIGHT($C$2,2))-$D2)<=1/288))>1,"YES","")
CF: =SUMPRODUCT(($A$2:$A$78=$A2)*($G$2:$G$78=$G2)*($E$2:$E$78=$E2)*(ABS(DATE(LEFT($C$2:$C$78,4),MID($C$2:$C$78,6,2),RIGHT($C$2:$C$78,2))+$D$2:$D$78-DATE(LEFT($C$2,4),MID($C$2,6,2),RIGHT($C$2,2))-$D2)<=1/288))>1
 
Last edited:
Upvote 1
Perfect. That formatting works. Thank you very much for taking the time to help me, I truly appreciate it. Have a wonderful weekend.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Customer Ageing Different Formulas
Replies
3
Views
263
Alex Blakenburg
A
D
  • Question Question
Having trouble getting Pivot Table data to pull from pivot table to regular table using =GetPivotData()
Replies
4
Views
1K
smozgur
smozgur
N
  • Solved
Copy specific cells based on controls checkbox and paste in the next empty row on a table
Replies
5
Views
81
nstaab07
N
R
  • Solved
Find multiple matches with wildcard search
Replies
6
Views
540
Peter_SSs
Peter_SSs
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
165
Ol Reliable
O

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top