Splitting data from one spreadsheet into multiple spreadsheets



I have on master spreadsheet on Sheet1. Column A has categories (i.e.
vegetables, fruits, nuts, etc.). Columns B through F have data corresponding
to that date (sales, expenses, profits, etc.).

I am trying to get the data from Sheet1 to split into multiple sheets based
on the category that was listed in Column A. For example, any time time
column A said Vegetables, it would copy that row's data into Sheet2.
Anytime column A said Fruits, it would copy that row's data into Sheet 3.

I know I can simply do this by using an IF formula in sheet2 stating that if
Sheet1!ColumnA states "Vegetables", copy the data into the corresponding
cells. However, that would keep the format on sheet 2 the same as sheet1.
So all the rows that don't state Vegetables in sheet 1 columnA would be blank
in sheet 2. I do not want all these blank rows in sheet 2, sheet 3, etc.

Is there any way to write a formula in sheet 2 that will fill each row
consecutively every time it finds that category in sheet 1 column A? I hope
I made myself clear. For example:

Sheet 1
Row Category
1 Vegetable
2 Fruit
3 Vegetable
4 Nuts
5 Vegetable

I want sheet 2 to look like this
1 Vegetable
2 Vegetable
3 Vegetable



Bernard Liengme

You and you alone know what is best for you work. But having said that it
should be pointed out that one of the cardinal rules for databases is not to
duplicate the data.

If you selected all the data in the master sheet and applied Data | Filter,
then any time you wishes you could use the filter to select, for example,
just "Nuts". You could even copy and paste it to its own worksheet to make a
special report and then delete that worksheet - data stored only ONCE

best wishes


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question