Aladin's 3 columns

[jahnosha]

Hi all

if I have three columns of text that I want listed into one column what function would I use? I'm partially using this:

http://www.mrexcel.com/archive/Formulas/28674.html

in his example, how would I make a fourth column combining all the others>?

 

[jahnosha]

I just answered my own question... Never mind!

BigStr a workbook level definition, so it can be used everywhere in the workbook unchanged.[/QUOTE]

Ok, because I thought that was the issue which I'm currently trying to trouble shoot, the second iteration of this program brings back the correct number of individual items but brings them in pairs and incomplete,

if I had

FAD GAD
DAD NAD

It will bring something like
FAD
FAD
DAD
DAD
GAD
GAD
NAD
NAD

until the number of individual items is reached.

Any ideas?

 

[Aladin Akyurek]

I just answered my own question... Never mind!

BigStr a workbook level definition, so it can be used everywhere in the workbook unchanged.

Ok, because I thought that was the issue which I'm currently trying to trouble shoot, the second iteration of this program brings back the correct number of individual items but brings them in pairs and incomplete,

if I had

FAD GAD
DAD NAD

It will bring something like
FAD
FAD
DAD
DAD
GAD
GAD
NAD
NAD

until the number of individual items is reached.

Any ideas?

Good testing...

The definition of List3 is flawed. Change it to:

=OFFSET(Sheet1!$C$4,0,0,Sheet1!$C$2)

Would you check whether this corrects the output of the system?

 

[jahnosha]

No no, I think your definition of list 3 was spot on.
The issue was corrected the following

that in DLIST was supposed to be:
=IF(ROWS($C$4:C4)<=$D$2,INDEX(List3,MODE(IF(List3<>"",
IF(ISNA(MATCH(List3,D$3:D3,0)),MATCH(List3,List3,0)*{1,1})))),"")

but
D$3:D3 was actually D$2:D2 something so that it was counting the previous one as i dragged hence duplicated.

In this:
=COUNTIF(List1,"?*")

What does "?*" mean?

 

[Aladin Akyurek]

No no, I think your definition of list 3 was spot on.
The issue was corrected the following

that in DLIST was supposed to be:
=IF(ROWS($C$4:C4)<=$D$2,INDEX(List3,MODE(IF(List3<>"",
IF(ISNA(MATCH(List3,D$3:D3,0)),MATCH(List3,List3,0)*{1,1})))),"")

but
D$3:D3 was actually D$2:D2 something so that it was counting the previous one as i dragged hence duplicated.

I see. May I suggest to change the definition of List3 though?

In this:
=COUNTIF(List1,"?*")

What does "?*" mean?

This formula counts every cell in List1 that has a string which consists of at least 1 text char. The expression counts only text occurrences excluding the formula blanks (i.e., "").

 

[jahnosha]

I see,

Could you please explain to me the change of List 3? What difference is it making, right now my sheet is doing what it should.. I'll make an attempt to change it today in a copy regardless.

 

[Aladin Akyurek]

I see,

Could you please explain to me the change of List 3? What difference is it making, right now my sheet is doing what it should.. I'll make an attempt to change it today in a copy regardless.

The old definition...

=Sheet1!$C$4:INDEX(Sheet1!$C:$C,Sheet1!$C$2)

If Sheet1!$C$2 <= 4, the definition starting at C4 (row 4) won't cover the right area. The one with OFFSET will.

 

[jahnosha]

I understand, I know for a fact that my sheet will never allow that number to be less than 10. So it should be ok, is that assumption correct?

 

[Aladin Akyurek]

I understand, I know for a fact that my sheet will never allow that number to be less than 10. So it should be ok, is that assumption correct?

In that case, yes... I'm a bit biased toward robustness. That's why I bring such things up.

 

[jahnosha]

Yea, I understand, would you mind my asking what training/learning you've gone through to become so knowledgeable in Excel? School? Trial and error? I'm 23 years old and want to get better at this, I have great ideas on how to improve work in excel but takes me a long time to figure out what sort of functions/programming is needed.

 

[Aladin Akyurek]

Yea, I understand, would you mind my asking what training/learning you've gone through to become so knowledgeable in Excel? School? Trial and error? I'm 23 years old and want to get better at this, I have great ideas on how to improve work in excel but takes me a long time to figure out what sort of functions/programming is needed.

No, not by trial and error. Rather a formal science background.

I believe that the subject area is learnable. One way to achieve the learning is to examine the problems posted on forums like mrexcel and the solutions that are forwarded. Experimenting a lot with functions allows you understand their behavior and contributes greatly to that goal.