Creating a list from selected cells more easily

New Contributor

Hi,

 

I have a list of exercises for my clients. Each client has different exercises and the order is different for every person. I would like to create a list from the exercises that I select.

At present I can figure out some parts but not all. Your help to make this simpler would be appreciated.

 

Excel for Web or Desktop are both available to me.

Example

EXERCISE

STATUS

(green = good, yellow = untested, blank = untried)

Core exercise A 
Core exercise B 
Core exercise C 
Arm exercise A 
Arm exercise B 
Arm exercise C 
Leg exercise A 
Leg exercise B 
Leg exercise C 
Breathing exercise A 

Example List that I would like to produce: Core exercise C, Breathing exercise A, Leg exercise B, Arm exercise B, Core exercise C, Leg exercise C.

 

My workflow in Excel:

1. I select the cell alongside the exercise in the Status column (this is the first exercise in the list)

2. Then I hold Ctrl + select the next exercise in the list

3. While I hold Ctrl+ select the remaining exercises in the list, in order

4. I change the Background colour for all the selected cells to Green (Status = Good)

5. I would then like to copy a comma separated list of the chosen exercises in order for my notes.

 

TEXTJOIN seems to be a good formula to use but I'm not quite sure how to put this into a simple action, office script etc to make easily reproducible.

 

Thanks in advance,

 

Dom

4 Replies

@life-work-balance Hi there

 

I'm sure you could get this to work based on color formatting, but I'd suggest something with a helper column. You'd mark X in the helper column for what you want to include. Then you can use conditional formatting based on that column to mark the cells green if you want. 

 

For the concatenated list, it's a nested IF within a TEXTJOIN. 

 

SelectedEXERCISESTATUS
  (green = good, yellow = untested, blank = untried)
XCore exercise A 
 Core exercise B 
 Core exercise C 
XArm exercise A 
 Arm exercise B 
 Arm exercise C 
 Leg exercise A 
XLeg exercise B 
XLeg exercise C 
 Breathing exercise A 
   
   
   
 Output:Core exercise A, Arm exercise A, Leg exercise B, Leg exercise C

 

Output: =TEXTJOIN(", ",TRUE,(IF($A$3:$A$12="X",$B$3:$B$12,"")))

Conditional formatting for cell C3: =A3="X" (then set the background color to green, it will shade column C when you put an X in column A

 

@life-work-balance so it appears you want a list to copy and paste elsewhere and my original thought was to simply number the list and then create a helper column using SORT to arrange that list in the order you want.  HOWEVER I noticed you used the same exercise 2x in the list so things got complicated.  I created the following formula to parse a list with potential list of numbers:

 

=LET(in,A3:B12,
fin,FILTER(in,INDEX(in,,2)>0),
full_list,DROP(REDUCE(1,SEQUENCE(ROWS(fin)),LAMBDA(p,i,VSTACK(p,DROP(REDUCE("",TEXTSPLIT(INDEX(fin,i,2),","),LAMBDA(pp,ii,VSTACK(pp,IF({1,0},INDEX(fin,i,1),IFERROR(--ii,ii))))),1)))),1),
out,DROP(SORT(full_list,2),,-1),
out)

 

EXERCISESTATUS output list
(green = good, yellow = untested, blank = untried)  
Core exercise A  Core exercise C
Core exercise B  Breathing exercise A
Core exercise C1,5 Leg exercise B
Arm exercise A7,9 Arm exercise B
Arm exercise B4 Core exercise C
Arm exercise C8,10 Leg exercise C
Leg exercise A  Arm exercise A
Leg exercise B3 Arm exercise C
Leg exercise C6 Arm exercise A
Breathing exercise A2 Arm exercise C

 

in the attached example I have a table like the 1st 2 columns and the formula in the 4th to create the desired list in order.

Hi @ryanbackerman ,

That looks nice and simple. the Output is working well.

For some reason I can't get the conditional formatting to work though.
My settings:

Apply to range
C3:C12

Rule Type
Highlight cells with
Specific text
Containing
=A3="X" 
Format with Green Fill

Thanks @mtarler that works but it is more complicated for when I input the exercise. Cheers.