Solved

Excel formula to combine text using IF statement

Posted on 2014-12-02
7
100 Views
Last Modified: 2015-01-01
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

Thanks
0
Comment
Question by:vpopper
7 Comments
 
LVL 5

Expert Comment

by:sharedit
Comment Utility
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.

http://igoogledrive.blogspot.com/2013/09/concatenate-2-rows-if-they-have-same.html
0
 
LVL 23

Accepted Solution

by:
Michael74 earned 168 total points
Comment Utility
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
        Else
            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
    Next
    
    For i = 2 To lastRow
        Range(DRUG_COL & i).Value = dict.Item(Range(REC_COL & i).Value)
    Next

End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:Let's Go
Comment Utility
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 166 total points
Comment Utility
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:
=IF(COUNTIF($A$2:$A$7,A2)>1,B2&"-"&VLOOKUP(A2,A3:$B$7,2,FALSE),B2)

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).

Regards,
-Glenn
0
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 166 total points
Comment Utility
If there are more than 2 duplicates,

In C2 and copied down:

=IFERROR(LOOKUP(2,1/($A$1:A1=A2),$C$1:C1)&"-"&B2,B2)

In D2 and copied down:

=COUNTIF($A$2:$A$10,A2)=COUNTIF($A$2:A2,A2)

Copy the formulas and paste special as values.

Now filter FALSE in Col D and delete the rows.

Kris
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now