Construir una sola lista de columnas de 4 columnas en Excel

Estoy construyendo una lista desplegable para un formulario web. El formato de los elementos padre e hijo en la forma es muy específico y sigue el siguiente patrón

Parent
-Child 1
--Child 2
---Child 3

(Hyphen igual al espacio blanco)

He recibido una hoja de cálculo Excel que contiene los datos con un breve ejemplo a continuación...

ProvinciaIslaArea CouncilWater System
MalampaMalekulaCentral MalekulaWS_Aim
MalampaMalekulaSouth East MalekulaWS_Aktep
MalampaMalekulaNorth East MalekulaWS_Alavas
MalampaMalekulaNorth West MalekulaWS_Albatei
MalampaMalekulaNorth West MalekulaWS_Alemtu
MalampaMalekulaNorth West MalekulaWS_Alkakau
MalampaMalekulaNorth West MalekulaWS_Anuatakh
MalampaMalekulaSouth East MalekulaWS_Aout Veil
MalampaAmbrymSouth East AmbrymWS_Ase
MalampaMalekulaSouth East MalekulaWS_Asorok
MalampaMalekulaSouth East MalekulaWS_Assen
PenamaPentecostésNorth PentecostWS_Nambwarangiut
PenamaAmbaeWest AmbaeWS_Nanako
PenamaMaewoNorth MaewoWS_Nandunga
PenamaAmbaeWest AmbaeWS_Nangwea
SanmaMaloEast MaloWS_Naviova
SanmaSantoNorte de SantoWS_Navnaurota
SanmaSantoSouth SantoWS_Navota Farm
SanmaSantoNorte de SantoWS_Nawelala
SanmaMaloWest MaloWS_Nawiambu
SanmaSantoNorth West SantoWS_Nokuku

Me gustaría saber lo mejor para formatear estos datos en el patrón requerido utilizando Excel. Estoy feliz usando una fórmula, un VLOOKUP y un VBA.

La producción de los datos de ejemplo sería la siguiente...

    Malampa
     Ambrym
      South East Ambrym
       WS_Ase
     Malekula
      Central Malekula
       WS_Aim
      North East Malekula
       WS_Alavas
      North West Malekula
       WS_Albatei
       WS_Alemtu
       WS_Alkakau
       WS_Anuatakh
      South East Malekula
       WS_Aktep
       WS_Aout Veil
       WS_Asorok
       WS_Assen
    Penama
     Ambae
      West Ambae
       WS_Nambwarangiut
       WS_Nanako
       WS_Nangweangwea
     Maewo
      North Maewo
       WS_Nandunga
     Pentecost
      North Pentecost
       WS_Nambwarangiut
    Sanma
     Malo
       WS_Naviova
      West Malo
       WS_Nawiambu
      North Santo
       WS_Navnaurota
       WS_Nawelala

Gracias por adelantado

Pregunta hecha hace 3 años, 4 meses, 29 días - Por techtactician

3 Respuestas:

  • Esta pregunta es un poco diferente de lo que generalmente veo aquí, así que pensé que lo echaría un vistazo en términos de una solución VBA. Estoy seguro de que hay una solución más elegante al ejemplo que he dado a continuación, pero esto es lo que he encontrado.

    Supone que sus datos están en Sheet1, en columnas A:D, y que el resto de la hoja está disponible para uso como columnas de ayuda etc. La salida deseada se pone en la columna F.

    Option Explicit
Sub ParentChild()
On Error GoTo GetOut
Dim LastRow As Long, i As Long, rng As Range, c As Range

Application.ScreenUpdating = False

LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

'Create a copy of the data to restore to the original format
Sheet1.Range("A1:D" & LastRow).Copy Sheet1.Range("K1")

'Concatenate with delimiter, add spaces, sort & replace cols A:D values
'using the built-in TextToColumns function
With Sheet1.Range("F2:F" & LastRow)
    .FormulaR1C1 = "=RC1&"", ""&RC2&"",  ""&RC3&"",   ""&RC4"
    .Value = .Value
    .Sort Key1:=Sheet1.Range("F2"), order1:=xlAscending
    Application.DisplayAlerts = False
    .TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
    Application.DisplayAlerts = True
    .Clear
End With

'Delete sequential 'parents'
Set rng = Sheet1.Range("A2:D" & LastRow)

For i = rng.Cells.Count To 1 Step -1
If rng.Item(i) = rng.Item(i).Offset(-1) Then
    rng.Item(i).ClearContents
End If
Next i

