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
Medium Priority
238 Views
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
Question by:Tavasan65
• 3
• 2

LVL 50

Assisted Solution

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

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

ID: 37021466
Wish I could delete my posts!  I didn't read the comment in the spreadsheet until too late.
0

LVL 50

Accepted Solution

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

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

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

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…
###### Suggested Courses
Course of the Month15 days, 17 hours left to enroll