Results 1 to 3 of 3

Concatenate Multiple Rows And Switch Columns If Blank

Threaded View

  1. #1
    Registered User
    Join Date
    05-28-2009
    Location
    Oxford
    MS-Off Ver
    Excel 2007
    Posts
    86

    Concatenate Multiple Rows And Switch Columns If Blank

    I am using the following VBA to concatenate multiple results of Index Match formulas:


    Function Lookup_concat(Search_string As String, _
      Search_in_col As Range, Return_val_col As Range)
    
    Dim i As Long
    Dim result As String
    
    For i = 1 To Search_in_col.Count
      If Search_in_col.Cells(i, 1) = Search_string Then
        result = result & ", " & Return_val_col.Cells(i, 1).Value
      End If
    Next
    
    Lookup_concat = Mid(result, 3)
    
    End Function

    With the following formula:
    =Lookup_concat(E2,Sheet2!$D$2:$D$2000,Sheet2!$N$2:$N$2000)

    This is working well, however sometimes the N column in sheet2 contains a blank cell. In this scenario I want to return the value in the M column.


    Please see attached xlsm.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Concatenate multiple rows and columns into 1 cell
    By mj44 in forum Excel General
    Replies: 19
    Last Post: 08-29-2013, 02:37 PM
  2. Replies: 14
    Last Post: 08-01-2013, 08:48 AM
  3. Clock In/Out - Macro to Log Time by a Switch on multiple columns (multiple rows)
    By jasw529 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-30-2013, 06:45 AM
  4. How do I switch Rows to Columns?
    By rdubya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2007, 07:23 PM
  5. [SOLVED] Switch Rows and Columns
    By Bonnie in forum Excel General
    Replies: 3
    Last Post: 03-03-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1