How to copy a rowdata when matching a value in a column to another worksheet

Copper Contributor



I am looking for help with an excelsheet I am putting together. I have data differences that are presented in column A of worksheet 1: Raw Material and Finished product. In the next couple of columns are some analysis results for both products.

What I want to do is the following:

ProductDateAnalysis 1Analysis 2

Raw material

Raw material2-1-2334
Finished product2-1-2356
Raw material3-1-2378
Finished product4-1-23910
Raw material4-1-235020
Raw material5-1-233040
Finished product6-1-2368


The data above is all in sheet1.

I want to copy the data into sheet 2 (Raw materials) or sheet 3 (Finished product) respectively. 

I am trying to get this automated but cannot figure it out. I automatically copy Raw material (a:d) data into sheet 2 and the Finished product (a:d) data into sheet 3. Can someone help me with this?

Many thanks!

6 Replies

@Larsvane I would use the FILTER function in the product sheets rather than copying rows from the main sheet. But if you insist on copying, you'll need VBA which isn't something I do.


See attached file.


=IFERROR(INDEX(A$2:A$9,SMALL(IF($A$2:$A$9="Raw material",ROW($A$2:$A$9)-1),ROW($A1))),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell F2 in the example and filled across range F2:I8.


If you work with Office 365 you can apply the FILTER function.


Another alternative that works for older versions of Excel could be advanced filter.

copy row data.JPG

Thanks for your reply. Okey I see how this works, but it does not work in another worksheet (after changing the values A2:A9 to worksheet!A2:a9) How can I incoperate this?


=IFERROR(INDEX(worksheet!A$2:A$9,SMALL(IF(worksheet!$A$2:$A$9="Raw material",ROW(worksheet!$A$2:$A$9)-1),ROW(worksheet!$A1))),"")

This formula works in my file. I have to confirm the formula with ctrl+shift+enter because the attached file is version Excel 2013. Does it work when you open the attached file?


@OliverScheurich Thanks for the reply. I tried it in your file and it worked. Then I tried the exact same in mine and it did not work fully. I attached part of the file i am using for it. In my case it should use the array of d4-d10. It also does not work in "blad 2"


I've adjusted the formulas and the intended results are returned in both sheets.