Create list of duplicates based on occurances in another list

Having tried many search terms I cannot find anything that is similar to my problem, so I'm hoping someone can help!

Eng list.xlsx

The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.

I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.

I hope that makes sense...!

With this code.

After that you can filter on column C in sheet output.

See the attached file.

Please Login or Register  to view this content.

Or, with a couple of array formulas**:

In A14:

=IF(ROWS($1:1)>COUNTIF($E$4:$I$7,"Y"),"",INDEX($A$4:$A$7,MATCH(TRUE,MMULT(0+(ROW($A$4:$A$7)>=TRANSPOSE(ROW($A$4:$A$7))),COUNTIF(OFFSET($E$4:$I$4,ROW($A$4:$A$7)-MIN(ROW($A$4:$A$7)),,,),"Y"))>=ROWS($1:1),0)))

In B14:

=IF(E14="","",INDIRECT(TEXT("3000"&RIGHT(SMALL(IF($E$4:$I$7="Y",10^6*ROW($E$4:$I$7)+COLUMN($E$4:$I$7)),ROWS($1:1)),3),"R000000C000000"),0))

Copy both down until you start to get blanks.

Note that the "3" in the "3000" in the second formula should match the row number equivalent to that of the top row of your table.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Can you re-post the attachment with your attempt at moving the formulas?

Regards

Thanks. Your (array) formula in Sheet2 A2 should be:

=IF(ROWS($1:1)>COUNTIF(Sheet1!$E$4:$I$7,"Y"),"",INDEX(Sheet1!$A$4:$A$7,MATCH(TRUE,MMULT(0+(ROW(Sheet1!$A$4:$A$7)>=TRANSPOSE(ROW(Sheet1!$A$4:$A$7))),COUNTIF(OFFSET(Sheet1!$E$4:$I$4,ROW(Sheet1!$A$4:$A$7)-MIN(ROW(Sheet1!$A$4:$A$7)),,,),"Y"))>=ROWS($1:1),0)))

And that in B2:

=IF(A2="","",INDIRECT("'Sheet1'!"&TEXT("3000"&RIGHT(SMALL(IF(Sheet1!$E$4:$I$7="Y",10^6*ROW(Sheet1!$E$4:$I$7)+COLUMN(Sheet1!$E$4:$I$7)),ROWS(Sheet1!$1:1)),3),"R000000C000000"),0))

Regards

You're welcome!