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. | ||
