How to find various values from a spreadsheet

Question

Hello, I have a spreadsheet with lots of data, and I would like to separate them each into a spreadsheet by category, example: all products that are with the date of September are shown in another spreadsheet or all the codes of products that repeat are placed in a separate spreadsheet, there is some function for this as if it were a process but for a set of values

Answer 1

You can do it in a "macro" of Excel. The code below in VBA makes the essentials of what you need and is similar to macro.

The first step is to sort the datasheet according to the category you need to separate. If you cannot change the original worksheet, copy the data to another worksheet and sort (the code shows how to copy to another worksheet).

Once with the sorted data it is easy to check that data range corresponds to category who wants to separate.

I called it Planilla1 the data sheet and Planilla2 the one that will receive data from a category.

See that I use two variables of type "string", one with the track to copy and one with the track to paste.

Sheets("Planilha1").Activate
'Ativa a Planilha de dados

Planilha.Rows(FaixaParaCopiar).Select
'Seleciona faixa a copiar 

Selection.Copy
'Copia os dados selecionados para a área de transferência

Sheets("Planilha2").Activate
'Ativa a Planilha para receber uma categoria de dados

Planilha.Rows(FaixaOndeColar).Select
'Seleciona e prepara para colar

ActiveSheet.Paste
'Cola na Planilha da categoria escolhida

You can test that it works!

There is no "function" that does this at once, there are several solutions besides VBA, such as using "macros" or the "dynamic table" (it is worth knowing, if applicable).