Concatenating various cells

[Angela]

Let me set this up.

A1=smpt:
B2:B300= a list of user names
[email protected]
C3:C300= blank column for combined cells
D3300=a column of cells that already contain information that I would
really LIKE to have the new combined cells appended to.. but I may be
reaching.

=CONCATENATE(A1,B2,A2) will give me smpt:[email protected] in c2 with
no problem

How can I get the function to accept a range of cells? and populate them
from c2:c300?

If anyone can offer some assistance on this It would be greatly appreciated.

Angela

 

[JulieD]

Hi Angela

is this what you're after?

=CONCATENATE($A$1,B2,$A$2)

and drag down to C300.

Cheers
JulieD

 

[Angela]

Julie You are a lifesaver..

That did the trick.. for populating the cells c2:c300

Thanks for the insight!

If you have any idea how I could append those newly combined cells to a cell
that has information already in it, that would be superiffic.

Angela!

 

[Frank Kabel]

Hi
no need for CONCATENATE. Try:
=$A$1&B2&$A$2

 

[JulieD]

Hi Angela

two options
1) use a bit of vba to loop through each cell in column D & append the value
of C

Sub addstuff()

Dim rng As Range
Set rng = Range("D2100")

For Each cell In rng
cell.Value = cell.Value & cell.Offset(0, -1)
Next

End Sub

-- to use, right mouse click on a sheet tab, choose view code, on the left
hand side, click on the line with your workbook name in bold on it (if you
can't see this, choose view/ project explorer from the menu), choose Insert
/ Module ... copy & paste the above code into the right hand side of the
screen & choose run /run from with your mouse clicked somewhere between the
sub & end sub lines.

2) in column E (starting at E2) type
=D2 & C2
copy down to row 300
then click on column E (click on the letter E)
choose edit / copy
then click on column D (click on the letter D)
edit / paste special - values

this will change paste the changed values over column D

NOTE: before trying either of these two suggestions please ensure that you
have a backup of your workbook.

Cheers
JulieD

 

[Angela]

OMG this worked like a CHARM. I used the vba loop.

you are on a roll.
Now:
When I append, can I insert a % before the appended data?

Would that be possible also?

Eagerly awaiting your response!
Angela

 

[JulieD]

Hi Angela

sure just change the line in the code to read

cell.Value = cell.Value & "%" & cell.Offset(0, -1)

any text can be appended or concatenated as long as its in "" and is linked
to the other text or variables using the &

Hope this helps
Cheers
julieD

 

[Angela]

This worked perfectly.
Thanks for your help.

Angela

 

[JulieD]

glad to assist - thanks for the feedback.

This worked perfectly.
Thanks for your help.

Angela
that

 

[creid]

Sorry to piggyback on this thread but I have a similar (but no
identical) problem concerning the concatenate function and JulieD seem
to have worked wonders so I thought I'd humbly ask for a little help.
I am working on a spreadsheet that summarizes tenant information for
skyscraper. I have several columns worth of info about the tenants
among which is the floor number (column E). Using some conditiona
formatting and a very messy function I have set up a sort of timelin
graph to the right that essentially shows the year when the leases o
various tenants expire. In the cell for the expiration year th
aforementioned Messy Function returns a value for the amount of spac
that becomes available when that lease expires. What I would like t
do is to generate, for each year (at the bottom of that year’s column)
a list of the floors that have a lease expiring in that year. I don’
care whether all of the floors get printed (comma separated fo
readability) in one big cell, or if the get listed one-per-cell beneat
the year in question.
The tricky part is that the empty cells on that “graph” I mentione
(ie, all the cells where a lease isn’t expiring, which is most of them
are not actually empty. For other reasons (conditional formatting t
make the “graph” work) the Messy Function fills those empty spaces wit
either “AAA” or “BBB”. This makes it hard to just use the CONCATENAT
function and a cell range, because it would pick up all those AAAs an
BBBs and throw them in as well. So I what I am really after is a sor
of conditional concatenate function, like SUMIF, but for Concatenate.
CATIF, if you will. This seems to me like a job for a VBA module, bu
unfortunately I don’t know any VBA.
If you have any idea how I might solve this (even if the solution i
nothing like what I have suggested) I would be exceedingly grateful.
If my description of the situation is a little too unclear (which
fear it might be, owing to the complexity of the sheet) then I coul
email you the file in question. Just let me know. Thanks for you
time.

Hopeful,
Chris Rei

Attachment filename: tenant.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66691

 

[JulieD]

Hi Craig

no need to apologise - assuming that your workbook is structured

A B C D E F
G H
floor
2004 2005 2006

and your data goes from row 2 to row 100
there's probably neater code to do it but the following seems to work to
populate row 101 of columns F, G & H

Sub concatstuff()
Dim rng As Range
Dim res As String
Dim i As Long
Dim j As Long

j = 1 'to nominate column E as the field to concatenate
For i = 70 To 72 'columns F, G & H - modify to suit (A = 69 so just work
it out from there)
Set rng = Range("" & Chr(i) & "2:" & Chr(i) & "100") 'modify to
suit (2 & 100 are the row numbers, adjust as necessary)
For Each cell In rng
If cell.Value <> "AAA" And cell.Value <> "BBB" Then
If Len(res) < 1 Then
res = cell.Offset(0, -j).Value
Else
res = res & ", " & cell.Offset(0, -j).Value
End If
End If
Next
j = j + 1
Range("" & Chr(i) & "101").Value = res '101 is the row number that
the total will be populated into, adjust to suit
Next
End Sub


