Forum Discussion
How to copy a rowdata when matching a value in a column to another worksheet
Hello,
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:
| Product | Date | Analysis 1 | Analysis 2 |
Raw material | 1-1-23 | 1 | 2 |
| Raw material | 2-1-23 | 3 | 4 |
| Finished product | 2-1-23 | 5 | 6 |
| Raw material | 3-1-23 | 7 | 8 |
| Finished product | 4-1-23 | 9 | 10 |
| Raw material | 4-1-23 | 50 | 20 |
| Raw material | 5-1-23 | 30 | 40 |
| Finished product | 6-1-23 | 6 | 8 |
| etc. |
|
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!
=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.
- 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?
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.
