• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF value THEN print corresponding text

Rick V.

Rick V.

New Member
Hi all,

I'm having a terrible time trying to figure out which formula to use when trying to print all corresponding text associated with a particular value.

Here is sample sheet: https://docs.google.com/a/sonicbids...cbwJbb_DunCZwzvdSaxLnTD5c0/edit#gid=452418696

Basically, I want to check column K for any records with a particular value e.g. 6 and then if true print corresponding value in column L into column A. Eventually, I'll modify that formula to print to columns B,C, etc. for their respective values. I hope this makes sense.

I've been using IF, ARRAY_CONSTRAIN, ARRAYFORMULA with no success. Here's the latest version I've been using:

=if(K2:K23=5, ARRAY_CONSTRAIN(L2:L23,10))

Thanks for your help!
 
Rick

Firstly, Welcome to the Chandoo.org Forums

I think you simply need in A2: =IF(K2=5, L2,"")

In A2: You may also want to try:
=IF($K2=VALUE(RIGHT(A$1,1)), $L2,"")
Which I think you can copy across and down
 
Please see the file referred to above
Note it has been made in Google Docs and so uses functions niot compatible with Excel like Join() in Column L
You can use in L2: =F2&"; "&G2&"; "&H2
copy down
 

Attachments

So here's the thing... this is a much smaller sample size than the actual. I basically would like a top down list of each line item associated with a particular sprint. e.g. All items (column L) in sprint 5 should be listed in order (without spaces between them) in their respective sprint columns. I basically want to show people what is planned for in these sprints without having to scroll all the way down given the data set is huge. I've also modified column L to use concatenate instead of join for you Excel-ers and attached file here.

It's ok if each sprint column (A,B,C,D, etc) has a different formula the key is to print the corresponding data in sequential order. Make sense? And thanks for your help!
 

Attachments

Last edited:
Problem was solved using the following FILTER formula.

=FILTER($L$2:$L$23,$K$2:$K$23=5)

Thanks everyone for your help.
 
Rick V

Chandoo.org is an Excel based forum

You provided a file using Google Docs format, which has functions like Join() and Filter() which aren't compatible with Excel.

Please be very clear about this in future.
 
You must log in or register to reply here.
Back
Top