#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. 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. |
#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 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")) 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: 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. |
#6
|
|||
|
|||
Nice!
Very nice! - I changed my code to yours and it works great. Thanks a ton for your help again!
|
|
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 |