Split EXCEL file

Asked

Viewed 4,025 times

3

I have an excel database with 1 million emails in it, and I need to split it into 4 files of 250,000 emails each, does anyone know how I can do this with any excel function ? thank you

excel

  • you probably already solved the problem, since you did not answer the comments, maybe we can mark your question as solved?

2 answers

3

If you already know the total number of cells, just do it manually, go straight to a certain line (250,000) simply write the number of the line in question and press enter.

Excel

Or use advanced filters.

Here is an example using advanced filter

  • Do by hand, the break of 1 million lines should not be very easy, is not no?

  • 2

    @Math just select from line 01until line 250,000 cut the data and paste into another spreadsheet, will do it 04 times and will spend far less micro resources than leaving this task for Excel to manage by spending memory and processing and catching, at the risk of corrupting the spreadsheet!

  • Ok, but how to "Go to" the 250,000 line? That’s my question. Mouse scroll? Infinite page downs? That’s why I say not to be too easy.

  • 2

    @Math to Luana did not make clear if this spreadsheet will grow dynamically or it is already at its maximum limit, if it is already at the maximum limit is simple, Excel allows you to go straight to a certain line by simply writing the number of the line in question and selecting the data and ready. But if the spreadsheet is to grow dynamically, then it is more advantageous to use the advanced filter as proposed or even a simple database like Access or Mysql.

  • "go straight to a certain line by simply writing on the top left the number of the line in question" that’s the point I wanted to get at!! I used Excel my whole life and had never seen (watched) that box! And look who I looked for, before I saw your answer. If you put that in answer I vote for her.

  • 2

    @Math changed the answer, I think now this more didactic, this excel functionality is very useful.

  • If I put a print would be great, but the way it is already taught me something I didn’t know. + 1, already excellent

  • 2

    @Math you asked for, there it is.

  • For me that is the correct answer. Congratulations, and welcome to SOPT :)

Show 4 more comments

1

I believe a solution in VBA would be far more practical than a manual and tiring, so I did it here if I didn’t have experience with macro, I left the code well explained:

The idea is to take the coluna A and divide it according to the variable divisor. Worksheets are saved in the default folder Meus Documentos. The only concern is that for speed I used the simplest method of approximation that always surrounds for more: Asymarith, if you want another method, just look in this link article.

Sub Dividir_em_arquivos()
Dim ultimalinha As Integer, divisor As Integer
Dim resultado As Double
Dim nomeaba As String, nomearquivo As String, nomeaba_loop As String, nomearquivo_loop As String

'pega o nome da sheet e do arquivo que voce esta executando a macro
nomearquivo = ActiveWorkbook.Name
nomeaba = ActiveSheet.Name

'seleciona a ultima linha da COLUNA "A"
ultimalinha = Range("A" & Rows.Count).End(xlUp).Row

'------------- > coloque aqui divisor
divisor = 4

'calculo (SOMENTE NUMEROS PARES, usando o SymArith: https://support.microsoft.com/en-us/kb/196652/pt-br)
resultado = ultimalinha / divisor
resultado_apro = Int(resultado * 1 + 0.5) / 1


'loop para criar as planilhas e selecionar as colunas para copia
For i = 0 To divisor - 1
    resultado_inicio = (resultado_apro * i) + 1
    resultado_fim = resultado_apro * (i + 1)


    'set nome na planilha do loop
    nomeaba_loop = "numero" & i

    'cria planilha
    Set NewBook = Workbooks.Add
    With NewBook
        .Title = nomeaba_loop
        .SaveAs Filename:=nomeaba_loop
    End With

    'adiciona aba para colar as informações
    nomearquivo_loop = ActiveWorkbook.Name
    Sheets.Add.Name = nomeaba_loop

    'seleciona a planilha fonte
    Windows(nomearquivo).Activate
    Sheets(nomeaba).Select

    'Copia as informações da planilha fonte
    Range("A" & resultado_inicio & ":A" & resultado_fim).Copy

    'seleciona a planilha do loop
    Windows(nomearquivo_loop).Activate
    Sheets(nomeaba_loop).Select

    'cola as informações
    Range("A1:A" & resultado_apro).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    'seleciona novamente a planilha fonte para começar o loop
    Windows(nomearquivo).Activate
    Sheets(nomeaba).Select

Next i

End Sub

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.