Solved

# Choices will be enter in a single cell

Posted on 2011-10-24
197 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

LVL 50

Assisted Solution

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

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

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

LVL 50

Accepted Solution

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

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

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!