Finding numbers between values

freshgoblinmilk

New Member
Joined
Nov 7, 2022
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi all

I have 2 tabs as shown in the attached screenshots. One works out ID numbers in sequence. The other is for customer/unique ID numbers. What I need is to highlight both lines red when an ID number shows in both tabs as they can't be used more than once.

Problem is that columns E and F only show the first and last number in the batch, and the duplicated entry can't be either of those OR any numbers in between. I have made the bottom 2 rows to show what I mean. Both these lines need to come up as red as 11101 and 11102 will feature between IDs 11100 and 11104.

Ideally I wanted this without macros but at this point I will sacrifice a goat if required, so whatever solution you guys can find will help me keep my sanity.
 

Attachments

  • Screenshot_1.jpg
    Screenshot_1.jpg
    223.2 KB · Views: 42
  • Screenshot_2.jpg
    Screenshot_2.jpg
    188 KB · Views: 28

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
you can transform 1110-02 to 11102 with =SUBSTITUTE(A1,"-0","")
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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