Find the nth instance of a number in a range of cells-CodePudding

I have a spreadsheet that contains entries for a general ledger account that we need to reconcile.
Ideally every entry starts with a set of numbers but sometimes due to human error or other reasons, the entries might have the number within the string of characters. For example, this is how the entry should look:

33333333 Name 12345

But sometimes it can look like this:

Name 33333333 12345

12345 Name 33333333 Fee

What I'm trying to do is have a search for the number and tell me all the different cells that contain but don't necessarily start or end with that 33333333 number. Right now I have figured out how to get the first instance of the number, return the cell reference and then pull the value from the cell reference. Here is the formula for the cell reference:

=CELL("address",INDEX(C9:C199,MATCH("*"&B1&"*",C9:C199,0)))

Where C9:C199 is the range of cells I'm looking up and B1 is the Lookup cell, which will change depending on what number the Searcher wants to lookup. For example, the result could be $C$19 and then I have the Indirect function giving me the value of that cell reference. But I want to be able to pull the 1st, 2nd, 3rd, etc instance of a cell reference because there could be many, up to 5 or 6 cells that contain the number in some way (not all of which are relevant to what we're trying to reconcile but we want to see all the instances). So how do I get the 2nd instance of this formula, such that if there was a second answer of $C$26, I could also do an indirect reference to that cell and get that value as well.

=CELL("address",INDEX(C9:C199,MATCH("*"&B1&"*",C9:C199,0)))

My ultimate goal is to be able to easily see the following such that visually I can easily see that they offset. I've thrown in a random "fee" word because some users like to throw in random stuff like that:

33333333 Name 12345 Debit $500

Name 33333333 12345 Fee Credit $500

33333333 Name 12345 Other Fee Debit $250

Visually you would see the two $500 fees offset and then the remaining would be the $250 entry. Ultimately I would delete the two $500 entries to have the remaining $250 as the leftover to be offset at a future date. I can do the deleting of entries once I figure out how to get the nth cell reference instance.

Thank you!

CodePudding user response：

You can try a custom function like this if you don't mind messing around with VBA and have Excel 365 or 2021 (dynamic array support is required for this function to be used in a cell). Add the function to a new module in the project and call it with =GetMatches(search range, search term). Also note, I've made this case insensitive with the use of LCase on both the search range and search term.

Function GetMatches(rng As Range, searchTerm As String) As Variant
Dim c As Range
Dim matches As New Collection
Dim output() As Variant
Dim iMax As Integer
Dim i As Integer

Set rng = Intersect(rng.Worksheet.UsedRange, rng)

For Each c In rng
    If InStr(1, LCase(c), LCase(searchTerm)) > 0 Then matches.Add c
Next c

iMax = matches.Count
ReDim output(1 To iMax, 1)

For i = 1 To iMax
    output(i, 0) = matches(i)
    output(i, 1) = matches(i).Address
Next i

GetMatches = output

End Function

CodePudding user response：

One way to do this, with Power Query:

Name cell B1 as 'tblSearchTerm' (create a ListObject) then name the source table as 'Table1' (create a LisObject).

If not shown, open the pane "Queries & Connections' (Data>Queries & Connections).

In 'ResultingSearchTable' right click and choose 'Load to...' then choose a starting cell to load the resulting search table.

If any term is found, the data will be listed as follows:

|RowNumber|NumberInRow|

RowNumber is the DataBodyRange row number of the ListObject.

let
        tblSearchTerm = Excel.CurrentWorkbook(){[Name="tblSearchTerm"]}[Content],
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"AddedIndex" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
        #"DividedColumnByDelimiter" = Table.ExpandListColumn(Table.TransformColumns(AddedIndex, {{"TextLines", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TextLines"),
        #"ChangedType" = Table.TransformColumnTypes(#"DividedColumnByDelimiter",{{"TextLines", type text}}),
        #"ReplacedValue" = Table.ReplaceValue(#"ChangedType","$","",Replacer.ReplaceText,{"TextLines"}),
        #"ChangedType1" = Table.TransformColumnTypes(#"ReplacedValue",{{"TextLines", type number}}),
        RemovedErrors = Table.RemoveRowsWithErrors(ChangedType1, {"TextLines"}),
        #"RenamedColumns" = Table.RenameColumns(RemovedErrors,{{"Index", "RowNumber"}, {"TextLines", "NumberInRow"}}),
        #"FinalFilter" = Table.NestedJoin(#"RenamedColumns", {"NumberInRow"}, tblSearchTerm, {"SearchTerm"}, "tblSearchTerm", JoinKind.Inner),
        #"ResultingSearchTable" = Table.SelectColumns(FinalFilter,{"RowNumber", "NumberInRow"})
in
        #"ResultingSearchTable"

CodePudding user response：

With data in C2:C199 (adjust as needed)

B2: (the address)  =ADDRESS(AGGREGATE(15,6,1/ISNUMBER(FIND($B$1, $C$2:$C$199))*ROW($C$2:$C$199), ROW(INDEX($A:$A,1):INDEX($A:$A, COUNTIF(C2:C199,"*"&$B$1&"*")))),3)

A2: (the contents) =INDEX($C:$C,AGGREGATE(15,6,1/ISNUMBER(FIND($B$1, $C$2:$C$199))*ROW($C$2:$C$199), ROW(INDEX($A:$A,1):INDEX($A:$A, COUNTIF(C2:C199,"*"&$B$1&"*")))))