Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Choices will be enter in a single cell

Posted on 2011-10-24
6
Medium Priority
?
238 Views
Last Modified: 2012-05-12
Hello Experts,

What I have is a spreadsheet where there are multiple choices and based on selections of those choices each choice will be shown in a single cell.

I hope the sample is easy to understand. Test-Choice.xlsx
0
Comment
Question by:Tavasan65
  • 3
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 2000 total points
ID: 37021434
How are you hoping to do this? There's no native function in Excel to concatenate a range so if you want to accomplish this with formulas you'll need to concatenate a whole load of IF functions, e.g. in B3 copied down.

=SUBSTITUTE(IF(C3="x",","&C$2,"")&IF(D3="x",","&D$2,"")&IF(E3="x",","&E$2,"")&IF(F3="x",","&F$2,"")&IF(G3="x",","&G$2,"")&IF(H3="x",","&H$2,"")&IF(I3="x",","&I$2,"")&IF(J3="x",","&J$2,"")&IF(K3="x",","&K$2,"")&IF(L3="x",","&L$2,"")&IF(M3="x",","&M$2,"")&IF(N3="x",","&N$2,"")&IF(O3="x",","&O$2,"")&IF(P3="x",","&P$2,"")&IF(Q3="x",","&Q$2,"")&IF(R3="x",","&R$2,""),",","",1)

see attached

regards, barry
27413117.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 37021445
In cell C4, insert the following formula and copy across the columns to the right.
=IF(ISERR(FIND(C$2,$B4)),"","x")
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 37021466
Wish I could delete my posts!  I didn't read the comment in the spreadsheet until too late.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 37021467
I'm assuming that the requirement is the other way round, column B should be populated based on columns C to R. If that's right then another way to get the result I suggested is to download Morefunc add-in and use this array formula in B3 copied down

=SUBSTITUTE(MCONCAT(IF(C3:R3="x",","&C$2:R$2,"")),",","",1)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 37021500
You're right, barryhoudini.  The comment in the workbook confirms that
(**  Idea is based on selection on column C thru R will show in column B)
0
 

Author Closing Comment

by:Tavasan65
ID: 37021886
Thanks so much and the second solution is the best but the computer is locked by IT and will not allow the download.  That is why I gave both solutions credit.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question