Filtrar e reordenar colunas no Excel

Question

Eu tenho um serviço (phplist, um gerenciador de newsletter) que exporta uma lista de usuários com vários campos. No final, cada usuário tem uma ou mais listas para as quais se inscreveu.

O problema é que a tabela não é ordenada como eu gostaria e, em vez de criar uma nova coluna para cada lista, cada linha cria as colunas necessárias. Este é um exemplo:

Oqueeuqueroé,seeutiver,porexemplo,oitoboletinsinformativos(listas),paraqueoExcelpossaconverteressatabelaemumaquecrieascolunasapropriadasepreenchaosdados.Oresultadodatabelaanteriorconvertidaseriaeste:

Ou algo similar (em vez de um Sim ou em branco, eu poderia ter um Sim e um Não, qualquer coisa). Dessa forma, eu poderia filtrar a tabela por lista, o que é impossível com a minha tabela atual: as colunas na tabela de origem, como você pode ver, podem conter listas diferentes em cada linha. Isso é possível no Excel?

Solução final:

Graças a W_Whalley, consegui encontrar a resposta real para o problema. Se alguém usou o PHPList, este gerenciador de newsletters permite que você faça o download da lista de usuários inscritos, mas, como mencionei na pergunta original, ela não fornece as listas em que você está inscrito de uma maneira boa. Na verdade, ele fornece uma coluna final com todas as listas na mesma célula. Isso é um pouco diferente do problema que eu considerei, porque uma linha dessa tabela seria:

Name | Surname |     Email    |    Lists

John | Perry | [email protected] | List1 List3 List6 List 7

E não

Name | Surname |     Email    |    Lists

John | Perry | [email protected] | List1 |  List3 | List6 | List 7

Eu propus a segunda tabela porque achei que era mais fácil de gerenciar, mas não era. Na verdade, eu tive que fazer uma pequena modificação para obter diferentes colunas para cada lista depois de exportar a lista de usuários do PHPList. Isso não era necessário.

Eu exportei a lista de usuários imediatamente, e a solução foi aplicar a fórmula sugerida por W_Whalley considerando apenas uma coluna de cada vez. Fazendo isso por várias colunas funcionou. A fórmula final (usando um exemplo de linha e coluna) foi:

=IF(ISERROR(SEARCH(L$1,$D2)),"no","yes")

Ou, na versão em espanhol do Excel (a que eu estava usando) com uma coluna de exemplo:

=SI(ESERROR(HALLAR($AJ$1;$AI27));"";"SI")

Espero que isso seja útil para alguém lá fora. Obrigado a todos, especialmente W_Whalley!

    

Answer 1

Aqui está uma solução não VBA. Assumindo que você tenha no máximo 8 listas (você pode ajustar conforme necessário) e que, por conveniência, a tabela com a qual você inicia começa na célula A1. Coloque os nomes das seqüências das listas nas células L1 a S1. Digite esta fórmula na célula L2 = SE (ISERROR (PESQUISA (L $ 1, $ D2 & $ E2 & $ F2 & $ G2 & $ H2 & $ I2 & $ J2 & $ K2)), "não", "sim") Copie esta fórmula de L1 para S2 e, em seguida, copie até onde você precisa ir.

O que está fazendo: O SEARCH ("listN", [concatenado "list1 ... list8"]) retorna o número inicial do índice da parte correspondente da string ou, se não encontrado, um erro #VALUE (pelo menos no LibreOffice..sorry, não tem o Excel para testar). A função ISERROR retorna "no" se houver um erro e "yes" se não, isto é, se a string "listN" for encontrada nos nomes das listas concatenadas.

Você pode filtrar a tabela usando a função de filtro automático. Parece funcionar com 60.000 linhas.

    

Answer 2

Esta é uma solução VBA, caso a solução de fórmula não atenda aos seus requisitos.

Eu dividi o código em pequenos blocos para que eu possa explicá-los individualmente. Eu inclua os comandos Debug.Print para que você possa entender o que cada bloco está fazendo. Eu espero ter o nível de explicação sobre o certo.

Option Explicit
' "Option Explicit" means you have to explicitly declare every variable
' but you will get a "variable not declared" warning if you try to run
' your code with a misspelt variable.

