Ordenar Dados Alfanumericos

Question

Bom dia.

Tenho uma Tabela como mostra a Fig 1 e fiz uma rotina para ordenar a coluna Funcionario da Tabela de nome "ShiHody" com codigo conforme Fig 2. Gostaria que ficasse na sequencia como Nome 1/2/3...10/11/12.... Tem como alterar o codigo para que ordene dessa maneira?

Desde ja agradeco

Tadao

Fig 1

Fig 2

 

Sub ordena()

Call Desproteger

With ActiveSheet.ListObjects(1).Sort
    With .SortFields
        .Clear
        .Add Key:=Range("ShiHody" & "[[#all],[Funcionario]]"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    End With
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Call Proteger

End Sub

Answer 1

A melhor forma de resolver seu problema é criar uma coluna auxiliar (começando em A8) que tenha uma fórmula do tipo =EXT.TEXTO(B8;5;255).

Assim, você ordena os dados pela coluna A.

Fazer um algoritmo para "entender" a classificação pela coluna B é desnecessariamente trabalhoso.

---

http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

Answer 2

Obrigado pelo retorno Felipe.

A sua sugestao de usar o Mid(string,start,length), funcionou perfeitamente, obrigado.....mas fui infeliz em colocar um exemplo para fins de  postar no forum,que tem numero de string fixo,no caso de ter um estoque de materiais e nele tem por exemplo parafuso1/2/3...arruela1/2/3...1o..20/21..com dados alfanumericos variaveis teria que identificar onde comeca o caracter numerico?

Tadao

Answer 3

A fórmula:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789"))

Retorna o índice do caractere em que aparece o primeiro número.

Então, use:

=MID(B8,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789")),255) para extrair o número.

Para converter o resultado da fórmul em número, você pode usar:

=MID(B8,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789")),255)*1

---

http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

Answer 4

Obrigado pela formula para extrair os numeros, Felipe.

Estava tentando entender a chave dentro do Find, se puder explanar agradeceria.

Pelo que entendi, deve ser uma constante matricial? Como ela atua dentro da funcao Find?, procura o valor que esta dentro da chave de cada vez na celula e se encontrar esse valor procurada retorna a sua posicao?qual a finalidade da concatenacao &"0123456789"?

Testei a formula e funcionou beleza, entao devo ordenar nessa coluna que retornou os numeros,nao e?...mas nesse caso vai ordenar como arruela1,parafuso1,aerruela2,parafuso2....em vez de arruela1, arruela2 ..... parafuso1, parafuso2 ... e nomes sem numeros ou que tem numeros no meio da frase retorna erro.

Sera que e possivel fazer uma formula na coluna auxilar para que qdo ordenado nessa coluna auxiliar a minha lista fica como abaixo?

Alicate

Bota No.1

Bota No.2

Bota No.3

Bota No.10

Bota No.11

Bota No.12

Enserado

Martelo

Perfilado

Parfuso7mm

Parfuso8mm

Parfuso9mm

Parfuso11mm

Parfuso12mm

em vez de ficar  ordenado como excel abaixo

Alicate

Bota No.1

Bota No.10

Bota No.11

Bota No.12

Bota No.2

Bota No.3

Enserado

Martelo

Parfuso11mm

Parfuso12mm

Parfuso7mm

Parfuso8mm

Parfuso9mm

Perfilado

Tadao

Answer 5

" procura o valor que esta dentro da chave de cada vez na celula e se encontrar esse valor procurada retorna a sua posicao?"
Sim!

"qual a finalidade da concatenacao &"0123456789"?"
Essa fórmual faz um FIND em todos os números de 0 a 9. Se um deles não for encontrado, a função retorna um erro. Por isso, coloca-se essa concatenação no final para garantir que a fórmula nunca retorne um erro, mas sim o número de caracteres + 1 no caso da expressão não ter nenhum número.

Estou vendo aqui, sua lista de itens é um pouco complexa porque tem itens:

-sem numeração nenhuma

-no formato TEXTO-NÚMERO

-no formato TEXTO-NÚMERO-TEXTO

Uma fórmula com funções nativas seria demasiadamente complexa para resolver seu problema. Sugiro criar uma rotina em VBA ou uma UDF para criar sua coluna auxiliar.

Cole o código abaixo num módulo regular:

Function CLASSIFICADOR(pCell As Range)
    Dim i As Long
    Dim Result As String
    Dim Numbers As String
    Dim Characters As String
    Dim iChar As String
    
    For i = 1 To Len(pCell.Text)
        iChar = Mid(pCell.Text, i, 1)
        If IsNumeric(iChar) Then
            Numbers = Numbers & iChar
        Else
            Characters = Characters & iChar
        End If
    Next i
    
    Result = Characters & Format(Numbers, "00000")
    
    CLASSIFICADOR = Result
End Function

Em A8, escreva:

=CLASSIFICADOR(C8)

Copie essa fórmula para baixe e classifique os dados por ela.

---

http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

Answer 6

Ola Felipe, obrigado pela Funcao que enviou.

Fiz conforme a sua orientacao, como estou traballhando com Tabela, espandi a Tabela ate a coluna A e na celula A8 coloquei a funcao que voce fez conforme mostra a Fig 3. A funcao retorna 0...ou eu fiz algo errado?

Tadao

Fig 3

Answer 7

Ola Felipe, mudei a Formula para =CLASSIFICADOR(B8), conforme mostra a Fig 4 e classifiquei pela coluna A da Tabela e resultou como na Fig 5, certissimo......muito obrigado. Agora e so proteger e ocultar a coluna A e usar a Tabela,nao?

Agradeco mais uma vez pela solucao e esplanacao das formulas usando constantes matriciais que ainda preciso aprender mais.Agora posso usar essa solucao em qualquer situacao.....Xièxiè(謝謝).

Tadao

Fig 4

Fig 5

Answer 8

"Agora e so proteger e ocultar a coluna A e usar a Tabela,nao?"

Sim!

Que bom que resolveu seu problema, até mais!

---

http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG