Dear All,
I was wondering if there's a way to combine data from column B to G into H to J, from smallest to biggest, ignoring the blanks
I was wondering if there's a way to combine data from column B to G into H to J, from smallest to biggest, ignoring the blanks
ART RECORD SHEET(IVF(HK)).xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
1 | CY NO (1) | CY NO (2) | CY NO (3) | CY # (1) | CY# (2) | CY# (3) | CY NO (1st) | CY NO (2nd) | CY NO (3rd) | ||
29 | 14 | 14 | |||||||||
30 | 15 | 15 | |||||||||
31 | 15 | 15 | |||||||||
32 | |||||||||||
33 | 16 | 16 | |||||||||
34 | |||||||||||
35 | 17 | 17 | |||||||||
36 | 19 | 19 | |||||||||
37 | 18 | 18 | |||||||||
38 | 24 | 24 | |||||||||
39 | 25 | 24 | 25 | ||||||||
40 | 25 | 25 | |||||||||
41 | 26 | 26 | |||||||||
42 | 27 | 26 | 27 | ||||||||
43 | 28 | 29 | 28 | 29 | |||||||
44 | 30 | 30 | |||||||||
45 | 30 | 30 | |||||||||
46 | |||||||||||
Tank |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B29:D46 | B29 | =IFERROR(INDEX(tableart[CY NO. (ALL)], SMALL(IF($BF29=tableart[LOCATION 1], ROW(tableart[LOCATION 1])-ROW(ART!$BH$2)+1), COLUMN(A28))),"" ) |
E29:G46 | E29 | =IFERROR(INDEX(tableart[CY NO. (ALL)], SMALL(IF($BF29=tableart[LOCATION 2], ROW(tableart[LOCATION 2])-ROW(ART!$BK$2)+1), COLUMN(A28))),"" ) |
H29:H46 | H29 | =IF($B29="",$E29,$B29) |
I29:I46 | I29 | =IF($C29="",$F29,$C29) |
J29:J46 | J29 | =IF($D29="",$G29,$D29) |
Press CTRL+SHIFT+ENTER to enter array formulas. |