Solved

Excel formula to combine text using IF statement

Posted on 2014-12-02
7
102 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
ID: 40477195
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
ID: 40477457
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
ID: 40477903
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 166 total points
ID: 40478699
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
ID: 40480252
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 46

Expert Comment

by:Martin Liss
ID: 40526452
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Data in Rows to be converted into single row 9 40
Excel calculate based on 'x' in column 2 22
Excel Question 17 15
Return Column Number based on a specific value 25 21
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

776 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