?
Solved

Excel formula to combine text using IF statement

Posted on 2014-12-02
7
Medium Priority
?
106 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 672 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
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!

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 664 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 664 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 49

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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