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]

17

 

[Aladin Akyurek]

17

I've made a mistake regarding the definition of List3. It should be:

=Sheet1!$C$4:INDEX(Sheet1!$C:$C,MATCH(BigStr,Sheet1!$C:$C))

The formula in D2 should now give 9 as result.

 

[jahnosha]

D2 is still bringing back NA and what was 17 is now 20.
The lists im working off of is a list of 10, and 10, they dont match. the third list brings back the 10 and 10 into one list of 20. these are dynamic by the way but i don't think that changes anything.

 

[Aladin Akyurek]

D2 is still bringing back NA and what was 17 is now 20.
The lists im working off of is a list of 10, and 10, they dont match. the third list brings back the 10 and 10 into one list of 20. these are dynamic by the way but i don't think that changes anything.

The communication would be easier if we have the same samples...

 

[jahnosha]

http://www.4shared.com/file/39ScDjUo/sort.html

the stuff we were talking about is in targets tab.
i've brought it thus far from utilization detail to rank the top 10 in each time frame, my end goal is to take the top ten of each and plot them on the bar chart. if it exists in one time period and not the other, it should still plot it and not plot for the other time frame, so it could bring up more than 10, but at least 10.

utilization graph is the same info just put in a chart. Targets is a bunch of different stuff i've gotten from mr. excel to pull info.

let me know what you think, I really appreciate your time and effort.

 

[jahnosha]

what i have it currently doing is wrong as it only pulls if the name matches in the row, AND adds the distinct from each list. cells Q38 and on.

 

[How_Do_I]

A strange one... I’ve copied everything that Aladin has written on this thread and my Jeanie looks like it is working….
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
The only thing that has confused me is the definition of List3. I didn’t think the formula would put the “dancing ants” around cells that contain the formula but show no value… i.e. the cells I’ve copied the formula down to but are blanked out by the formula “on/off” switch.

Excel Workbook
	A	B	C	D
1			6
2			16	7
3	List1	List2	List3	#Dlist#
4	Arsenal	Blackburn	Arsenal	Arsenal
5	Arsenal	Blackpool	Arsenal	Aston Villa
6	Aston Villa	Bolton	Aston Villa	Chelsea
7	Aston Villa	Chelsea	Aston Villa	Blackburn
8	Chelsea	Arsenal	Chelsea	Blackpool
9	Chelsea	Aston Villa	Chelsea	Bolton
10		Birmingham City	Blackburn	Birmingham City
11		Blackburn	Blackpool
12		Blackpool	Bolton
13		Bolton	Chelsea
14			Arsenal
15			Aston Villa
16			Birmingham City
17			Blackburn
18			Blackpool
19			Bolton
20
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[jahnosha]

sorry password is 02368

 

[jahnosha]

Hey Aladin,

This was a really great success btw, thanks so much for all your help!
I've initiated a second phase where in one sheet I do this process multiple times. does the 'BigStr' defined name remain the same or can I use this so long as I update the other lists?

 

[Aladin Akyurek]

Hey Aladin,

This was a really great success btw, thanks so much for all your help!

Glad to hear this... You are welcome.

I've initiated a second phase where in one sheet I do this process multiple times. does the 'BigStr' defined name remain the same or can I use this so long as I update the other lists?

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