concatenating array results.

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118
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
ABCDEFGH
2PRsT2
320456RHHU
420456RHHU
520342RHHU
612456RHHU
7
8
920456WSS
1034678WSS
1134546WSS
1212345WSS
Sheet1



Sheet 2.

Excel Workbook
BC
3T2PR Numbers
4RHHU
5WSS
Sheet2
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows
As you mentioned trying MCONCAT I'm assuming that the funciton is avaliable to use, if not then it is VBA only.
Excel Workbook
ABH
1PRsT2
220456RHHU
320456RHHU
420342RHHU
512456RHHU
6
7
820456WSS
934678WSS
1034546WSS
1112345WSS
12
15
16T2PR Numbers
17RHHU20456, 20456, 20342, 12456,
18WSS20456, 34678, 34546, 12345,
Sheet
 

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 26, 2010
Messages
118

ADVERTISEMENT

great - thanks a million for your help
 

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118
{=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

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows
Should still work, what is in E1?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows
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&", ",""))}
 

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top