How to concatenate?

  • Thread starter Thread starter Aioe
  • Start date Start date
A

Aioe

Have the situation:

col A col B
1 Peter xx1
2 xx2
3 Rea xx3
4 Don xx1
5 xx2
6 xx3
7 Pat
8 Ron zz1
9 xx4
12 xx5
13 yy1
14 yy2
15 aaa
16 bbb

Can you help / explain which combination
of functions to use to get concatenated,
"xx1 xx2" in the cell C1 for Peter,
"xx3" in the cell C3 for Rea,
"xx1 xx2 xx3" in the cell C4 for Don,
...........................
"zz1 xx4 xx5... bbb" in the cell C8 for Ron.

thank you
 
Put this "monster" formula in C1:

=IF(A1="","",B1)&IF(AND(A2="",COUNTA(A1:A2)=1),"
"&B2,"")&IF(AND(A3="",COUNTA(A1:A3)=1),"
"&B3,"")&IF(AND(A4="",COUNTA(A1:A4)=1),"
"&B4,"")&IF(AND(A5="",COUNTA(A1:A5)=1),"
"&B5,"")&IF(AND(A6="",COUNTA(A1:A6)=1),"
"&B6,"")&IF(AND(A7="",COUNTA(A1:A7)=1),"
"&B7,"")&IF(AND(A8="",COUNTA(A1:A8)=1)," "&B8,"")

and copy down to C16.

It will cope with up to 7 consecutive cells for each name, as in your
example, but it is relatively easy to expand it if you have more.

Hope this helps.

Pete
 
In my newsreader the formula has been split wherever there is a space,
i.e. " "&B2 etc. This version, which I have manually split by
inserting hard returns, shows the formula structure more clearly:

=IF(A1="","",B1)&
IF(AND(A2="",COUNTA(A1:A2)=1)," "&B2,"")&
IF(AND(A3="",COUNTA(A1:A3)=1)," "&B3,"")&
IF(AND(A4="",COUNTA(A1:A4)=1)," "&B4,"")&
IF(AND(A5="",COUNTA(A1:A5)=1)," "&B5,"")&
IF(AND(A6="",COUNTA(A1:A6)=1)," "&B6,"")&
IF(AND(A7="",COUNTA(A1:A7)=1)," "&B7,"")&
IF(AND(A8="",COUNTA(A1:A8)=1)," "&B8,"")

Hope this helps.

Pete
 
Yes, thanks much.
ForYI there're some names with ~ max 30 sub items, maybe more :P
I know some code /do while/ will do the job, but on 36° I'm bLocked ;)
/goin to try...
 
I would use a macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim TopRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim iRow As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 1
TopRow = FirstRow
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = TopRow To LastRow
If Trim(.Cells(iRow, "A").Value) = "" Then
'empty cell in A, so concatenate it in column C
.Cells(TopRow, "C").Value _
= .Cells(TopRow, "C").Value & " " & .Cells(iRow, "B").Value
Else
'new group
TopRow = iRow
'put that value in column B into column C
.Cells(TopRow, "C").Value = .Cells(TopRow, "B").Value
End If
Next iRow

'uncomment this group (maybe!) when you've checked the output
'it deletes the rows where column A is empty

' On Error Resume Next
' .Range("A:A").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
' On Error GoTo 0

End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Thanks a lot.
Anyway, I'm sure going to try the macro as I've already said,
sometimes it happens, there's a bunch of more than 30 "subitems",
but it's an extreme which can be settled by "brutal force" ,)

The intention of the real story is:
/ filter the MainItems
/ see which panels must be open (Col B)
/ sort & filter them unique
/ Vlookup the filtered items versus manhours
/ Total the hours for Open/Close.

and, I suppose you've already noticed that, the whole story is
the consequence of .pdf -> .xls transanction. If I use Acrobat (page by
page), copy as a table option, I got it OK, and through Word LF and CR
replacement, problem solved.
But, sometimes it's more effective to go trought row by row
transaction...


It happens that Dave Peterson formulated :
 
Works great, thanks!

Dave Peterson used his keyboard to write :
I would use a macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim TopRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Click to expand...
.......... cut
 
Back
Top