concatenating array results.

[switters_aka]

Trying to be helpful here and paste in Excel Jeanie HTML code for the two sheets this example is based on. Hope it helps. Anyway, the main problem is the following: Sheet 1 lists purchase requests in the PR column for various purchases with respective T2 codes. The PR numbers can repeat in one T2 panel and in different T2 panels.

On sheet 2 in the PR column I want to list horizontally the PR numbers for each T2, preferably without using VBA. I saw something on another forum yesterday using MCONCAT(Lookup()) but can't for the life of me get it to work on this scenario. Any help would be much appreciated. Thanks in advance.

4

Excel Workbook
	A	B	C	D	E	F	G	H
2	PRs							T2
3	20456							RHHU
4	20456							RHHU
5	20342							RHHU
6	12456							RHHU
7
8
9	20456							WSS
10	34678							WSS
11	34546							WSS
12	12345							WSS
Sheet1

Sheet 2.

Excel Workbook
	B	C
3	T2	PR Numbers
4	RHHU
5	WSS
Sheet2

 

[Andrew Poulsom]

Copy Chip Pearson's StringConcat function into a general module:

http://www.cpearson.com/Excel/stringconcatenation.aspx

Then in C2 type:

=StringConcat(",",IF(Sheet1!H$2:H$11=B2,Sheet1!A$2:A$11,""))

and confirm with Ctrl+Shift+Enter. Note that it doesn't eliminate duplicates.

 

[switters_aka]

Thanks Andrew, but I was looking for a NON VBA solution - see first line of para 2 from my original post. Is VBA the only solution to this?

 

[jasonb75]

As you mentioned trying MCONCAT I'm assuming that the funciton is avaliable to use, if not then it is VBA only.

Excel Workbook
	A	B	H
1	PRs		T2
2	20456		RHHU
3	20456		RHHU
4	20342		RHHU
5	12456		RHHU
6
7
8	20456		WSS
9	34678		WSS
10	34546		WSS
11	12345		WSS
12
15
16	T2	PR Numbers
17	RHHU	20456, 20456, 20342, 12456,
18	WSS	20456, 34678, 34546, 12345,
Sheet

 

[switters_aka]

thanks for that. Do you know of a way to handle duplicates with MCONCAT?

 

[jasonb75]

Only by use of a helper column in the source data table.

Helper formula is filled down to I11


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 179px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PRs</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">T2</TD><TD>Excel 03</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20456</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">RHHU</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20456</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">RHHU</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20342</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">RHHU</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12456</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">RHHU</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20456</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WSS</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34678</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WSS</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34546</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WSS</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12345</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">WSS</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>T2</TD><TD>PR Numbers</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>RHHU</TD><TD>20456, 20342, 12456, </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>WSS</TD><TD>20456, 34678, 34546, 12345, </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I2</TD><TD>=SUMPRODUCT(--(A$2:A2=A2),--(H$2:H2=H2))</TD></TR><TR><TD>B17</TD><TD>{=MCONCAT(IF(($H$2:$H$11=A17)*($I$2:$I$11=1),$A$2:$A$11&", ",""))}</TD></TR><TR><TD>B18</TD><TD>{=MCONCAT(IF(($H$2:$H$11=A18)*($I$2:$I$11=1),$A$2:$A$11&", ",""))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[switters_aka]

great - thanks a million for your help

 

[switters_aka]

{=MCONCAT(IF(($H$1:$H$10=INDIRECT(E1&ROW()))*($I$1:$I$10=1)*(A1:A10<>""),$A$1:$A$10&", ",""))}

I was expecting a problem here I need to use indirect references - hence the INDIRECT(E1&ROW()) in the first logical argument of the IF statement. It is returning the correct value on F9 but gives a #VALUE error when it executes the whole function. Is this an MCONCAT issue?

 

[jasonb75]

Should still work, what is in E1?

 

[jasonb75]

I stand corrected, it doesn't work, the problem appears to be use of indirect in array formula.

There may be other "better" ways to overcome the problem, but this seems to work.

{=MCONCAT(IF(($H$1:$H$10=CELL("contents",INDIRECT(E1&ROW())))*($I$1:$I$10=1)*(A1:A10<>""),$A$1:$A$10&", ",""))}