Pulling different part# based on multiple sortings

[Jamie Wagler]

Please refer to the attached file.

In the columns coloured green I am trying to pull the part # from the two yellow columns based on the matching reason from the red columns.

I have tried the following (green columns)

=IF(ISNA(INDEX($A$3:$B$60000,MATCH(Q$1,$A$3:$A$60000,0),2)),INDEX($D$3:$E$60000,MATCH(Q$1,$D$3:$D$60000,0),2),INDEX($A$3:$B$60000,MATCH(Q$1,$A$3:$A$60000,0),2))

But it will only pull the first part# where as I need to pull all that fall within the same cause. This could include duplicates of the same part# if either the same issue comes up or another issue emerges.

Columns H and K are counts based on Write IN or OUT reasons and Column N is the total for each.

Thanks!

 

[Luke M]

I'd strongly suggest putting your data into a re-organized table using headers of
In/Out | Cause | Part# | Date

and then you can quickly use a PivotTable to easily organize all the Causes and part numbers. This will make data analysis much easier. However, you can do what you want with just a formula. In Q3, put this array formula:

=IF(COUNTIF($A$3:$A$20,Q$1)>ROWS(Q$1:Q1),INDEX($B:$B,MIN(IF(($A$3:$A$20=Q$1)*(ISNA(MATCH($B$3:$B$20,Q$2:Q2,0))),ROW($A$3:$A$20)))),IF(COUNTIF($A$3:$A$20,Q$1)+COUNTIF($D$3:$D$20,Q$1)>ROWS(Q$1:Q1),
INDEX($E:$E,MIN(IF(($D$3:$D$20=Q$1)*(ISNA(MATCH($E$3:$E$20,Q$2:Q2,0))),ROW($A$3:$A$20)))),""))

Confirm the formula as an array by using Ctrl+Shift+Enter, not just Enter.
Copy down as far as would be needed, and then copy to the other columns.

 

[Jamie Wagler]

Luke M,

Your formula works great for the first few items but is only capturing the first 4 of 5 items under the OVER ISSUE heading and in the later columns (WO Error and further) not returning any values.

Any ideas?

 

[Luke M]

Grr, missed an equal sign. The two ">" checks need to be ">="

=IF(COUNTIF($A$3:$A$20,Q$1)>=ROWS(Q$1:Q1),INDEX($B:$B,MIN(IF(($A$3:$A$20=Q$1)*(ISNA(MATCH($B$3:$B$20,Q$2:Q2,0))),ROW($A$3:$A$20)))),IF(COUNTIF($A$3:$A$20,Q$1)+COUNTIF($D$3:$D$20,Q$1)>=ROWS(Q$1:Q1),
INDEX($E:$E,MIN(IF(($D$3:$D$20=Q$1)*(ISNA(MATCH($E$3:$E$20,Q$2:Q2,0))),ROW($A$3:$A$20)))),""))

Sorry about that.

 

[Jamie Wagler]

I just reviewed your formula after being away from my desk for awhile and came to the same conclusion.

Thanks!!!

 

[Jamie Wagler]

Luke,

I have a small curiosity with your formula, please see the attached file.

When the same item comes up under the same cause it appears as 0, and when other items are places in the same column the 0 moves down the list.

Any suggestions?

 

[Luke M]

Ah, I see now that the COUNTIF approach doesn't handle duplicates. Trying a different approach, this array formula in Z3 seems to work:
=IF(COUNTIF($A$3:$A$200,Z$1)+COUNTIF($D$3:$D$200,Z$1)<ROWS(Z$1:Z1),"",
IFERROR(INDEX($B:$B,(1/MIN(IF(($A$3:$A$200=Z$1)*(ISNA(MATCH($B$3:$B$200,Z$2:Z2,0))),ROW($A$3:$A$200))))^-1),
IFERROR(INDEX($E:$E,(1/MIN(IF(($D$3:$D$200=Z$1)*(ISNA(MATCH($E$3:$E$200,Z$2:Z2,0))),ROW($A$3:$A$200))))^-1),"")))

 

[Jamie Wagler]

Luke,

Don't know why but it is not working on mine, R709718 is still only appearing once. In this case I think I can make it work but I thought I would bring it to your attention.

 

[Luke M]

You said you didn't want duplicates, correct?

 

[Jamie Wagler]

Luke,

In my original email I said "This could include duplicates of the same part# if either the same issue comes up or another issue emerges."

Part of my original concern and problem that I was having was that it wouldn't pull duplicates. This would include items that appear multiple times under the same column or under the 'Write In' and 'Write Out" columns.

See the attached file.

I have added R709718 (highlighted in yellow) under the 'Write out' column. What I would like is for that item to appear 3 times in the Z column.

My apologies for the misunderstanding.

 

[Luke M]

It's ok, leaving in the duplicates is easier.

Array formula:
=IF(COUNTIF($A$3:$A$200,Z$1)>=ROWS(Z$1:Z1),INDEX($B:$B,SMALL(IF($A$3:$A$200=Z$1,ROW($A$3:$A$200)),ROWS(Z$1:Z1))),
IF(COUNTIF($A$3:$A$200,Z$1)+COUNTIF($D$3:$D$200,Z$1)>=ROWS(Z$1:Z1),INDEX($E:$E,SMALL(IF($D$3:$D$200=Z$1,ROW($A$3:$A$200)),ROWS(Z$1:Z1)-COUNTIF($A$3:$A$200,Z$1))),""))