excel - How to search and extract certain values in cells - VBA

Question

I have the following data, which can be seen by using the link below:

Example of Input and desired Output Data

Using the following logic:

Extract all of JPM (arranger’s) total values from "Sheet1" and plot them in sheet 2, on each row under the column that says JPM.

For example, if B1 in "Sheet2", is equals to BAML, then search BAML in sheet 1 and extract all its total values from column F and insert in "Sheet2", under the BAML column.

Please NOTE: for example BAML can be in a cell by itself or it can be in any other position in a cell in column C.

I require help and assistant in developing a macro which can display the output as shown in the data link above, in "Sheet2". I am not sure, how would I go about solving this problem.

Any help would be very much appreciated.

Kind regards

Input data (Sheet1)

Output data (Sheet2)

Answer 1

If you only need the total of each cotributor, you can do it using following Excel formula. Change Criteria based on the contributor name, e.g. JPM, CITG, BAML... so on.

Formula:

=SUMIF($C$2:$C$13,"*JPM*",$B$2:$C$13)

Output

JPM      CITG    BAML    BCG     CIBC       DB
2432.74  500     500     500     1812.84    1812.84

Will this be sufficient for you? Else do you want to show price for each contributor in a distributted pivot view?

Since the OP's request is to sort prices by row for each contributor, a VBA snippet is coded. For easier visual understanding the solution pivot is displayed next to your source data.

Following is the steps to solution and output.

1. Add a button to your Sheet.
2. Double click the button and add Call splitSortPivot()
3. Add a module to your Excel project
4. Double click on the module and copy the following code
5. Save, compile

Code:

Option Explicit

Public Sub splitSortPivot()
Dim ws As Worksheet
Dim sourceRange As Range
Dim rng As Range
Dim i, j, k As Integer    
Dim sourceArray As Variant
Dim arrangersArray As Variant
Dim ary As Variant

Set ws = Worksheets("Sheet1")
Set sourceRange = ws.Range("B2:C13")

'Based on the number of records +2 you have in source sheet (Sheet1)
'you may set the first dimension's upper bound of the array
'+1 for header and +1 for total --> in current case 12  + 1 + 1 = 14
'even setting this number can be done programmatically using used rows in C column.
ReDim arrangersArray(0 To 13, 5)

'This code can be optimized to add names programmatically
arrangersArray(0, 0) = "JPM"
arrangersArray(0, 1) = "CITG"
arrangersArray(0, 2) = "BAML"
arrangersArray(0, 3) = "BCG"
arrangersArray(0, 4) = "CIBC"
arrangersArray(0, 5) = "DB"

sourceArray = sourceRange.Value

    For j = LBound(sourceArray, 1) To UBound(sourceArray, 1)
        If InStr(1, sourceArray(j, 2), ",") > 0 Then
            ary = Split(sourceArray(j, 2), ",")
            For k = LBound(ary) To UBound(ary)
                For i = LBound(arrangersArray, 2) To UBound(arrangersArray, 2)
                    If arrangersArray(0, i) = Trim(ary(k)) Then 
                       arrangersArray(j, i) = sourceArray(j, 1)
                       arrangersArray(13, i) = arrangersArray(13, i) + arrangersArray(j, i)
                    End If
                Next i
            Next k
        Else
            For k = LBound(arrangersArray, 2) To UBound(arrangersArray, 2)
                If arrangersArray(0, k) = sourceArray(j, 2) Then
                    arrangersArray(j, k) = sourceArray(j, 1)
                    arrangersArray(13, k) = arrangersArray(13, k) + arrangersArray(j, k)
                End If
            Next k
        End If
    Next j

'Output the processed array into the Sheet. 
Range("G1").Resize(UBound(arrangersArray) + 1, _ 
UBound(Application.Transpose(arrangersArray))) = arrangersArray

End Sub

Output screen shot: