index/match multi columns as if it were one column

[dfida]

Hi,

I have several columns, each of which may be in separate tabs.
I am wondering if there is a way to index/match them as if they were just one long column?

Excel Workbook
	B	C	D	E	F	G
8	POOLE	HARRINGTON	GREGORY	JOHNSON	FLANAGAN	TAYLOR
9	FOUNTAIN	MURPHY	SMITH	VEAL	FLECK	HOPS
10	BARTHOLOMEW	LOPEZ	EVERTS	RICHARD	BURTON	GRAY
11	MARION	EVANS	LYONS	SURPRISE	WALKER	QUIGLEY
12	DURAN	DUFFIN	BALL	CARTER	LARSEN	RILEY
13	SINCLAIR	SMITH		LOWRIE	KRAMER	WALL
14	HALL	PRYOR		BELL	RANDALL	SIMPSON
15		MCEVERS		BRUNSON	FERGUSON
16		SHAW		CASANOVA	GALLOWAY
17		CARLSON		CUNNINGHAM
18		HARRISON		BYRD
19		HUTCHINSON		ROY
20		MCDONNELL		BOSLEY
21
22
23
24
25
26
27	ROW	COLUMN	INDEX/MATCH
28	1	1	POOLE
29	8	1	HARRINGTON
30	22	1	SMITH
Sheet1

Excel 2007

 

[jasonb75]

Try using the code from http://www.mrexcel.com/forum/showpost.php?p=2008823&postcount=9

With the formula =INDEX(ARRAYJOIN(B8:B14,C8:C20,D8:D12,E8:E20,F8:F16,G8:G14),B28)
in D28

 

[dfida]

Thanks, I added the function, but all I got was a #NAME? error.

 

[jasonb75]

I would guess that your security settings are blocking it, are you blocking macros by default?

 

[mikerickson]

Without a UDF, you might look at these two formulas.

In E28,
=SUMPRODUCT(($B$8:$G$20=D28)*(ROW($B$8:$G$20)+COLUMN($B$8:$G$20)/100))

In F28, the CSE formula
=MAX(($B$8:$G$20=D28)*(ROW($B$8:$G$20)+COLUMN($B$8:$G$20)/100))

Note that the results are absolute row/column; relative to the sheet, not the data range. Also, duplicate entries wreck the SUMPRODUCT formula.

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>27</b><td align="left" bgcolor=#FFFFFF>Name<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>28</b><td align="left" bgcolor=#FFFFFF>Poole<td align="right" bgcolor=#FFFFFF>8.02<td align="right" bgcolor=#FFFFFF>8.02</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>29</b><td align="left" bgcolor=#FFFFFF>Harrington<td align="right" bgcolor=#FFFFFF>8.03<td align="right" bgcolor=#FFFFFF>8.03</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>30</b><td align="left" bgcolor=#FFFFFF>Smith<td align="right" bgcolor=#FFFFFF>22.07<td align="right" bgcolor=#FFFFFF>13.03</tr>
</table>

 

[dfida]

I would guess that your security settings are blocking it, are you blocking macros by default?

Nope, I am using 2007 and saving as xlsm and also am able to run other macros.

 

[dfida]

Without a UDF, you might look at these two formulas.

Thanks, actually, sorry for not stating this up front, but I am looking create the array for use in another array...e.g. I would like to index this "single column created from multi columns" as a datasource in another part of the worksheet, using a formula like index(a:a, match(countif(....) etc.

Perhaps I will need to use helper columns or some similar strategy.

Thanks again for the help.

 

[jasonb75]

The function works fine for me, I assume you did put it in a regualr module and not a worksheet / workbook module?

Does it work if you change the first line of the function code to public?

Public Function ArrayJoin(ParamArray vArgs() As Variant) As Variant

 

[dfida]

The function works fine for me, I assume you did put it in a regualr module and not a worksheet / workbook module?

Does it work if you change the first line of the function code to public?

Public Function ArrayJoin(ParamArray vArgs() As Variant) As Variant

I created a new model "module 2" and pasted the code in there.


-- removed inline image ---

 

[jasonb75]

Then it should work. What formula did you use with it?