Index Match Concatenate

mkay13

New Member
Joined
Jun 3, 2015
Messages
1
Hey everyone,

I know there are tons of posts out there on this, but I just can't seem to wrap my head around it at all. I'm not able to post an attachment of a sample workbook so I'll explain my dilemma.

I have one workbook with two spreadsheets: Partners and Projects. In the Partner tab, I have listed Partner names in one column, and left the Project List column empty. In the Projects tab, I have listed all the different projects and in another column, which partners these projects have been assigned to. I need to be to lookup all project names assigned to each partner in the second spreadsheet and enter them in the first spreadsheet under "Project List" but in one cell only separated by columns. Can someone help me develop a formula for this and an explanation of each step? Thank you so much in advance, internet!!!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I have a sort of workaround that I've just developed. Please know that I don't currently use this in any of my files and can't vouch for it's consistency based on any experience using or relying on it. However, if you're up for trying it (and adapting it to fit your situation) then here it is:

For my example setup, I begin my table in cell A1, and it occupies A1:E10. My row headers are "Project", "Partner", "ID", "Manager Count", and "Assembly" in columns A through E:

Table 1:
Code:
[TABLE="width: 1253"]
<tbody>[TR]
[TD]Project
[/TD]
[TD]Partner
[/TD]
[TD]ID
[/TD]
[TD]Manager Count
[/TD]
[TD]Assembly
[/TD]
[/TR]
[TR]
[TD]Bridge A
[/TD]
[TD]Alfred
[/TD]
[TD]Alfred1
[/TD]
[TD]1
[/TD]
[TD]Bridge A
[/TD]
[/TR]
[TR]
[TD]Bridge B
[/TD]
[TD]Brady
[/TD]
[TD]Brady1
[/TD]
[TD]1
[/TD]
[TD]Bridge B
[/TD]
[/TR]
[TR]
[TD]Tower A
[/TD]
[TD]Collin
[/TD]
[TD]Collin1
[/TD]
[TD]1
[/TD]
[TD]Tower A
[/TD]
[/TR]
[TR]
[TD]Tower B
[/TD]
[TD]Collin
[/TD]
[TD]Collin2
[/TD]
[TD]2
[/TD]
[TD]Tower A, Tower B
[/TD]
[/TR]
[TR]
[TD]Tower C
[/TD]
[TD]Brady
[/TD]
[TD]Brady2
[/TD]
[TD]2
[/TD]
[TD]Bridge B, Tower C
[/TD]
[/TR]
[TR]
[TD]Condo A
[/TD]
[TD]Alfred
[/TD]
[TD]Alfred2
[/TD]
[TD]2
[/TD]
[TD]Bridge A, Condo A
[/TD]
[/TR]
[TR]
[TD]Condo B
[/TD]
[TD]Alfred
[/TD]
[TD]Alfred3
[/TD]
[TD]3
[/TD]
[TD]Bridge A, Condo A, Condo B
[/TD]
[/TR]
[TR]
[TD]Condo C
[/TD]
[TD]Brady
[/TD]
[TD]Brady3
[/TD]
[TD]3
[/TD]
[TD]Bridge B, Tower C, Condo C
[/TD]
[/TR]
[TR]
[TD]Condo D
[/TD]
[TD]Alfred
[/TD]
[TD]Alfred4
[/TD]
[TD]4
[/TD]
[TD]Bridge A, Condo A, Condo B, Condo D
[/TD]
[/TR]
</tbody>[/TABLE]

The "Project" and "Partner" columns are just a list of names that are hard-keyed.
The "ID" formula in cell C2 is: =B2&D2 . Copy this formula down the column.
The "Manager Count" formula in cell D2 is: =COUNTIF($B$1:B1,B2)+1 . Copy this formula down the column.
The "Assembly" formula in cell E2 is: =IF(D2=1,A2,OFFSET($E$1,MATCH(B2&(D2-1),$C$2:$C$10,0),0)&", "&A2) . Copy this formula down the column.




Then, where you want to list the projects for each partner with the commas separating them, put this table. My example one is in I1:J4 of the same tab:

Table 2:
Code:
[TABLE="width: 432"]
<tbody>[TR]
[TD]Partner
[/TD]
[TD]Projects
[/TD]
[/TR]
[TR]
[TD]Alfred
[/TD]
[TD]Bridge A, Condo A, Condo B, Condo D
[/TD]
[/TR]
[TR]
[TD]Brady
[/TD]
[TD]Bridge B, Tower C, Condo C
[/TD]
[/TR]
[TR]
[TD]Collin
[/TD]
[TD]Tower A, Tower B
[/TD]
[/TR]
</tbody>[/TABLE]

The "Partner" names are hard-keyed.
The "Projects" formula in cell J2 is: =VLOOKUP(I2&COUNTIF($B$1:$B$10,I2),$C$2:$E$10,3,FALSE) . Copy this formula down the column.


The end result as shown in the second table above has the partner's name in one column and all their projects in the next column.

Let me know if you have any questions or if this doesn't work for you.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top