To use the code, create a copy of your workbook, and then open your workbook
up, right mouse click on a sheet tab and choose view code.

in the VBE window, you should see the workbook name in bold on the left (if
you can't choose, view/ project explorer from the menu) .. .click on the
workbook name and choose insert / module, then copy & paste the above code
into the module - if any go red it's probably because of a line break, just
delete the line return (not that you can see it) from the first half of the
red & see if that solves your problem... press Alt & F11 to return to your
workbook, display the sheet with the "chart" on it and then choose tools /
macro / macros from the menu, click on the one called concatstuff and press
the RUN button.

let me know how you go.

Cheers
JulieD

 

[JulieD]

Oh craig

i forgot to mention that you'll need to run the code every time you update
the workbook .. this can be made a bit more automatic if you'ld like.

Cheers
JulieD

 

[creid]

Julie,

Thanks for your help on this. So far I haven't been able to ge
the macro to work quite right, but I think it has to do with my no
customizing the variables correctly. I have attached a version of th
file that better shows exactly how my columns and rows are laid out.
The column containing the floor numbers is column E and the floor
start at row 5. Heading right from there on the same sheet (everythin
is on one sheet) the first year column (2004) in the "graph" area start
in column L. The years I want to scan go all the way to column Y.
want the concatenated data for each year to appear in cells in row 9
from columns L to Y (the full range of years in the Graph).
I attempted to change the variables in your code to fit those cel
ranges, but I was unsucessful. I was a little confused as to th
numbering system. You said "A=69" as far as columns go, which is how
figured out the numbers that I put in, but I didn't see any place t
specify the number for the Floor column. The attached file shows wha
happened when I tried to run my edited version of your code. Here i
the code that I used:


Sub ConcatStuff()
Dim rng As Range
Dim res As String
Dim i As Long
Dim j As Long

j = 1 'to nominate column E as the field to concatenate
For i = 80 To 93 'columns F, G & H - modify to suit (A = 69 so jus
work it out from there)
Set rng = Range("" & Chr(i) & "5:" & Chr(i) & "96") 'modify to suit (
& 100 are the row numbers, adjust as necessary)
For Each cell In rng
If cell.Value <> "AAA" And cell.Value <> "BBB" Then
If Len(res) < 1 Then
res = cell.Offset(0, -j).Value
Else
res = res & ", " & cell.Offset(0, -j).Value
End If
End If
Next
j = j + 1
Range("" & Chr(i) & "99").Value = res '101 is the row number that th
total will be populated into, adjust to suit
Next
End Sub


I'm sure this is related to my having little to no understanding o
VBA. Although I've seen enough of basic programming that I recogniz
things like For loops, so if you feel like explaining a little bi
about what each part of the code does I might be able to pick up on i
a little. Thanks again!


