If Statement Linked to Copy Paste Function

I am working on a project where I am attempting to sort entire rows of information on specific assets to different excel pages based on which asset pool they belong to. I have an excel page that has all of the assets I am working with and it is linked to a Google documents page to update every time we add an asset to the list. But I would like to have separate excel pages for each of the asset pools and I am trying to write a formula that will have excel automatically copy the new information from the master list to the specific excel page for the asset pool that this information belongs to.

I hope that reads well, essentially I am trying to sort information from an excel page to a series of different excel pages depending on a single variable. I created an "if" statement and have it set up so that depending on which asset pool an asset belongs to it will be copied and pasted to the proper excel page. My dilemma is that there are a lot of blank spaces between assets on each of the individual excel pages. Because I can only link each "if" statement to one asset at a time. So the first asset on the master list will automatically be copied into the excel page designated for its asset pool, while all of the other excel pages designated for the other asset pools will leave the first line blank. It has created blank rows between the assets on the individual asset pool lists.

Is there a way to rewrite the "if" statement so that there will be no blank rows in the asset pool excel pages, or so that the assets are copied one after another? Or is there another way to sort information depending on a single variable to different excel pages more effectively? Thanks for your help, if this is not perfectly clear please comment and I will work to clarify.

I have done this several times on this Forum using 3 basic formulae. In the main sheet you need to use a helper column which will uniquely identify each record by asset pool and a sequential number. This would be a formula like:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

in cell H2, say, assuming your asset pool is listed in column A starting with cell A2 (all these details help when you are posing a question, so that we can customise the answer for you). This formula would be copied down beyond your data (i.e. until you get hyphens), in order to accommodate more data being added in future.

Then in each of the subsidiary sheets I like to have another helper column (assume H again) with a formula which finds the rows in the main sheet where the records for that asset pool are located. You only need to set it up for one sheet, and then copy that sheet, rename it, and change the name of the asset. Assume you have the name of the asset in H1, then you can have this formula in H2:

=IFERROR(MATCH($H$1&"_"&ROWS($1:1),'Main Sheet'!$H:$H,0),"-")

Then in A2 you can have this formula to retrieve the matching data from column A of the main sheet:

=IF(OR($H2="",$H2="-"),"",IF(INDEX('Main Sheet'!A:A,$H2)="","",INDEX('Main Sheet'!A:A,$H2)))

and this can be copied across to G2, and then the whole of the second row can be copied down as far as you need it.

Then copy that sheet, rename it, and change the name of the asset in H1.

Hope this helps - in future it would be better if you posted an example workbook (the FAQ describes how to).

Hope this helps.

Pete