Sep 18 2019 12:35 PM
I am usually pretty good with formulas but can't figure this one out! I have a bunch of data in the chart on the left that needs to be reorganized into the format in the middle. The ultimate output should look like the one on the right.
The problem is that you can't really use Vlookup or index/match because there is no unique value (i.e. there are two pairs of Run1 and QC Low....that have 2 different values. In other places I have used an array, but that doesn't really work because the column sizes are different. I've also used an array with SMALL to sort the data....this works well, but that would move values from Dil and Run2 (i.e. 10 and 11) up two cells next to Run1, which isn't right.
I wonder if anyone has a unique solution to this?
Thanks,
Greg
DATA | HOW TO POPULATE CHART? | How it should be populated | |||||||||||
QC | File | how many | High | Low | Dil | High | Low | Dil | |||||
High | Run01 | 1 | Run01 | ? | ? | ? | Run01 | 1 | 2 | ||||
Low | Run01 | 2 | Run01 | ? | ? | ? | Run01 | 4 | 3 | ||||
Low | Run01 | 3 | Run02 | ? | ? | ? | Run02 | 5 | 6 | 10 | |||
High | Run01 | 4 | Run02 | ? | ? | ? | Run02 | 8 | 7 | 11 | |||
High | Run02 | 5 | |||||||||||
Low | Run02 | 6 | |||||||||||
Low | Run02 | 7 | |||||||||||
High | Run02 | 8 | |||||||||||
Dil | Run02 | 10 | |||||||||||
Dil | Run02 | 11 |
Sep 18 2019 10:35 PM
Hi @gms4b
Your requirement is quite achievable, since you look for two different values for each category therefore you need to use two different formulas (one for minimum value & other for maximum value). You can try below formulas,
=MIN(IF($A$3:$A$12=F$2,IF($B$3:$B$12=$E3,$C$3:$C$12)))
=MAX(($A$3:$A$12=F$2)*($B$3:$B$12=$E4)*$C$3:$C$12)
Please use (Ctrl + Shift + Enter) as they are array formula.
I have also attached a sample file for your reference. Please let me know if it works for you.
Thanks
Tauqeer