Book1 | ||||||
---|---|---|---|---|---|---|
H | I | J | K | |||
1 | 1 | A | A B C D E F | A B C D E F | ||
2 | B | B C D E F | ||||
3 | C | C D E F | ||||
4 | D | D E F | ||||
5 | E | E F | ||||
6 | F | F | ||||
7 | 2 | A | A B C D E | A B C D E | ||
8 | B | B C D E | ||||
9 | C | C D E | ||||
10 | D | D E | ||||
11 | E | E | ||||
12 | 3 | A | A B C D | A B C D | ||
13 | B | B C D | ||||
14 | C | C D | ||||
15 | D | D | ||||
16 | 4 | A | A B C | A B C | ||
17 | B | B C | ||||
18 | C | C | ||||
19 | 5 | A | A B | A B | ||
20 | B | B | ||||
21 | 6 | A | A | A | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J1:J21 | J1 | =IF(H1,K1,"") |
K1:K21 | K1 | =IF(H2,I1,I1&" "&K2) |
Thanks for your suggestion, as I am new here I did not know that.What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use. Thanks
With helper column K
Book1
H I J K 1 1 A A B C D E F A B C D E F 2 B B C D E F 3 C C D E F 4 D D E F 5 E E F 6 F F 7 2 A A B C D E A B C D E 8 B B C D E 9 C C D E 10 D D E 11 E E 12 3 A A B C D A B C D 13 B B C D 14 C C D 15 D D 16 4 A A B C A B C 17 B B C 18 C C 19 5 A A B A B 20 B B 21 6 A A A Sheet1
Cell Formulas Range Formula J1:J21 J1 =IF(H1,K1,"") K1:K21 K1 =IF(H2,I1,I1&" "&K2)
New Microsoft Excel Worksheet (2).xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | 1 | A | ABCDE | A | B | C | D | E | |||
2 | B | ||||||||||
3 | C | ||||||||||
4 | D | ||||||||||
5 | E | ||||||||||
6 | 2 | A | ABCDEF | A | B | C | D | E | F | ||
7 | B | ||||||||||
8 | C | ||||||||||
9 | D | ||||||||||
10 | E | ||||||||||
11 | F | ||||||||||
12 | 3 | A | ABCD | A | B | C | D | ||||
13 | B | ||||||||||
14 | C | ||||||||||
15 | D | ||||||||||
16 | 4 | A | ABC | A | B | C | |||||
17 | B | ||||||||||
18 | C | ||||||||||
19 | |||||||||||
20 | |||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1,C16,C12,C6 | C1 | =CONCATENATE(D1,E1,F1,G1,H1,I1) |
D1:H1,D12:G12,D6:I6 | D1 | =IF(A1="","",TRANSPOSE(OFFSET(B1,,,AGGREGATE(15,6,IFERROR(1/(1/(((A2:$A$100)<>"")*ROW(A2:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(#REF!))-ROW()+1),1)))) |
D16:F16 | D16 | =IF(A16="","",TRANSPOSE(OFFSET(B16,,,AGGREGATE(15,6,IFERROR(1/(1/(((A17:$A$100)<>"")*ROW(A17:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(A1:A100))-ROW()+1),1)))) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
So if you can manually concatenate with few Helper column and if the data range for concatenate is few then you can try But Bo_Ry formula is worth applying
New Microsoft Excel Worksheet (2).xlsx
A B C D E F G H I 1 1 A ABCDE A B C D E 2 B 3 C 4 D 5 E 6 2 A ABCDEF A B C D E F 7 B 8 C 9 D 10 E 11 F 12 3 A ABCD A B C D 13 B 14 C 15 D 16 4 A ABC A B C 17 B 18 C 19 20 Sheet2
Cell Formulas Range Formula C1,C16,C12,C6 C1 =CONCATENATE(D1,E1,F1,G1,H1,I1) D1:H1,D12:G12,D6:I6 D1 =IF(A1="","",TRANSPOSE(OFFSET(B1,,,AGGREGATE(15,6,IFERROR(1/(1/(((A2:$A$100)<>"")*ROW(A2:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(#REF!))-ROW()+1),1)))) D16:F16 D16 =IF(A16="","",TRANSPOSE(OFFSET(B16,,,AGGREGATE(15,6,IFERROR(1/(1/(((A17:$A$100)<>"")*ROW(A17:$A$100)))-ROW(),LOOKUP("ZZZZZZ",B:B,ROW(A1:A100))-ROW()+1),1)))) Press CTRL+SHIFT+ENTER to enter array formulas.