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

On Jul 22, 10:35*am, Aioe <felix...@gmail.com> wrote:
> 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

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

On Jul 22, 12:54*pm, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Jul 22, 10:35*am, Aioe <felix...@gmail.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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...

on 22.7.2010, Pete_UK supposed :
> 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
>
> On Jul 22, 10:35*am, Aioe <felix...@gmail.com> wrote:
>> 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

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.)

On 07/22/2010 04:35, Aioe wrote:
> 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
>
>

--
Dave Peterson

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 :
> 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.)
>
> On 07/22/2010 04:35, Aioe wrote:
>> 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

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
.......... cut