Concatenation Help

[Guest]

Hello to all
I am working with a CSV file. The rows of data are grouped with a "Header
Row" and a blank row after this header. The number of rows in the group
vary. I am trying to concatenate the contents of one cell with the contents
of the header row. There are numerous groups of data. Im not sure how to
indicate the cell above of the header row. It will always have a blank row
before and after it. The formula would be in ColA

A B
23 Blank Row
24 Group1Header
25 Blank Row
26 Group1HeaderRecord1 Record1
27 Group1HeaderRecord1 Record1
28 Blank Row
29 Group2Header
30 Blank Row
31 Group2HeaderRecord2 Record2
32 Blank Row

Thanks

 

[Bernie Deitrick]

Henry,

You can do this, but it requires two columns of formulas, in columns A and
B: Assuming your first header is in C2 (NB, row 1 MUST be blank), in Cell
A2, enter the formula

=IF(AND(C1="",C3=""),C2,A1)

in Cell B2, enter the formula
=IF(AND(C2<>A2,C2<>""),A2&C2,"")

and copy down to match your data.

Note, too, that this will fail if you have only one record, which will be
picked up as a header.

HTH,
Bernie
MS Excel MVP

 

[Andy Brown]

OK, I probably shouldn't be let loose with code. Add to that you already
have a response from an MVP, so if I add *anything* I'm liable to be laughed
out of town. But what the hey.

Maybe you could try some code? Select the first cell in column A next to a
"GroupWhatever" cell, then run Macro1.

Sub Macro1()
n = Range("B65536").End(xlUp).Row
Do Until ActiveCell & ActiveCell.Offset(0, 1) = ""
If Left(ActiveCell.Offset(0, 1), 5) = "Group" Then
Range("C1") = ActiveCell.Offset(0, 1)
ActiveCell.Offset(2, 0).Select
Macro2
End If
Loop
ActiveCell.Offset(1, 0).Select
End Sub
'
Sub Macro2()
Do Until ActiveCell.Offset(0, 1) = ""
ActiveCell.Value = Range("C1") & ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Select
End Sub

HTH,
Andy

 

[Andy Brown]

Told you I was dangerous

You don't need the Macro1 first line (n = ...) ; it's a carry-over from a
previous effort.

Rgds,
Andy

 

[Guest]

Thanks Bernie!

Henry,

You can do this, but it requires two columns of formulas, in columns A and
B: Assuming your first header is in C2 (NB, row 1 MUST be blank), in Cell
A2, enter the formula

=IF(AND(C1="",C3=""),C2,A1)

in Cell B2, enter the formula
=IF(AND(C2<>A2,C2<>""),A2&C2,"")

and copy down to match your data.

Note, too, that this will fail if you have only one record, which will be
picked up as a header.

HTH,
Bernie
MS Excel MVP