Sub Rearrange()

  Dim ColOldCrnt As Integer
  Dim ColOldMax As Integer
  Dim RowCrnt As Long         ' Long in case there are more than 32767 rows
  Dim RowMax As Long          ' Use same row variable for both sheets
  Dim SheetOld() As Variant

  ' The first block of code (down to "Debug.Assert False") assumes your
  ' current list is in worksheet "Sheet1".  Change the "With Sheets()"
  ' command as necessary.

  ' The code finds the bottommost row and the rightmost column and then
  ' loads the entire rectangle to array SheetOld.  It is much faster using an
  ' array than accessing individual cells as necessary.

  With Sheets("Sheet1")
    RowMax = .Cells.Find("*", .Range("A1"), xlFormulas, , _
                                               xlByRows, xlPrevious).Row
    ColOldMax = .Cells.Find("*", .Range("A1"), xlFormulas, , _
                                         xlByColumns, xlPrevious).Column
    SheetOld = .Range(.Cells(1, 1), .Cells(RowMax, ColOldMax)).Value
  End With

  Debug.Print "Max row = " & RowMax
  Debug.Print "Max col = " & ColOldMax

  Debug.Print "First 15 rows from old sheet"
  For RowCrnt = 1 To 15
    For ColOldCrnt = 1 To ColOldMax
      ' With two dimensional arrays it is normal to have the column as the
      ' first dimension.  With arrays loaded from a worksheet, the row is
      ' the first dimension.
      Debug.Print "|" & SheetOld(RowCrnt, ColOldCrnt);
    Next
    Debug.Print "|"
  Next

  Debug.Assert False     ' This stops the routine until you press continue (F5)
                         ' Press Ctrl+G if you cannot see the Immediate Window.

  ' Normally I would put all the variables as the top but I want to discuss each
  ' block's variables separately.

  ' This block builds in array "ListName()" a list of all the names.  The list
  ' is in the order in which names are found.  If you have a mispelt name (for
  ' example: "Lsit1") you will get a column for "Lsit1".  You may have to run
  ' the routine, correct any mispelt names and then rerun.

  ' This is not top quality code.  I have had to compromise between good
  ' and easy to understand.  I hope I have the balance right.

  Dim Found As Boolean
  Dim InxNameCrnt As Integer
  Dim InxNameCrntMax As Integer
  Dim NameList() As String
  Dim NameCrnt As String

  ' Using constants makes the code a little easier to understand.
  ' I use the same constants for both the old and new sheets because
  ' the important columns are in the same sequence.
  Const ColFirstList As Integer = 4

  ReDim NameList(1 To 100)      ' Bigger than could be necessary
  InxNameCrntMax = 0

  For RowCrnt = 2 To RowMax
    For ColOldCrnt = ColFirstList To ColOldMax
      ' Get a name out of the array and trim any leading
      ' or trailing spaces
      NameCrnt = Trim(SheetOld(RowCrnt, ColOldCrnt))
      If NameCrnt <> "" Then
        Found = False
        ' Search the current list for this name
        For InxNameCrnt = 1 To InxNameCrntMax
          If NameList(InxNameCrnt) = NameCrnt Then
            ' This name already recorded
            Found = True
            Exit For      ' Exit search
          End If
        Next
        If Not Found Then
          ' Add this name to the end of the list
          InxNameCrntMax = InxNameCrntMax + 1
          NameList(InxNameCrntMax) = NameCrnt
        End If
      End If
    Next
  Next

 Debug.Print "Names in order found:"
 For InxNameCrnt = 1 To InxNameCrntMax
   Debug.Print "|" & NameList(InxNameCrnt);
 Next
 Debug.Print "|"

 Debug.Assert False     ' This stops the routine until you press continue (F5)

 ' The next block builds the output worksheet in array SheetNew().

  ' I have used "Given" and "Family" instead of "Name" and "Surname" so I
  ' can reserve "Name" for the list names.
  Const ColGiven As Integer = 1
  Const ColFamily As Integer = 2
  Const ColEmail As Integer = 3

  Dim ColNewCrnt As Integer
  Dim ColNewMax As Integer
  Dim SheetNew() As String

  ' One column for the columns to the left of the first name and then
  ' one per name.
  ReDim SheetNew(1 To RowMax, 1 To ColFirstList - 1 + InxNameCrntMax)

  ' Copy across columns heading for the first columns
  For ColNewCrnt = 1 To ColFirstList - 1
    SheetNew(1, ColNewCrnt) = SheetOld(1, ColNewCrnt)
  Next
  ' Head the remaining columns with name
  For InxNameCrnt = 1 To InxNameCrntMax
    SheetNew(1, ColFirstList - 1 + InxNameCrnt) = NameList(InxNameCrnt)
  Next

  Debug.Print "First row from new sheet:"
  For RowCrnt = 1 To 1
    For ColNewCrnt = 1 To UBound(SheetNew, 2)
      Debug.Print "|" & SheetNew(RowCrnt, ColNewCrnt);
    Next
    Debug.Print "|"
  Next

 Debug.Assert False     ' This stops the routine until you press continue (F5)

 ' This block copies information from the old sheet to the new sheet

  For RowCrnt = 2 To RowMax
    ' Copy the initial columns unchanged
    For ColNewCrnt = 1 To ColFirstList - 1
      SheetNew(RowCrnt, ColNewCrnt) = SheetOld(RowCrnt, ColNewCrnt)
    Next
    For ColOldCrnt = ColFirstList To ColOldMax
      ' Get a name out of the old sheet and trim any leading
      ' or trailing spaces
      NameCrnt = Trim(SheetOld(RowCrnt, ColOldCrnt))
      If NameCrnt <> "" Then
        Found = False
        ' Search the current list for this name
        For InxNameCrnt = 1 To InxNameCrntMax
          If NameList(InxNameCrnt) = NameCrnt Then
            ' Name found
            Found = True
            Exit For      ' Exit search
          End If
        Next
        Debug.Assert Found  ' Name found on first pass but not second
                            ' Program error
        SheetNew(RowCrnt, ColFirstList - 1 + InxNameCrnt) = "Yes"
      End If
    Next
  Next

  Debug.Print "First 15 rows from new sheet:"
  For RowCrnt = 1 To 15
    For ColNewCrnt = 1 To UBound(SheetNew, 2)
      Debug.Print "|" & SheetNew(RowCrnt, ColNewCrnt);
    Next
    Debug.Print "|"
  Next

 Debug.Assert False     ' This stops the routine until you press continue (F5)

 ' This code assumes the destination sheet is "Sheet2". Change the
 ' "With Sheets()" command if necessary

 With Sheets("Sheet2")
   .Cells.EntireRow.Delete      ' Remove everything for the sheet
   .Rows(1).Font.Bold = True     ' Set the top row to bold
   'Load the worksheet from the array
   .Range(.Cells(1, 1), .Cells(RowMax, UBound(SheetNew, 2))).Value = SheetNew

 End With

 ' I have not bothered about column widths and the columns are in the
 ' sequence found.  You could add a dummy row at the top of the old sheet
 ' for John Doe who gets every list in the sequence you require.  Alternately
 ' you could sort the rows by hand.


End Sub

Espero que tudo isso faça sentido. Boa sorte, se você usar essa abordagem.