One column list from multiple columns

[Tresfjording]

Hi ...
I've got this Excel sheet contaning 29 columns from E:AG with different words. You see, the norwegian alphabet has 29 letters.
Columns varying from a 100 rows to a few hundred thousand.

I want to, in a search cell, type for example, ??rem??, and get a one column list of all seven lettered words containing letters r, e, and m as letters number 3, 4 and 5.

It seems I am unable to get this list using FILTER, INDEX, MATCH, LAMDA, ISNUMBER, SEARCH, FIND, the moment I search in a named range or more than one column.

What am I doing wrong?

I have Office 365 for Busines with all updates running on a Win 11 Pro fairly powerful computer with 32GB RAM and a i5processor.

 

[Herakles]

Hi ...
I've got this Excel sheet contaning 29 columns from E:AG with different words. You see, the norwegian alphabet has 29 letters.
Columns varying from a 100 rows to a few hundred thousand.

I want to, in a search cell, type for example, ??rem??, and get a one column list of all seven lettered words containing letters r, e, and m as letters number 3, 4 and 5.

It seems I am unable to get this list using FILTER, INDEX, MATCH, LAMDA, ISNUMBER, SEARCH, FIND, the moment I search in a named range or more than one column.

What am I doing wrong?

I have Office 365 for Busines with all updates running on a Win 11 Pro fairly powerful computer with 32GB RAM and a i5processor.

Can we please have some good data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Muhammad_Usman]

1235.xlsm
	A	B	C	D	E
1	12	AAREMBB	12
2	12	12	AAREMBB		AAREMBB
3	AAREMBB	AAAREM	REMBB		AAREMBB
4					AAREMBB
5
6
Sheet9

Here is vba code for this you can change your data range and sheet name as well

Sub ExtractWordsWithRem()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetCell As Range
    
    ' Set your target worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet9")  ' Change "Sheet9" to your sheet name
    
    ' Set the target cell where you want to start displaying the list
    Set targetCell = ws.Range("E2")  ' Change to your desired starting cell
    
    ' Loop through each cell in columns A to C
    For Each cell In ws.Range("A1:C5")  ' Change the range as needed
        ' Check if the cell value is 7 characters long and contains "rem" in positions 3, 4, and 5
        If Len(cell.Value) = 7 And Mid(cell.Value, 3, 3) = "REM" Then
            ' Display the word in the target cell
            targetCell.Value = cell.Value
            ' Move to the next target cell
            Set targetCell = targetCell.Offset(1, 0)
        End If
    Next cell
End Sub

 

[Fluff]

How about

=LET(d,TOCOL(E2:AG1000,1),FILTER(d,(MID(d,3,3)="rem")*(LEN(d)=7)))

 

[Peter_SSs]

How about

=LET(d,TOCOL(E2:AG1000,1),FILTER(d,(MID(d,3,3)="rem")*(LEN(d)=7)))

Yesterday I was considering something along those lines but did not post because of this

Columns varying from a 100 rows to a few hundred thousand.

It would only take a few of those 29 columns to contain a few hundred thousand items for the TOCOL to fail.
Just in case there are no more than 1,048,576 entries altogether in the 29 columns, this was my suggestion which also allows for ..

I want to, in a search cell, type for example, ??rem??

Tresfjording.xlsm
	B	C	D	E	F	G	H
1	??rem??	AAREMBB		12	AAREMBB	12
2		xxREMyy		12	ABREMCDEF	abcdefg
3		abremcd		xxREMyy	AAAREM	REMBB
4					abremcd	abcdrem
5
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=LET(tc,TOCOL(E1:AG500000,1),FILTER(tc,(SEARCH(B1,tc&"|"&B1)=1)*(LEN(B1)=LEN(tc)),""))
Dynamic array formulas.

If there could be more than 1,048,576 entries altogether in the 29 columns then my vba approach would be a user-defined function as shown below.
I have used these assumptions

1. Each of the 29 columns has multiple entries (this was indicated in post 1 with "Columns varying from a 100 rows to ...")
2. There are no blanks among the list of entries in a particular column
3. There will be no more than 65,536 values in the result list.

Option Explicit
Option Compare Text

Function GetWords(rng As Range, SearchText As String) As Variant
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long
  
  If rng.Rows.Count = Rows.Count Then
    lr = rng.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    a = rng.Resize(lr).Value
    ReDim b(1 To 1, 1 To 65536)
    b(1, 1) = "N/A"
    For j = 1 To UBound(a, 2)
      For i = 1 To UBound(a)
        If Len(a(i, j)) = 0 Then
          Exit For
        Else
          If a(i, j) Like SearchText Then
            k = k + 1
            b(1, k) = a(i, j)
          End If
        End If
      Next i
    Next j
    ReDim Preserve b(1 To 1, 1 To IIf(k = 0, 1, k))
    GetWords = Application.Transpose(b)
  Else
    GetWords = "Please use whole columns"
  End If
End Function

Tresfjording.xlsm
	B	C	D	E	F	G
1	??rem??	xxREMyy		12	AAREMBB	12
2		AAREMBB		12	ABREMCDEF	abcdefg
3		abremcd		xxREMyy	AAAREM	REMBB
4					abremcd	abcdrem
5
Sheet3

Cell Formulas
Range	Formula
C1:C3	C1	=GetWords(E:AG,B1)
Dynamic array formulas.

 

[Fluff]

It would only take a few of those 29 columns to contain a few hundred thousand items for the TOCOL to fail.

Missed that bit.