Mar 27 2023 01:49 AM
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!
Mar 27 2023 02:41 AM
@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.
Mar 27 2023 02:48 AM
=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.
Mar 27 2023 03:23 AM
Mar 27 2023 05:07 AM
=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?
Apr 13 2023 06:21 AM
@Quadruple_Pawn 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"
Apr 13 2023 07:00 AM