Como reorganizar um grande arquivo do Excel

Question

Um amigo meu "herdou" uma planilha do Excel que foi usada como um arquivo de endereço.

Infelizmente, a pessoa que o criou teve a horrível ideia de fazer isso assim:

Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302

E, claro, meu amigo gostaria de reformatar isso como:

|Name         | Surname      |Tel       |City         |Street    |ZipCode |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |
|    ...      | ...          | ...      | ...         | ...      |  ...   |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |

Eu provavelmente poderia salvá-lo como csv, remendar um script Perl para analisar o csv e reorganizá-lo de uma maneira mais sensata, mas estou ocupado atm, por isso, se alguém tiver alguma solução de não programação, veria se ele consegue administre sozinho.

Notícia ruim A planilha não é regular. Ou seja Um endereço inteiro pode abranger mais de 2 linhas, e isso é mais ou menos aleatório. Aparentemente, esse é um tipo de relatório de algum programa de contabilidade de pequenas batatas, eles imprimem em um arquivo e depois o fazem em Excel. Não vou ver se parse-o-matic pode ajudar.

(Eu estou olhando para Parse-O-Matic porque eu usei para um problema semelhante anos atrás, existe uma versão básica gratuita, e espero que possa ser útil para o meu amigo como uma ferramenta de uso geral no futuro) .

    

Answer 1

É fácil. Digamos que você tenha seus dados a partir de A1 cell:

| A1 | B1 | C1 | ...

| A2 | B2 | C2 | ...

Na primeira célula vazia da linha superior, digite na fórmula =A2 e arraste / preencha a célula à direita para o mesmo número de colunas, para que sua planilha fique assim:

| A1 | B1 | C1 | ... | =A2 | =B2 | =C2 | ...

| A2 | B2 | C2 | ...

1. selecione todas as células com fórmulas na linha superior e arraste / preencha-as para o resto das linhas
2. selecione a planilha inteira, copie e cole os especiais (somente valores) em outra planilha
3. nessa nova planilha, exclua tudo, exceto as linhas com "Nome" na primeira coluna
4. crie cabeçalhos para as colunas com dados reais
5. excluir colunas com os cabeçalhos antigos

Answer 2

Não garanto que isso seja muito menos trabalhoso do que as outras ideias sugeridas, mas esta é a minha solução; Eu traduzi

para

(em Sheet2 ). As fórmulas são:

• A2 : =OFFSET(Sheet1!$B$1, ROW()*3-6, 0, 1, 1)
• B2 : =OFFSET(Sheet1!$D$1, ROW()*3-6, 0, 1, 1)
• C2 : =OFFSET(Sheet1!$F$2, ROW()*3-6, 0, 1, 1)
• D2 : =OFFSET(Sheet1!$D$2, ROW()*3-6, 0, 1, 1)
• E2 : =OFFSET(Sheet1!$B$2, ROW()*3-6, 0, 1, 1)
• F2 : =OFFSET(Sheet1!$F$2, ROW()*3-6, 0, 1, 1)

e, claro, você arrasta / preenche. O primeiro parâmetro em cada chamada OFFSET é a localização da primeira ocorrência do campo desejado na primeira planilha; Por exemplo, você deseja Tel na Coluna C e o primeiro Tel em Sheet1 está na célula F2 . Você multiplica o número da linha atual por 3 porque cada "registro" em Sheet2 é apenas uma linha, mas os registros em Sheet1 são três linhas cada. O -6 é apenas um fator de correção para fazer com que ele saia certo, supondo que você tenha títulos em Sheet2 e não há nenhum em Sheet1 - o primeiro registro está na linha 2 e 2 * 3 é 6, mas, para acessar Sheet1!B1 por um deslocamento de Sheet1!B1 , você precisa de um deslocamento de 0 (portanto, subtraia 6).

Você pode manter o Sheet2 vinculado à Folha1 na perpetuidade, ou copiar e colar valores e descartá-lo.

    

Answer 3

Eu pensei em uma possível solução:

1. Crie uma nova planilha vazia.
2. Na planilha original, crie um filtro automático e filtre por "Nome" na primeira coluna. Isso irá "selecionar" todas as linhas ímpares. Selecione e copie na nova folha.
3. Crie outro filtro, usando um conteúdo de célula diferente (Street). Isto irá selecionar todas as linhas pares. Copie-os na nova folha, colocando as linhas imediatamente à direita da primeira seleção.
4. A mesma abordagem pode funcionar se seus dados forem distribuídos em três ou mais linhas (meu exemplo tem apenas dois, mas isso pode funcionar para três ou mais padrões de linhas).
5. Crie uma linha na parte superior, adicione o nome do campo
6. Remova as colunas nas quais os rótulos de campo estavam.