wakerider017
Board Regular
- Joined
- Jun 10, 2015
- Messages
- 77
Hey guys, I have two data sets that I want to combine. Data is currently on two different worksheets.
Below is an example of what I am trying to do, but my actual data has more columns for both Opportunity and Contacts. I also have many more rows.
[TABLE="width: 211"]
<colgroup><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 211"]
<colgroup><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Bob[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Tina[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tim[/TD]
[/TR]
</tbody>[/TABLE]
I need to link them by the Account number.
Ideally I'd like a hierarchy, where each Opportunity has its own row and lists all the related contacts underneath. Not sure how to do that? Can this be done in a pivot table
Thinking output would be something like this:
[TABLE="width: 211"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tim[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Bob[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Tina[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tim[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[/TR]
</tbody>[/TABLE]
Below is an example of what I am trying to do, but my actual data has more columns for both Opportunity and Contacts. I also have many more rows.
[TABLE="width: 211"]
<colgroup><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 211"]
<colgroup><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Bob[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Tina[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tim[/TD]
[/TR]
</tbody>[/TABLE]
I need to link them by the Account number.
Ideally I'd like a hierarchy, where each Opportunity has its own row and lists all the related contacts underneath. Not sure how to do that? Can this be done in a pivot table
Thinking output would be something like this:
[TABLE="width: 211"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tim[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Bob[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Tina[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tim[/TD]
[/TR]
[TR]
[TD="align: center"]Opportunity[/TD]
[TD="align: center"]Account [/TD]
[TD="align: center"]Stage[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Closed[/TD]
[/TR]
[TR]
[TD="align: center"]Contact[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[/TR]
</tbody>[/TABLE]