1

Based on values in first two columns I need to populate third column (marked with yellow).

image

Is something like this possible without using VB?

  • I can suggest you UDF (VBA) will work as a Excel Formula without any problem, please confirm whether you need this or not? – Rajesh S Jul 4 '18 at 16:10
  • Are there always 4 possibilities with 2 winning teams or does the numbers vary? – Kevin Anthony Oppegaard Rose Jul 4 '18 at 16:26
  • It is always 4 participants, 2 winners and 2 losers, and I need it without VBA – user3760419 Jul 4 '18 at 17:03
  • @user3760419, the solution what I was suppose to suggest can work with any combination 4 +2 or 5+3 or with words and number even and it's very simple to use ! – Rajesh S Jul 5 '18 at 6:51
  • @RajeshS, unfortunately VB is not an option – user3760419 Jul 5 '18 at 8:11
2

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:

Worksheet Screenshot

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"}
  )
)}
  • The explanation will have to wait until after I get some sleep ;-) – robinCTS Jul 4 '18 at 16:39
  • Wow, tnx Robin! I will check if 2016 will be avaliable. As I always have 4 teams with 2w and 2l could the solution be simplified so that it can run without text join and without VB? – user3760419 Jul 4 '18 at 17:10
  • @user3760419 You just caught me ;-) Yes it can be simplified so it doesn't use 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
  • Perfect, now I have both great generic solution (in case 2016 is available) or a specific ones that will support all versions. Tnx !!! – user3760419 Jul 5 '18 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.