Returning filtered data to a new sheet in a multiline cell

Hi all,

First of all, thank you for all that you do. Searching these forums and Google is a task at times, but so worth it creating new and improved files.

Ok, so here it goes. I work in insurance and have a file of preferences and contact information split out by region (e.g. Chicago, Boston, New York City, etc.). Each region is on a separate tab with the Sales Reps' contact info as attached (confidential information removed for this thread). Each cell with the Sales Reps per Sales office is a single cell with multiple lines. So, each cell has carriage returns for each additional Rep. This is the same for the Rep name, Rep email, and Rep number.

At this time, each contact info is updated individually. I'd like to change this and do so on the master Rep sheet. Issue: Once I do this, I'm looking to run a VBA filter command to filter information based on the master Rep sheet and return the names, email, and #s of Reps in a region/office back into the individual regional cell as a multiline cell. The aim is to have this be dynamic enough where you would only have to change the master sheet and the VBA would update from there (if a button is needed, no worries there).

I'm hoping this is enough info for anyone who is willing to help. Somewhat difficult to reinvent this document while removing all the confidential data. If you have any questions or need more info for insight, please let me know. Your help is greatly appreciated!

You could use formulas on the sheets that link back to the Master Rep sheet e.g.;

Formula:

='Master Rep'!A1 & CHAR(10) & 'Master Rep'!B1 & CHAR(10) & 'Master Rep'!C1

This concatenates cells A1, B1, and C1 from the Master Rep sheet with carriage returns Char(10) in between. When you change the values on the Master Rep sheet, the formula will automatically update.

I'm not volunteering to do this, but yes it could be done in with VBA.

And formulas could be made to list each rep from a region. If you add a new one, it would show up in the list. It wouldn't be too messy if you're willing to have a column where the row number of the rep is calculated so the concatenate formulas can reference it.

In either case, an example workbook would be required to illustrate your specific layout. You description doesn't give layout details and no one wants to make a workbook based on your image.

I guess we can explore the formulas first. Here's a test file I've created. The goal is to be able to use Sales Reps column B to show all Chicago Reps by name/email/phone and transfer that data over to Chicago A3/A4/A5, respectively.

What type of formula would be able to create the for functionality of a VBA loop? So, I would specify that the formula reference Sales Reps column B and find #10 so that only Chicago reps will be shown. Then the formula would go one-by-one and concatenate with carriage returns after each individual entry that's linked with the #10. Unfortunately, based on prior formatting (not my original design), I'm limited to a single cell to display each of the three values (A3/A4/A5).

Please let me know if you'd need any additional info or clarification.

Thank you once again!

Instead, I created a custom User Defined Function (UDF) called ConcatIf. Sort of a VBA and Formula solution.

Put this formula in a cell to concatenate the names based on the region

=CONCATIF(RegionRange, CriteriaRegion, RangeToConcatenate)

Function ConcatIf(rngCriteria As Range, Crit As String, rngConcat As Range) As String
    Dim vCriteria As Variant, vConcat As Variant
    Dim i As Long
    Const cDelimiter As String = vbLf
    vCriteria = Intersect(rngCriteria.Parent.UsedRange, rngCriteria).Value
    vConcat = Intersect(rngConcat.Parent.UsedRange, rngConcat).Value
    For i = 43 To UBound(vCriteria)
        If UCase(vCriteria(i, 1)) = UCase(Crit) Then ConcatIf = ConcatIf & cDelimiter & vConcat(i, 1)
    Next i
    ConcatIf = Mid(ConcatIf, Len(cDelimiter) + 1)
End Function