• 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.

Merge 3 columns to one column

B

bines53

Active Member
Hello friends !

I have a table of 6 columns and 11 rows.
I want to merge, all three columns possible.
There 19 combinations.
ABC
ABD
ABE
ABF
ACD
ACE
ACF
ADE
ADF
AEF
BCD
BCE
BCF
BDE
BDF
BEF
CDE
CDF
DEF

For example :ABC ,Columns represent =A,B,C,
BCD,Columns represent =B,C,,Etc.

All combinations, into one column.
Such format 14, 5, 17.
I have a column, you can do it tests Stats,Excel Functions. Thank you!
 

Attachments

  • test.10.xlsx
    8.7 KB · Views: 4
Bines53

In your example file you gave 1 single example yet rows 12-19 are blank. It is difficult to see a pattern when the dataset is incomplete. At any rate the long hand formula may be something like this.

=INDIRECT(LEFT(J1,1)&ROW(A1),1)&","&INDIRECT(MID(J1,2,1)&ROW(A1),1)&", "&INDIRECT(RIGHT(J1,1)&ROW(A1),1)


Please provide more guidance if this is not correct.

Take care

Smallman
 
Using Smallman's formula and rearranging your data I would have done this:
upload_2015-1-11_11-33-8.png

H2: =INDIRECT(LEFT(H$1,1)&ROW(),1)&","&INDIRECT(MID(H$1,2,1)&ROW(),1)&", "&INDIRECT(RIGHT(H$1,1)&ROW(),1)

See attached file:
 

Attachments

  • test.10.xlsx
    10.5 KB · Views: 1
Hi @Smallman ,@Hui

This can be done with the CHOOSE function?
Without volatile functions ?
Create a single column, having come all the possible19 columns ?

Thank you!
 
Bines53,
You shouldn't be scared of volatile functions.
Like all good things in life, it is there inappropriate or over use that is detrimental to spreadsheet performance
 
Hi Somendra,

Excellent!

I try to check if it can be done tests statistics, is not possible,
A simple example =COUNT(K2:K12),Returns 0.

Thank you!
 
Wow

This post looks like you are about to choose a much longer formula based on something you have read about Volatile functions.

I am afraid I am with Hui on this one. Volatile functions are really only a problem in very large workbooks where calculation processing power is compromised. If your file is nimble you don't even notice they are there. I use them and they work as expected.

Take care

Smallman
 
@Smallman

Just for your information.

@bines53 a.k.a David is really handling big files with loads of data and loads of CF ( :) ). All the members who regularly helps him by formulas knows this things that somebody has done some magical spell (like Hermione :p) on him against the Volatile functions and when they make formula's they keep this thing in Mind.

Regards,
 
Hi Somendra,

This is, I want as a number, how do I convert the formulas you have built,
with function value ?

Regards,
 
Hi Somendra,

Construction of me, a nice formula and I can not use, because I only work with numbers!?
If I understand correctly, all the merge of cells, cell gets text format ?

Regards,
 
The formulas uses & and , which are concatenation operator and , make it text.

So for the example you shown COUNT is working same as COUNTA. Use COUNTA.

Regards,
 
You must log in or register to reply here.
Back
Top