- Chris(not craig, although thats not a bad name either

Attachment filename: fixed tenant list.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66841

 

[JulieD]

Hi Chris

just having a quick look at your post as i'm on my way out to work ...
column E (floor number) is indicated by the use of J

j = 1 'to nominate column E as the field to concatenate

using the offset function we reference the column we're looking at and -j to
get to column E ... i worked on the theory that F was your first "year"
column, therefore E is -1 column from F .... if L is your first column then
set j to 7 in the line

j = 7 'to nominate column E as the field to concatenate

Let me know how it goes, and i'll have another look tonight and provide a
better explaination of the code.

Cheers
Julie

 

[creid]

Hey Julie,

I've made some progress but I'm not quite there yet. By playin
with the variables I was able to get it to look at the correct cells
print to the correct cells, and concatenate from the correct cells.
There seems to be one glitch in the results however. For the firs
column of the graph, it correctly identifies the floors that hav
numbers (ie square footages and not "AAA" or "BBB"). However, at th
bottom of the next year in the graph, it repeats the floors from th
first column AND adds to them the second column. Thus as we progres
across the columns of the graph we pick up a growing list of all th
floors that had space available in ANY previous year, as opposed t
just listing the floors the have space available in the year i
question. Perhaps there is something in the way the function loops
I'm not sure. It seems as if such a problem might be cause by the cod
treating "AAA" differently from "BBB" since I use BBBto represent th
time before a lease expires, and AAA for the time after a lease ha
expired or for places where there is no data at all. Here is the cod
I am using as it stands now:

Sub concatstuff()
Dim rng As Range
Dim res As String
Dim i As Long
Dim j As Long

j = 7 'to nominate column E as the field to concatenate
For i = 76 To 89 'columns F, G & H - modify to suit (A = 69 so jus
work it out from there)
Set rng = Range("" & Chr(i) & "5:" & Chr(i) & "96") 'modify to suit (
& 100 are the row numbers, adjust as necessary)
For Each cell In rng
If cell.Value <> "AAA" And cell.Value <> "BBB" Then
If Len(res) < 1 Then
res = cell.Offset(0, -j).Value
Else
res = res & ", " & cell.Offset(0, -j).Value
End If
End If
Next
j = j + 1
Range("" & Chr(i) & "99").Value = res '101 is the row number that th
total will be populated into, adjust to suit
Next
End Sub


Let me know your thoughts. Thanks again.

Chris

PS) I am going away for the weekend, so although I will be able t
check the forum, I may not be able to experiment with my excel documen
til tuesday

 

[JulieD]

Hi Chris

sorry, forgot to reset "res" after each floor ... add to the code after

Range("" & Chr(i) & "99").Value = res '101 is the row number that the total
will be populated into, adjust to suit
res=""


Cheers
JulieD

 

[creid]

Hi Julie,

I'm back from my Labor Day trip to New Hampshire. Just tried you
fix and it works perfectly now. Thank you so much! One final questio
though. You mentioned that you could automate this macro to run ever
time I updated the info (or maybe whenever I opened the document
whatever makes more sense). What code would I need to do that?
Also, do you have any suggestions as to where I would go (a websit
ideally, but a book would be good too) to get a good intro to VBA (wit
a mind towards using it for excel mostly)? It seems to me that writin
custom excel functions shouldn't be too hard so long as I can learn t
basic syntax etc of the language, so that I can put my ideas into code
Thanks again.

Chri

 

[JulieD]

Hi Chris

sorry for the delay in replying ... to run the code when the workbook opens
in the VBE window, double click on ThisWorkbook, on the left hand side of
the screen and then on the right choose Workbook from the left hand side
drop down at the top of the screen and Open should come up on the right (if
not choose open from the drop down)
now copy and paste the code you have into here

close the VBE window, close & save the workbook & give it a go

let us know how you go.

Cheers
JulieD

 

[creid]

Julie-

Works perfectly! I can't even begin to thank you for all you
help. Its great that there are people like you willing to spend s
much time helping random people. Just as a final question, do you kno
of anywhere I might go to learn a little on my own about VBA? I think
might like to write some of my own functions and macros in the future.
If you know of any good beginner tutorials let me know. Thanks agai
for everything.

grateful,
Chris Rei

 

[JulieD]

Hi Chris

thanks for the feedback and i'm glad it works ...

as a trainer i would recommend starting out by doing a 2 days hands-on
course, however make sure it's about using VBA with Excel (and not VB) - i
run a really fun course next time you're in Perth, Western Australia

.... and then have a look at John Walkenbach's book "Excel 200x Power
Programming with VBA" (where x is the version you're using) ... this is the
one that i use (as well as the newsgroup) ... i've also heard "Excel 2000
VBA Programmers Reference"; John Green, Stephen Bullen, Felipe Martins,
Brian Johnson recommended (don't know if they have a later version, but
there's not that many differences anyway).

Keep in touch ... you have my email address.

Cheers
JulieD