Combining multiple records, with unique value into one row

[baby0bugs]

Hi All,

I have an address report that comes out with the individual addresses (regular, mailing, shipping) as different records. Is there a formula that will allow me to pull them into one row.

Example:

If the student ID matches AND one of the Contact Types = _MailingAddress, then pull the Contact Address into the MailingAddress Cell, otherwise, pull the Reguler Address into the Mailing Address Cell
AND
If the Student ID matches AND one of the Contact Types = COA, then Add COA Pending in the Mailing2 Cell

I have been going round and round with different formulas and none will do exactly what I need. I can't get the same data into the MailingAddress for each matching Student ID.

I need to ensure that matching student IDs have the same information, so I can remove duplicates aftrer I have pulled all the data into the corresponding columns.

Any Ideas? Sample Report is attached for your review.

thanks!

[Bernie Deitrick]

Add a second sheet to your workbook showing how you want the data at the end of the process.... add comments to any changed cell as to 'why' that cell was changed.

[baby0bugs]

Thank you for your assistance!


I have added a new tab to the sample spreadsheet showing how I want the data to look and comments to explain what I'm trying to accomplish.

There are three scenarios:

1. Student has _MailingAddress listed as a Contact Type
2. Student has _MailingAddress Listed as a Contact Type as well as COA listed as a Contact Type
3. Student has COA Listed as a Contact Type
4. Student Does Not have _MailingAddress or COA listed as a Contact Type

[Bernie Deitrick]

Your examples don't match your Sample Data - so I'm a bit confused - but these appear to be your rules:

IF _MailingAddress and COA are both listed as a Contact Type THEN
Copy the Contact Address columns where the Contact Type = _MailingAddress to the Mailing columns
Copy COA contact address to Shipping Address and added "COA Pending" to Shipping2 column

IF _MailingAddress IS listed as a Contact Type but COA is NOT listed as a Contact Type Then
Copy the Contact Address columns where the Contact Type = _MailingAddress to the Mailing columns
Copy the Regular Address to the Shipping Address columns - because this student does not have a Shipping Address as the contact type

IF _MailingAddress in NOT listed as a Contact Type, but COA is listed as a Contact Type THEN
Copy the COA Address to the Mailing & Shipping Address columns
Add "COA Pending" to the end of the data within the Contact Address2

If _MailingAddress is NOT listed and COA is NOT listed Then
Copy Regular Address columns to MailingAddress and Shipping Address columns

Do you want all rows to have the same new, copied values: i.e., if there are four entries, all entries get the new values in the appropriate columns? It will not be formula-driven, but macro (VBA code) - are you okay with enabling and using macros? (some organizations don't allow macro code....)

[baby0bugs]

Hi Bernie,

I'm sorry about the mix up with the examples and Sample Data.

Yes, the rules you have are correct, I will need to add the same rules for ShippingAddress, but I think I can handle that, once the code is written.

I am comfortable with VBA Codes and macros, we have no restrictions with using them.

Thanks so much for your assistance!

[Bernie Deitrick]

Try this initial macro, that handles just the first case, where _MailingAddress and COA are both listed:

Please Login or Register  to view this content.

[baby0bugs]

Hi Bernie,

I put this in my original spreadsheet and tweaked it to correspond with the columns and it worked!

Awesome!!

[Bernie Deitrick]

Are you able to handle the other cases based on my example code or do you need help with that?

[baby0bugs]

I'll give it a shot and let you know if I need help or your eye on my code.

Thanks for getting me started!

[baby0bugs]

I was able to make the necessary adjustments to my original spreadsheet. However, I discovered that it is looking at a change in the RegularAddress. It should be looking at a change in the Student ID (Column A). I have attached my sample spreadsheet and included a tab for the example conditions - if you can work with the first one, as well as the sample output.

I have tried tweaking it, but it isn't working for me.


Are there any ideas to make these conditions work?

Thanks!

[baby0bugs]

Figured it out myself - got it working.