Based on values in first two columns I need to populate third column (marked with yellow).
Is something like this possible without using VB?
Based on values in first two columns I need to populate third column (marked with yellow).
Is something like this possible without using VB?
EDIT:
If there are always four teams with two winners and two losers, there is a relatively simpler solution:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&",",LEFT(B1,FIND(",",B1)),""),MID(B1,FIND(",",B1)+1,LEN(B1))&",","")&",",",,","")
Yes, this is possible with just an array formula. It's not exactly simple, though.
The following is a general solution that works with any number of participants and winning teams:
Array enter (Ctrl+Shift+Enter) the following formula in C2
and copy-paste/fill-down into the rest of the table column (don't forget to remove the {
and }
):
{=
TEXTJOIN(
",",
TRUE,
IF(
MMULT(
--(
TRIM(MID(SUBSTITUTE(A2&",",",",REPT(" ",LEN(A2)+1)),(ROW(INDEX(C:C,1):INDEX(C:C,(LEN(A2)+1)/2))-1)*(LEN(A2)+1)+1,LEN(A2)+1))
=TRANSPOSE(TRIM(MID(SUBSTITUTE(B2&",",",",REPT(" ",LEN(B2)+1)),(ROW(INDEX(D:D,1):INDEX(D:D,(LEN(B2)+1)/2))-1)*(LEN(B2)+1)+1,LEN(B2)+1)))
),
ROW(INDEX(C:C,1):INDEX(C:C,1+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))))
),
"",
TRIM(MID(SUBSTITUTE(A2&",",",",REPT(" ",LEN(A2)+1)),(ROW(INDEX(C:C,1):INDEX(C:C,(LEN(A2)+1)/2))-1)*(LEN(A2)+1)+1,LEN(A2)+1))
)
)}
Note that TEXTJOIN()
is only available in Excel 2016. In earlier versions of Excel, a poly-fill UDF is required. (See this post for a basic one.)
Explanation:
coming soon
For now, though, hopefully the following partly evaluated version of the formula in C2
might help:
{=
TEXTJOIN(
",",
TRUE,
IF(
MMULT(
--(
{"A";"B";"C";"D"}
={"A","C"}
),
{1;2}
),
"",
{"A";"B";"C";"D"}
)
)}
TEXTJOIN()
. I don't understand what you mean with "without VB" as there is no VBA unless you don't have Excel 2016. Unless that's what you mean :-) PS I've updated the answer with the simpler non-general solution.
– robinCTS
Jul 4 '18 at 17:27