Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-11-2020, 08:13 AM
DJ0691 DJ0691 is offline Find a specific number pattern in a column Windows 10 Find a specific number pattern in a column Office 2013
Novice
Find a specific number pattern in a column
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default Find a specific number pattern in a column

Hi Gurus,



I have a sheet with a long column with several thousand numbers in the rows. I need to search through this entire column and find a specific pattern of contingent numbers if it exists.

Example: column B1:B5678 contains many entries in the rows containing numbers. I'd like to enter something like in cells C1:C3 the numbers 15, 39 and 54. The formula or VBA would look through B1:B5678 and see if numbers 15, 39 and 54 are in contingent cells in this column and if so, tell me where.

I've explored the MATCH, FIND and SEARCH functions but they are typically looking for a single value while I'm looking for 3 values together in contingent cells.

Any ideas? Thanks!!
Reply With Quote
  #2  
Old 02-11-2020, 07:37 PM
BobBridges's Avatar
BobBridges BobBridges is offline Find a specific number pattern in a column Windows 7 64bit Find a specific number pattern in a column Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 685
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I'm not sure what you mean by "contingent cells", but I'm guessing you mean "contiguous" instead. "Contingent" means "dependent"; a "contingent fee", for example, means a fee that may or may not be imposed depending on other circumstances. You, I suspect, mean cells that are next to each other, right?

I may have done you a disservice by replying without offering a solution; some people will see that there's a reply (mine) and assume there's no need to look at your question. But maybe also they'll look at your question, not understand it and not answer it for that reason. If no one replies now, maybe repost the (corrected) question.

Off-hand I'm not sure how I would do this. Or, hm, maybe I can think of a way. If you create a helping column that concatenates the three cell values (one row above, this row, one row below), you can then search that column for the concatenated string. Would that help? I can explain better if it's not clear what I mean.
Reply With Quote
  #3  
Old 02-12-2020, 06:38 AM
DJ0691 DJ0691 is offline Find a specific number pattern in a column Windows 10 Find a specific number pattern in a column Office 2013
Novice
Find a specific number pattern in a column
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default Thanks

Thanks Bob - you are correct I did mean contiguous! I'd blame autocorrect but cant do that here

Yes, I get what you mean by a helper column as I've used them numerous times in the past for other projects. I'll give that a shot and thanks again for the idea!
Reply With Quote
  #4  
Old 02-12-2020, 12:30 PM
DJ0691 DJ0691 is offline Find a specific number pattern in a column Windows 10 Find a specific number pattern in a column Office 2013
Novice
Find a specific number pattern in a column
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default That worked great

Thanks again for the idea - worked like a charm. Here is what I did:
1) Created the helper column K and populated with =E3&E4&E5&E6 on each row. Column E has the individual numbers. The creates one larger number in that cell in column K.
2) Made an input area in cells M2 thru P2
3) In cell Q2, entered the formula:

=IF(P2="","",HYPERLINK("#'File'!E"&(MATCH((M2&N2&O 2&P2),K:K,0)),"Find Pattern"))

4) This creates a hyperlink for user that brings them right to the pattern they are looking for - and only displays when numbers are entered in the input area.
Reply With Quote
  #5  
Old 02-12-2020, 01:31 PM
p45cal p45cal is offline Find a specific number pattern in a column Windows 10 Find a specific number pattern in a column Office 2019
Expert
 
Join Date: Apr 2014
Posts: 528
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

Without the helper column but needing to be array-entered (with Ctrl+Shift+Enter, not just Enter):
Code:
=IF(P2="","",HYPERLINK("#'File'!E" & MATCH(M2 & "|" & N2 & "|" & O2 & "|" & P2,$E$1:$E$900 & "|" & $E$2:$E$901 & "|" & E3:$E$902 & "|" & $E$4:$E$903,0),"Find"))
Note that I've used the likes of:
Code:
M2 & "|" & N2 & "|" & O2 & "|" & P2
instead of:
Code:
M2&N2&O2&P2
only to be more robust because:
2,32,32,32
and
23,2,2,232
both concatenate to the same
2323232
Using delimiters they're different:
2|32|32|32
23|2|2|232
so you won't get false finds.


See attached where I have engineered such a false find.
Attached Files
File Type: xlsx msofficeforums44379.xlsx (154.3 KB, 2 views)
Reply With Quote
  #6  
Old 02-13-2020, 10:47 AM
DJ0691 DJ0691 is offline Find a specific number pattern in a column Windows 10 Find a specific number pattern in a column Office 2013
Novice
Find a specific number pattern in a column
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default Nice!

Very nice! - I changed my code to yours and it works great. Thanks a ton for your help again!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to check combinations of values in one column to find match from another column kong1802 Excel 1 06-15-2018 05:26 AM
Find and select all string of simlar pattern anon123 Word 4 04-20-2016 11:41 PM
Extract data based on pattern with respect to specific column PRA007 Excel Programming 14 12-04-2015 04:32 AM
Want a quotient using a constant to show up in column c each time a number is added to column b fibbermcghee Excel 2 12-09-2014 05:48 PM
Find & Replace: Wildcards (except this pattern) tinfanide Word 6 01-26-2014 06:39 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:14 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft