Look-Up duplicate value in column S:S and return the date in B:B from the duplicate value

R

RonFos3394

New Member
Joined
Jun 19, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi

Hoping someone can help, been racking my brains for a while on this one.

I've got a table of data from A5:AD15000. In column B:B is dates/times. In Column S:S is serial numbers and will have duplicate values.

*Note there may be more than 1 duplicate of the same value however I only want it to look for 2nd duplicate then if it finds the same serial again to treat it as another entry and look for a 2nd duplicate. (hope this makes sense).

The data table is sorted by date/time.

I'm wanting to find a formula that if a duplicate value is found within S:S to return the date/time located in the column B:B of the duplicate value. So I can find out the time difference between the two serial numbers.

Alternatively, if I can find the duplicates and have the formula work out if the date/time in B:B is within 48 hours to return "TRUE" or "FALSE".

Any help would be awesome!

Cheers
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hi, welcome to the forums!

Maybe something like this could help. Might not be 100% what you need, but probably something in the right direction.
I am not sure where you want this formula to be, but for this example it's in column A.

Book2
ABCDEFGHIJKLMNOPQRS
1Supply Vessel
2No Duplicates01-Jun-23 6:06 PMa
3FALSE02-Jun-23 6:06 PMbb
4No Duplicates03-Jun-23 6:06 PMz
5No Duplicates04-Jun-23 6:06 PMs
6No Duplicates05-Jun-23 6:06 PMl
7No Duplicates06-Jun-23 6:06 PMaa
8No Duplicates07-Jun-23 6:06 PMad
9No Duplicates08-Jun-23 6:06 PMab
10No Duplicates09-Jun-23 6:06 PMcd
11No Duplicates10-Jun-23 6:06 PMde
12No Duplicates11-Jun-23 6:06 PMfg
13No Duplicates12-Jun-23 6:06 PMqw
14Paired above13-Jun-23 6:06 PMbb
15No Duplicates14-Jun-23 6:06 PMp
16No Duplicates15-Jun-23 6:06 PMi
17No Duplicates16-Jun-23 6:06 PMo
18No Duplicates17-Jun-23 6:06 PMk
19TRUE18-Jun-23 6:06 PMbb
20No Duplicates19-Jun-23 6:06 PMll
21No Duplicates20-Jun-23 6:06 PMkk
22Paired above21-Jun-23 6:06 PMbb
23No Duplicates22-Jun-23 6:06 PMbb
24No Duplicates23-Jun-23 6:06 PMj
25No Duplicates24-Jun-23 6:06 PMh
26No Duplicates25-Jun-23 6:06 PMg
Sheet1
Cell Formulas
RangeFormula
A2:A26A2=IF(MOD(COUNTIF(S$1:S2,S2),2)=1,IF(ISNA(VLOOKUP($S2,CHOOSE({1,2},$S3:$S$15000,$B3:$B$15000),2,0)),"No Duplicates",IF(ABS(TODAY()-VLOOKUP($S2,CHOOSE({1,2},$S3:$S$15000,$B3:$B$15000),2,0))<=2,TRUE,FALSE)),"Paired above")
 
Upvote 0
Another option
Fluff.xlsm
ABST
1
219/06/2023 12:00A 
319/06/2023 18:00B 
420/06/2023 00:00A19/06/2023 12:00
520/06/2023 06:00D 
620/06/2023 12:00E 
720/06/2023 18:00B19/06/2023 18:00
821/06/2023 00:00G 
921/06/2023 06:00E20/06/2023 12:00
1021/06/2023 12:00I 
1121/06/2023 18:00G21/06/2023 00:00
1222/06/2023 00:00K 
1322/06/2023 06:00L 
1422/06/2023 12:00K22/06/2023 00:00
1522/06/2023 18:00A 
1623/06/2023 00:00O 
1723/06/2023 06:00P 
1823/06/2023 12:00A22/06/2023 18:00
1923/06/2023 18:00R 
2024/06/2023 00:00S 
2124/06/2023 06:00D20/06/2023 06:00
2224/06/2023 12:00U 
2324/06/2023 18:00V 
2425/06/2023 00:00O23/06/2023 00:00
2525/06/2023 06:00X 
2625/06/2023 12:00Y 
Master
Cell Formulas
RangeFormula
T2:T26T2=IF(ISEVEN(COUNTIFS(S$2:S2,S2)),XLOOKUP(S2,S$1:S1,B$1:B1,,,-1),"")
 
