Solved

Excel formula to combine text using IF statement

Posted on 2014-12-02
7
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:
Michael Fowler 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 47

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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 article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

705 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