Excel - Conditional Concatenate 1

[Melagan]

Greetings,

I have a sheet of data that looks like this:

BuyerLast[tab][tab][tab]BuyerFirst[tab]CoBuyerFirst
SELF[tab][tab][tab][tab][tab]STEVEN[tab][tab]RAMONA
CAMPISE[tab][tab][tab]JOHN
WHITTENBURG[tab]JAMES[tab][tab]HEIDI
ROGERS[tab][tab][tab]KENNETH[tab][tab]PAMELA
SICKLER[tab][tab][tab]JERRY

I would like to concatenate these three cells but with a small condition; If there is no CoBuyerFirst then the concatenation should look like this: BuyerFirst & " " & BuyerLast

However, if CoBuyerFirst is not null, then the concatenation should look like this:
BuyerFirst & " and " & CoBuyerFirst & " " & BuyerLast

How could I loop through those particular columns in my worksheet and generate this column with the above concatenations? To make things simple for any example code, say "BuyerLast" is column B, "BuyerFirst" is column C, "CoBuyerFirst" is column D, and the concatenation result should be column A.

Thank you in advance,


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Have a look at the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

[tt]
=BuyerFirst&if(isblank(CoBuyerFirst)," ","and "&CoBuyerFirst)&" "&BuyerLast
[/tt]


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Bong]

Why not switch the columns?

_________________
Bob Rashkin

 

[PHV]

Skip, as Melagan posted in the VBA forum, I thought this person didn't want a formula but code.
Melagan, if your issue is not CBA related you may consider to post here:
forum68

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

That would likely be: Classical Basic for Applications?

_________________
Bob Rashkin

 

[SkipVought]

Where is my head?????

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PHV]

Sorry for the typo, but I like the Classical way ;-)

 

[Melagan]

I posted here because I was hoping to run code to do the work instead of use formulae in every concurrent cell. I have been using this formula to date:
=TRIM(C2& " " & IF(ISBLANK(D2)," ","and "&D2)&" "&B2)

..but as new sheets come in, I have to copy that formula to every row in the sheet's used range. I thought that having a VB method to loop through each row in ActiveSheet.UsedRange to do this would be much more effecient.

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

So, again, Have a look at the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Melagan]

This is what I came up with - does anyone know of a better or more elegant way?

Dim i As Integer
Dim strCellValue As String
strCellValue = ""
'I begins at 2 because row 1 = column headers
i = 2
'Adjust range as necessary
For Each cell In Range("a2:a10000")
    strCellValue = Range("B" & i) & Range("C" & i)
    If strCellValue = "" Then
        cell.Value = ""
    Else
        cell.Value = Range("C" & i) & IIf(IsNull(Range("D" & i)), "", " and " & Range("D" & i) & " " & Range("B" & i))
    End If
i = i + 1
Next

I do not know how to iterate through row numbers in a loop, so the Range("B" & i) was the only thing I could think of. It works, at least =)


~Melagan
______
"It's never too late to become what you might have been.

 

[Chance1234]

If you range is changing all the time,

How about finding the last cell first (see FAQ section for methods relating to finding last used cell)

You could then replace the For Next with a Do Loop which would be a lot faster



Chance,

Filmmaker, gentleman and ROMAN!

 

[jakecam]

with the following code, you avoid of having to manually change the range in your code as rows of records decreases or increases.

records = Application.CountA(ActiveSheet.Range("A:A"))
dim i as integer

For i = 2 to records
    
   strCellValue = Range("B" & i) & Range("C" & i)
    If strCellValue = "" Then
        cell.Value = ""
    Else
        cell.Value = Range("C" & i) & IIf(IsNull(Range("D" & i)), "", " and " & Range("D" & i) & " " & Range("B" & i))
    End If

next i

Although with this approach some records at the end might not be evaluated if some records have blank last names.

 

[xlbo]

why loop? Once you have the last row and you know the 1st row, simply enter the formula into ALL the cells at the same time

lRow = cells(65536,2).end(xlup).row
Range("A2:A" & lRow).Formula = "=IF(TRIM(D2)="""",C2& "" "" & B2,C2 & "" and "" & D2 & "" "" & B2)"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chance1234]

cant argue with their xlbo,

Chance,

Filmmaker, gentleman and ROMAN!

 

[Melagan]

The simplest answer is usually the best. Thanks Geoff, have a star on me =)

~Melagan
______
"It's never too late to become what you might have been.