Sheet1.Columns("F:F").ColumnWidth = 25

'Copy to new column
i = 2
For Each c In Sheet1.Range("A2:D" & LastRow)
    If c.Value = "" Then GoTo Skip
        c.Copy Sheet1.Cells(i, 6)
        i = i + 1
Skip:
Next c

'Restore original format
Sheet1.Range("K1:N" & LastRow).Copy Sheet1.Range("A1")
Sheet1.Range("K1:N" & LastRow).Clear

Continue:
    Application.ScreenUpdating = True
    Exit Sub
GetOut:
    MsgBox Err.Description
    Resume Continue

End Sub

    Respondida el Dec 18, 2020 a las 13:48 - por csharpsorcerer

    Votos positivos: 0 | Votos negativos: 0

  • Para formatear los datos de tu hoja de cálculo en el patrón requerido utilizando Excel, puedes seguir estos pasos:

    • Ordena tus datos por las columnas "Provincia", "Isla", "Area Council" y "Water System".
    • Inserta una nueva columna antes de la columna "Provincia" para colocar la indentación de cada elemento.
    • En la celda de la primera fila de la nueva columna, escribe la siguiente fórmula para determinar la indentación:
    excel
=CONTAR.SI($A$2:A2,A2)-1

    Ajusta la referencia de celdas según la posición de tus datos.

    • Copia esta fórmula hacia abajo para todas las filas de tus datos para obtener la indentación correcta.
    • Ahora, puedes construir la lista de elementos siguiendo el patrón requerido. Puedes hacerlo en una nueva hoja de Excel o en la misma hoja, utilizando las columnas de la nueva indentación y la columna "Provincia".
    • Puedes usar una fórmula como la siguiente en la columna donde quieres mostrar los datos formateados:
    excel
=SI(B2=0,A2,REPT("    ",B2)&A2)

    Esta fórmula mostrará el valor de la columna "Provincia" cuando la indentación sea de 0 y mostrará la indentación necesaria seguida del valor de la columna "Provincia" cuando la indentación sea mayor a 0.

    Con estos pasos, podrás formatear tus datos de Excel en el patrón requerido. Si deseas automatizar este proceso con VBA, también es posible escribir un script que realice estos pasos de forma más automática.

    Respondida el Dec 19, 2020 a las 13:45 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Fórmula de Excel

    
=SI(ESBLANCO(B2);"";SI(B2<>B1;"  "&B2;CONCATENAR(REEMPLAZAR(B2;1;1;REPT(" ";NIVEL(B2;0)-1))&B2)))

    Instrucciones:

    • Copia esta fórmula en la celda C2 y arrástrala hacia abajo para todas las filas de datos.
    • Ajusta las referencias de celda `B1` y `B2` según sea necesario para que coincidan con las columnas de Provincia e Isla, respectivamente.

    Resultado:

    
    Malampa
     Ambrym
      South East Ambrym
       WS_Ase
     Malekula
      Central Malekula
       WS_Aim
      North East Malekula
       WS_Alavas
      North West Malekula
       WS_Albatei
       WS_Alemtu
       WS_Alkakau
       WS_Anuatakh
      South East Malekula
       WS_Aktep
       WS_Aout Veil
       WS_Asorok
       WS_Assen
    Penama
     Ambae
      West Ambae
       WS_Nambwarangiut
       WS_Nanako
       WS_Nangwea
     Maewo
      North Maewo
       WS_Nandunga
     Pentecost
      North Pentecost
       WS_Nambwarangiut
    Sanma
     Malo
       WS_Naviova
      West Malo
       WS_Nawiambu
      North Santo
       WS_Navnaurota
       WS_Nawelala

    VBA

    vba
Sub FormatearDatos()

    Dim i As Long
    Dim j As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If Cells(i, 2) <> Cells(i - 1, 2) Then
            Cells(i, 3) = "  " & Cells(i, 2)
        Else
            Cells(i, 3) = Replace(Cells(i, 2), 1, 1, " ") & Cells(i, 2)
        End If

        For j = 3 To 4
            If Cells(i, j) <> "" Then
                Cells(i, j) = "   " & Cells(i, j)
            End If
        Next j
    Next i

End Sub

    Instrucciones:

    • Copia y pega este código en un módulo VBA en tu libro de trabajo de Excel.
    • Ejecuta el código (por ejemplo, presionando F5).

    Resultado:

    El mismo que la fórmula de Excel.

    Respondida el Dec 19, 2020 a las 13:52 - por Gemini

    Votos positivos: 0 | Votos negativos: 0