Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

Excel formula to combine text using IF statement

I need an EXCEL formula (perhaps if statement) that could combine text when the Rec # is the same.
Below example - 1025325513 is shown twice in this sample list.
I would need to create another column that would combine the text (ALBUMAN-REGADENOSAN)
the non duplicate values in Rec # column would use the same one drug.  I would then delete the duplicate Rec Values and keep the new drug column with combined text.

Rec #               Drug
1025323898      ALTEPLASE
1025323989      ALBUMIN
1025324961      ALTEPLASE
1025325513      ALBUMIN
1025325513      REGADENOSAN
1025326578      ALBUMIN

3 Solutions
Hello,  You are trying to concatenate the text,  so I started looking there.  I found an example of a similar solution for you.  ...not using concatenate.

This may give you some help.

Michael FowlerSolutions ConsultantCommented:
Here is a VBA script that will do this in one step for you.

Note: VBA scripts cannot be undone so take a backup first to be sure

Sub combine()

    Const REC_COL  As String = "A"
    Const DRUG_COL As String = "B"
    Dim dict As Object
    Dim lastRow As Long, i As Long
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = Range(REC_COL & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        If Not dict.Exists(Range(REC_COL & i).Value) Then
            dict.Add Range(REC_COL & i).Value, Range(DRUG_COL & i).Value
            dict.Item(Range(REC_COL & i).Value) = dict.Item(Range(REC_COL & i).Value) & "-" & Range(DRUG_COL & i).Value
            Range(REC_COL & i, DRUG_COL & i).Delete (xlUp)
            i = i - 1
        End If
    For i = 2 To lastRow
        Range(DRUG_COL & i).Value = dict.Item(Range(REC_COL & i).Value)

End Sub

Open in new window

Let's GoCommented:
vpopper, this may also be of interest: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28568870.html

sharedit, I'm not sure if you realise, but the file you linked was for Google Docs, not Excel.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Glenn RayExcel VBA DeveloperCommented:
The VBA solution may indeed be a good idea; it could also be augmented to remove the duplicates found after processing.

However, if you do still want a function, insert this in cell C2 and copy down:

This presumes the data is in columns A & B, begins in row 2, and only contains duplicate pairs (that is, a Rec # only exists once or twice).

If there are more than 2 duplicates,

In C2 and copied down:


In D2 and copied down:


Copy the formulas and paste special as values.

Now filter FALSE in Col D and delete the rows.

Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now