excel - TEXTJOIN para xl2010/xl2013 con criterios

excel-formula concatenation

Question

Tengo 2 hojas de trabajo. La primera hoja de cálculo tiene aproximadamente 100 filas, pero solo nos interesa la columna Y. Las celdas de la columna Y tienen una mezcla de celdas en blanco (""), texto y números, y celdas que muestran # N / A. Similar a la imagen pero con un conjunto de datos más grande.

En la segunda hoja de trabajo, hay una celda en la que me gustaría capturar las celdas con ''textos y números'' y mostrar cada registro en una línea diferente dentro de la misma celda (por ejemplo, si hubiera 12 de cada 100 celdas con ''textos y números '', luego me gustaría mostrar esta información en una celda en particular en la segunda hoja de trabajo. Así:

He intentado algo como esto pero parece que solo captura la primera fila del texto (por ejemplo, la fila del título):

=IFERROR(INDEX(''1Comms''!Y:Y,MATCH(TRUE,''1Comms''!Y:Y<>"",0)),"")

¿Hay alguna forma de perder el título también?

¿Qué estoy haciendo mal y hay una manera de hacer esto?

Answer 1

Esta función definida por el usuario de TextJoinIfs (también conocida como UDF) proporciona la funcionalidad básica de TEXTJOIN para las versiones de Excel 2003 - 2013, así como la funcionalidad expandida para todas las versiones al agregar el control de errores opcional, la singularidad, la clasificación y un conjunto de condiciones para criterios fáciles.

Este código UDF de TextJoinIfs pertenece a una hoja de código de módulo público; Por ejemplo, Libro1 - Módulo1 (código).

Option Explicit

Public Function TextJoinIfs(delim As String, iOptions As Long, iIgnoreHeaderRows As Long, _
                            rng As Range, ParamArray pairs()) As Variant
    ''TEXTJOINIFS - Basic TEXTJOIN functionality for XL2003-XL2013 versions
    ''              Expanded TEXTJOINIFS functionality for all versions
    '' =TextJoinIfs(<delimiter>, <options>, <header_rows>, <string_range>, [criteria_range1, criteria1], [criteria_range2, criteria2], …)
    ''        OPTIONS
    ''     +2 Include blanks
    ''     +4 Include worksheet errrors
    ''     +8 Unique list
    ''     +16 Sort ascending (cannot be used with 17)
    ''     +17 Sort descending (cannot be used with 16)

    If Not CBool(UBound(pairs) Mod 2) Then
        TextJoinIfs = CVErr(xlErrValue)
        Exit Function
    End If

    Dim i As Long, j As Long, a As Long, arr As Variant
    Dim bIncludeBlanks As Boolean, bIncludeErrors As Boolean, bUniqueList As Boolean
    Dim bSorted As Boolean, bDescending As Boolean

    bIncludeBlanks = CBool(2 And iOptions)
    bIncludeErrors = CBool(4 And iOptions)
    bUniqueList = CBool(8 And iOptions)
    bSorted = CBool(16 And iOptions)
    bDescending = CBool(1 And iOptions)

    Set rng = Intersect(rng, rng.Parent.UsedRange.Offset(iIgnoreHeaderRows - rng.Parent.UsedRange.Rows(1).Row + 1, 0))

    With rng
        ReDim arr(.Cells.Count)
        If Not IsMissing(pairs) Then
            For i = LBound(pairs) To UBound(pairs) Step 2
                Set pairs(i) = pairs(i).Resize(rng.Rows.Count, rng.Columns.Count).Offset(iIgnoreHeaderRows, 0)
            Next i
        End If

        For j = 1 To .Cells.Count
            If CBool(Len(.Cells(j).Text)) Or bIncludeBlanks Then
                If Not IsError(.Cells(j)) Or bIncludeErrors Then
                    If IsError(Application.Match(.Cells(j).Text, arr, 0)) Or Not bUniqueList Then
                        If IsMissing(pairs) Then
                            arr(a) = .Cells(j).Text
                            a = a + 1
                        Else
                            For i = LBound(pairs) To UBound(pairs) Step 2
                                If Not CBool(Application.CountIfs(pairs(i).Cells(j), pairs(i + 1))) Then Exit For
                            Next i
                            If i > UBound(pairs) Then
                                arr(a) = .Cells(j).Text
                                a = a + 1
                            End If
                        End If
                    End If
                End If
            End If
        Next j
    End With

    ReDim Preserve arr(a - 1)

    If bSorted Then
        Dim tmp As String
        For i = LBound(arr) To UBound(arr) - 1
            For j = i + 1 To UBound(arr)
                If CBool(LCase(CStr(arr(i))) < LCase(CStr(arr(j))) And bDescending) Xor _
                   CBool(LCase(CStr(arr(i))) > LCase(CStr(arr(j))) And Not bDescending) Then
                    tmp = arr(j): arr(j) = arr(i): arr(i) = tmp
                End If
            Next j
        Next i
    End If

    TextJoinIfs = Join(arr, delim)
End Function

Syntax:

=TextJoinIfs(<delimiter>, <options>, <header_rows>, <string_range>, [criteria_range1, criteria1], [criteria_range2, criteria2], …)

Syntax:

Ejemplo 1

Operación simple de TextJoin descartando espacios en blanco y errores, manteniendo solo cadenas únicas. Concatenado con un delimitador de avance de línea (vbLF) pero ignorando las dos primeras filas de encabezado y ordenado ascendente.

=textjoinifs(CHAR(10), 24, 2, A:A)

Ejemplo 2

La operación ampliada de TextJoinIfs descarta espacios en blanco y errores, manteniendo solo cadenas únicas. Concatenado con un delimitador de punto y coma. Un conjunto de condiciones de rango y criterios.

=textjoinifs("; ", 8, 0, B:B, A:A, A2)

Ejemplo 3

Operación de TextJoinIfs expandida descartando espacios en blanco y errores. Concatenado con una coma / delimitador de espacio. Múltiples pares de condiciones usando comparaciones matemáticas.

=textjoinifs(", ", 0, 0, B:B, A:A, ">="&D2, A:A, "<="&E2)

_{Muchas gracias a Lorem Ipsum Generator por el contenido de la cadena de muestra.}