Cria uma lista separada por vírgula da coluna com espaços em branco, alimentados por caixas de seleção

Question

Eu quero fazer uma planilha em que o usuário possa marcar as caixas em uma planilha e fazer com que esses valores sejam trazidos, separados por vírgula, para uma célula, em outra planilha. Os valores das caixas de seleção precisam ser capazes de mudar à medida que uma nova entrada de linha é feita na primeira planilha.

Eu tenho o texto do nome associado das caixas de seleção preenchidas em uma coluna adjacente quando a caixa está marcada (VERDADEIRO).

Esta coluna é a que eu quero extrair o texto. Eu quero também ignorar espaços em branco e não incluir vírgulas extras.

Eu não estou familiarizado com o VBA, mas com um pouco de mão, eu poderia usar alguns. Abordagens de fórmulas inteligentes também são bem-vindas!

Aqui está um link para o documento de demonstração e a imagem das planilhas: link

    

Answer 1

Esta é uma resposta longa e complexa, que aborda todos os aspectos da sua pergunta. Isso exigirá que você adicione código à sua pasta de trabalho. Eu farei o meu melhor para orientá-lo.

Isenção de responsabilidade: Use isso por sua conta e risco. A melhor prática seria fazer uma cópia de backup do seu arquivo antes de começar. Salve seu trabalho com freqüência. Isso foi testado com o Excel 2010, com base nas informações fornecidas e serve apenas para ajudá-lo. Pode ser necessário ajustá-lo para atender às suas necessidades.

Primeiro, você precisará adicionar o seguinte código a um módulo no VBA Explorer. Isto pode ser aberto pressionando Alt + F11 . No painel esquerdo, expanda a pasta module . Se não houver um módulo, adicione um clicando com o botão direito do mouse e escolhendo Insert then Module . Clique duas vezes no módulo que você acabou de criar.

Agora, no painel direito, cole o código a seguir. Esse código pega os valores das colunas e os coloca em uma célula separada por um coma. Código de crédito - Microsoft MVPs McGimpsey & Associados.

'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs:  rRng - range to be concatenated
'         sDelimiter - optional delimiter
'            to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
      ByRef rRng As Excel.Range, _
      Optional ByVal sDelim As String = "") _
           As String
   Dim rCell As Range
   For Each rCell In rRng
      If rCell.Text <> "" Then
       MultiCat = MultiCat & sDelim & rCell.Text
      End If
   Next rCell
   MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Sub CopyRanges()
'Copy Months
Sheets("groups").Range("H2").Copy
Sheets("UserAccess").Range("D3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
'Copy Fruit
Sheets("groups").Range("H3").Copy
Sheets("UserAccess").Range("E3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
'Copy Color
Sheets("groups").Range("H4").Copy
Sheets("UserAccess").Range("F3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
'Copy Music
Sheets("groups").Range("H5").Copy
Sheets("UserAccess").Range("G3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues

'Reset check boxes
Dim ChkBox As Object
Dim Wks As Worksheet

  For Each Wks In Worksheets
    For Each ChkBox In Wks.CheckBoxes
      ChkBox.Value = xlOff
    Next ChkBox
  Next Wks
End Sub 

Agora, na folha groups , adicione essas fórmulas à célula H2, H3, H4 & H5 respectivamente. Isto é o que lê os valores da caixa de seleção e os coloca em uma célula.

=MultiCat(C2:C13,", ")
=MultiCat(F2:F6,", ")
=MultiCat(F8:F10,", ")
=MultiCat(F12:F15,", ")

Você deve poder testar isso marcando algumas caixas. Isso deve ler os valores da coluna C e F e, em seguida, colocá-los nas células para as quais você acabou de copiar as fórmulas.

Seascélulasnãoatualizaremquandovocêmarcarascaixasdeseleção,seránecessáriodefinirOptions>WorkbookCalculations>AutoCalculate.Nãosepreocupecomoformatodacolunaporqueagoraéapenasummarcadordeposiçãoeseráocultadomaistarde.

Adicioneumbotãoàfolhagroups.Sevocênãosoubercomofazerisso,sigaestasinstruções(useAdicionarumbotão(controledeformulário))- Adicione um botão e atribua uma macro a isto em uma planilha . Quando solicitar que a macro seja atribuída, selecione CopyRanges . Clique com o botão direito do mouse e escolha Edit Text para o que você quer dizer.

Desmarque Design Mode na faixa do desenvolvedor.

Selecione algumas caixas e clique no botão para experimentá-lo. Quando terminar, ele deve ter copiado os dados da coluna H para a próxima linha vazia na outra planilha e, em seguida, desmarcado as caixas de seleção da próxima entrada.

Quando estiver em funcionamento, oculte as colunas C , F & %código%. O Excel 2010 exigirá que você salve isso como uma pasta de trabalho H para que as coisas funcionem corretamente.

    

Answer 2

Compile a vírgula nos resultados que você já está usando [por exemplo, = IF (A2 = TRUE, B2 &"," , "")] e concatene conforme necessário, por exemplo, para cores =F8&F9&F10 . Se você não quiser a vírgula final, use LEN para contar o total de caracteres e, em seguida, LEFT com LEN-1.