Upvote 0
Solution
Fluff said:
Another option
Fluff.xlsm
ABST
1
219/06/2023 12:00A 
319/06/2023 18:00B 
420/06/2023 00:00A19/06/2023 12:00
520/06/2023 06:00D 
620/06/2023 12:00E 
720/06/2023 18:00B19/06/2023 18:00
821/06/2023 00:00G 
921/06/2023 06:00E20/06/2023 12:00
1021/06/2023 12:00I 
1121/06/2023 18:00G21/06/2023 00:00
1222/06/2023 00:00K 
1322/06/2023 06:00L 
1422/06/2023 12:00K22/06/2023 00:00
1522/06/2023 18:00A 
1623/06/2023 00:00O 
1723/06/2023 06:00P 
1823/06/2023 12:00A22/06/2023 18:00
1923/06/2023 18:00R 
2024/06/2023 00:00S 
2124/06/2023 06:00D20/06/2023 06:00
2224/06/2023 12:00U 
2324/06/2023 18:00V 
2425/06/2023 00:00O23/06/2023 00:00
2525/06/2023 06:00X 
2625/06/2023 12:00Y 
Master
Cell Formulas
RangeFormula
T2:T26T2=IF(ISEVEN(COUNTIFS(S$2:S2,S2)),XLOOKUP(S2,S$1:S1,B$1:B1,,,-1),"")
Click to expand...
Thank you for this!! Massively helped. It's got me 99% of the way there.

Is there possibly a way for the formula to repeat on the 3rd/5th duplicate found in column S to present the date from column B from the 2nd/4th duplicate.

The current formula seems to be working perfectly for 1st - 2nd duplicate and 3rd - 4th duplicate but doesn't pick up the dates from 2nd to 3rd etc.

Really appreciate your help
 
Upvote 0
RonFos3394 said:
The current formula seems to be working perfectly for 1st - 2nd duplicate and 3rd - 4th duplicate but doesn't pick up the dates from 2nd to 3rd etc.
Click to expand...
I thought that's what you wanted as you originally said
RonFos3394 said:
I only want it to look for 2nd duplicate then if it finds the same serial again to treat it as another entry and look for a 2nd duplicate.
Click to expand...
If you want it to show the previous date time for all duplicates try
Fluff.xlsm
ABST
1
219/06/2023 12:00A 
319/06/2023 18:00B 
420/06/2023 00:00A19/06/2023 12:00
520/06/2023 06:00D 
620/06/2023 12:00E 
720/06/2023 18:00B19/06/2023 18:00
821/06/2023 00:00G 
921/06/2023 06:00E20/06/2023 12:00
1021/06/2023 12:00I 
1121/06/2023 18:00G21/06/2023 00:00
1222/06/2023 00:00K 
1322/06/2023 06:00L 
1422/06/2023 12:00K22/06/2023 00:00
1522/06/2023 18:00A20/06/2023 00:00
1623/06/2023 00:00O 
1723/06/2023 06:00P 
1823/06/2023 12:00A22/06/2023 18:00
1923/06/2023 18:00R 
2024/06/2023 00:00S 
2124/06/2023 06:00D20/06/2023 06:00
2224/06/2023 12:00U 
2324/06/2023 18:00V 
2425/06/2023 00:00O23/06/2023 00:00
2525/06/2023 06:00X 
2625/06/2023 12:00Y 
Master
Cell Formulas
RangeFormula
T2:T26T2=IF(COUNTIFS(S$2:S2,S2)>1,XLOOKUP(S2,S$1:S1,B$1:B1,,,-1),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Pull values from For/Next statement?
Replies
3
Views
92
jmpatrick
J
H
  • Question Question
Finding matching Values in Column A and from those values highlight duplicates in another column
Replies
1
Views
103
hagia_sofia
hagia_sofia
R
  • Solved
Find Duplicates and change font color based on other cell value
Replies
3
Views
349
etaf
etaf
R
  • Solved
How to find duplicate values in one column while checking if a condition in another column is met
Replies
8
Views
378
Ramadan2512
R
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
357
MARZIOTULLIO
M

Forum statistics

Threads
1,224,823
Messages
6,181,179
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