Write an If formula

[deekay]

Hello,
I need to write an If formula for the following:

Column A has codes for various brokers (rep code)
Column B has Broker names (rep name)

One broker can have multiple rep codes

In column C, I need to write a formula that checks what the rep name is, and return the rep code accordingly.

Say if John Waldow has 3 rep codes, the cell in column C next to John Waldow should combine all the three rep codes (AAA/BBB/CCC).

So, I would have to check if A1 is equal to any entry in column A, then combine the codes for that entry from column B (and so on for the remaining cells).

Can someone help?

Thank You

 

[Faseeh]

Hi deekay,

if i have understood your problem correctly, you can combine all the conditions with "&" and check for duplicates! Or its better to upload some sample data...

 

[NARAYANK991]

Hi ,

Concatenation of text is something that Excel is not very strong in ; VBA would make it very easy. So would using one or more helper columns.

Which of these is acceptable to you ?

Narayan

 

[deekay]

Hello Narayan,
Im fine with using anything. As long as it works.
Thanks.

 

[json]

Hello Narayan,
Im fine with using anything. As long as it works.
Thanks.

i would highly suggest not using something that doesnt work!! hahaha


from the sound of it, do you have your data configured like this:
ColA----ColB
RepA---John
RepC---John
RepF---John
RepB---Mike
...etc?

if so, you may be able to just recate a PivotTable to list all Rep Names and their respective Rep Codes. Are you familiar with PivotTables?

 

[GFC]

You may want to have a look at
http://chandoo.org/wp/2010/11/10/vlookup-second-value/

I think a pivot table could do it...

 

[bobhc]

Good day deekay

If you turn your data into a table you will be able to filter on a persons name and get all codes allocated to that person.
Please look at the upload

 

[NARAYANK991]

Hi ,

If you do not have too many rep codes associated with one rep name , you can check this file.

Narayan

 

[crouchsw]

I used a custom function:

Function grabcodes(RepName As String, data As Range)
Dim rowcount As Integer
Dim toprow As Integer
Dim leftcol As Integer
Dim topleftcell
Dim repcodestg As String

rowcount = ActiveSheet.Range(data.Address).Rows.Count
toprow = Range(data.Address).Row
leftcol = Range(data.Address).Column
topleftcell = ActiveSheet.Cells(toprow, leftcol).Address
ReDim reparr(1 To rowcount, 1 To 2) As String


For i = 1 To rowcount
    reparr(i, 1) = ActiveSheet.Range(topleftcell).Offset(i - 1, 1)
    reparr(i, 2) = ActiveSheet.Range(topleftcell).Offset(i - 1, 0)
Next

For i = 1 To rowcount
    If reparr(i, 1) = RepName Then
        If repcodestg = "" Then
            repcodestg = reparr(i, 2)
        Else
            repcodestg = repcodestg & "/" & reparr(i, 2)
        End If
    End If
Next
grabcodes = repcodestg
End Function

 

[deekay]

Thank you everyone for helping.

Crouchsw: Worked like a charm. Thank you.

 

[Debraj]

In case of VBA, you can use below code.. its is in my Personal Macro and used a lot..

Function SearchAndConcat(SearchString As String, _
  SearchCol As Range, DisplayCol As Range, Optional Sep As String = ";")
Application.Volatile
  For i = 1 To SearchCol.Rows.Count
  If SearchCol.Cells(i, 1) = SearchString Then
  SearchAndConcat = SearchAndConcat & Sep & DisplayCol.Cells(i, 1).Value
  End If
  Next i
SearchAndConcat = Mid(SearchAndConcat, Len(Sep) + 1)
End Function

You can use this function like..

=SearchAndConcat(B2,$B$2:$B$7,$A$2:$A$7,"---")
where B2 is a searchstring in Column $B$2:$B$7, and will concat values from $A$2:$A$7 Col, with your required Separator..