#1




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!! 
#2




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. Offhand 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. 
#3




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! 
#4




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. 
#5




Without the helper column but needing to be arrayentered (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")) Code:
M2 & "" & N2 & "" & O2 & "" & P2 Code:
M2&N2&O2&P2 2,32,32,32 and 23,2,2,232 both concatenate to the same 2323232 Using delimiters they're different: 2323232 2322232 so you won't get false finds. See attached where I have engineered such a false find. 
#6




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

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  06152018 05:26 AM 
Find and select all string of simlar pattern  anon123  Word  4  04202016 11:41 PM 
Extract data based on pattern with respect to specific column  PRA007  Excel Programming  14  12042015 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  12092014 05:48 PM 
Find & Replace: Wildcards (except this pattern)  tinfanide  Word  6  01262014 06:39 AM 