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

Returning multiple results from a row/ column in a singe cell

M

marcus

New Member
Hello,


I have an array (to use a general term) of data where the X and Y axis are identical. The purpose is to identify relationship and correlation among the 30 odd categories (text values/ names). Since the axis are identical half, along the diagonal, of the cells do not contain data as this would be redundant.


I am attempting to return the values of the X axis in a single cell depending on whether a value (3, in this case) is in the same row. There will be situations where more than one value from the X axis will correspond with '3'; thus multiple values need to be returned.


A simple example is :

Cell a2 = Value1

Cell b1 = Value1

Cell c1 = Value2

Cell d1 = Value3

Cell e1 = Value4

Cell f1 = Value5

Cell c2 = 3

cell f2 = 3


Thus, the result (returned in g2) is Value2 Value5.


If it is not possible to return the result in one cell then it can be returned in adjacent cells.


I anticipate that I will use a similar process for the columns in the array and then CONCATENATE the results.


Thank you,


Marcus
 
Marcus


Can you post a sample file with your data and a sample of the answer you want so we can better understand it


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Sorry for the delay.


I posted a cleansed, sample file to:

https://dl.dropbox.com/u/95519877/Sample%20Correlation%20Matrix.xlsx


I included a legend in the file to describe some of the columns, rows and cells.


The desired output in column AQ which is driven by intermediate output in columns AR and AS.


Let me know if you have any questions.


Thank you,


Marcus
 
Hello,


Did anyone have an opportunity to consider this problem? I am still trying to sort through it.


Thank you,


Marcus
 
Hi Marcus ,


I don't think a formula-based solution is possible , since concatenation does not allow this.


Is VBA acceptable to you ?


However , if you wish to go through some information on the Internet , and try out on your own , here are some links :


1. http://www.mrexcel.com/forum/excel-questions/365691-concatenate-multiple-cells-array-formula.html


2. http://www.pcreview.co.uk/forums/concatenate-array-formula-t3387168.html


3. http://www.excelfox.com/forum/f2/concatinate-values-range-array-using-native-formula-298/


Narayan
 
Thank you for responding. I do not know how to program VBA but I will try to solve the problem with it.


In the meantime I created an inelegant but effective solution with a lot of helper columns, If statements and Concatenate.


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