Email Threading - Populate Columns with IDs of Parents / Children

lneidorf

Board Regular
Joined
May 20, 2004
Messages
97
Office Version
  1. 365
Platform
  1. Windows
Hi there.

I include a rendering of my data at the end of the post. SCROLL WAY DOWN.

I have a novel problem. I must regularly generate indices of various documents exported from a database. These consist of emails and their attachments, as well as other documents mixed in that are not associated with any emails. I refer to emails and their attachments as "families", with the emails "parents" and the attachments "children".

A description of the fields in use:
BEGDOC - the key field, a unique identifier of each record.
ENDDOC - left blank.
BEGATTACH - used to designate families.
ENDATTACH - left blank.
PGCOUNT- left blank.
PARENTID - to be populated by code.
ATTACHID - to be populated by code.
DOCTYPE - identified whether a document is an email with attachments, email without attachments, or an attachment.


For those documents that are emails and attachments, I need to automate the population of two fields in my report, PARENTID and ATTACHID. These are populated with the BEGDOC value. These fields need to be populated as follows:
-PARENTID is populated where DOCTYPE = "Attachment" with the BEGDOC value of its parent email.
-ATTACHID is populated where DOCTYPE = "eMail/eMail with attachment" with all of the BEGDOC values for its children attachments, separated by a semicolon.

Again, parent / children family relationships are designated by the BEGATTACH field.

Any and all suggestions are welcome!

Thanks!

<table>
<tr>
<td>BEGDOC</td>
<td>ENDDOC</td>
<td>BEGATTACH</td>
<td>ENDATTACH</td>
<td>PGCOUNT</td>
<td>PARENTID</td>
<td>ATTACHID</td>
<td>DOCTYPE</td>
</tr>
<tr>
<td>RKT-GA-915650</td>
<td></td>
<td>RKT-GA-915650</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>eMail/eMail without attachment</td>
</tr>
<tr>
<td>RKT-GA-915670</td>
<td></td>
<td>RKT-GA-915670</td>
<td></td>
<td></td>
<td></td>
<td>RKT-GA-915670; RKT-GA-915671</td>
<td>eMail/eMail with attachment</td>
</tr>
<tr>
<td>RKT-GA-915671</td>
<td></td>
<td>RKT-GA-915670</td>
<td></td>
<td></td>
<td>RKT-GA-915670</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td>RKT-GA-915672</td>
<td></td>
<td>RKT-GA-915672</td>
<td></td>
<td></td>
<td></td>
<td>RKT-GA-915673</td>
<td>eMail/eMail with attachment</td>
</tr>
<tr>
<td>RKT-GA-915673</td>
<td></td>
<td>RKT-GA-915672</td>
<td></td>
<td></td>
<td>RKT-GA-915672</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td>RKT-GA-915676</td>
<td></td>
<td>RKT-GA-915755</td>
<td></td>
<td></td>
<td></td>
<td>RKT-GA-915692; RKT-GA-915755; RKT-GA-915758; RKT-GA-915762</td>
<td>eMail/eMail with attachment</td>
</tr>
<tr>
<td>RKT-GA-915692</td>
<td></td>
<td>RKT-GA-915755</td>
<td></td>
<td></td>
<td>RKT-GA-915676</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td>RKT-GA-915755</td>
<td></td>
<td>RKT-GA-915755</td>
<td></td>
<td></td>
<td>RKT-GA-915676</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td>RKT-GA-915758</td>
<td></td>
<td>RKT-GA-915755</td>
<td></td>
<td></td>
<td>RKT-GA-915676</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td>RKT-GA-915762</td>
<td></td>
<td>RKT-GA-915755</td>
<td></td>
<td></td>
<td>RKT-GA-915676</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td>RKT-GA-915770</td>
<td></td>
<td>RKT-GA-915770</td>
<td></td>
<td></td>
<td></td>
<td>RKT-GA-915789</td>
<td>eMail/eMail with attachment</td>
</tr>
<tr>
<td>RKT-GA-915789</td>
<td></td>
<td>RKT-GA-915770</td>
<td></td>
<td></td>
<td>RKT-GA-915770</td>
<td></td>
<td>Attachment</td>
</tr>
<tr>
<td></td>
</tr>
</table>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Check my logic for the PARENTID column. It returned the results you show above when I dragged it down
Code:
=IF(AND(MATCH(C2,C:C,0), A2<>C2),IF(MATCH(C2,C:C,0),INDEX(A:A,MATCH(C2,C:C,0),1),""),"")
 
Upvote 0
Rod,

Thanks for that. That formula almost work for the PARENTID column, it just needs an IF condition to limit it to only those rows where DOCTYPE = "Attachment".

The more challenging column is the ATTACHID, which needs to be multivalue and not include the BEGDOC of the instant record.

I'm open to using formulas, but a VBA approach is ideal as I'm using code to do a ton of other things with this data set.

Thanks!
 
Upvote 0
Rod,

Thanks for that. That formula almost work for the PARENTID column, it just needs an IF condition to limit it to only those rows where DOCTYPE = "Attachment".

The more challenging column is the ATTACHID, which needs to be multivalue and not include the BEGDOC of the instant record.

I'm open to using formulas, but a VBA approach is ideal as I'm using code to do a ton of other things with this data set.

Thanks!
Ahhh, most people don't want VBA approach. I might take another stab at it tomorrow -- thanks
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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