Concatenate text - based on criteria

[ppembers]

Dear Geniuses at Mr Excel,

I have been searching through various forums for some guidance/solution to an issue that I am facing but without quite reaching the desired outcome.

The problem:
I am attempting to concatenating text after cell "EXT" until it reaches text "GIA".

To expand on this, I have:
EXT
-2m wide frontage.
-approximately 190sqm vacant lot.
-no measure of soft landscaping.
GIA
area measured based on 1F
verified by GR
EXT
approximately 12.2sq.m.

etc. etc.

There are no blank cells between rows, the data is continuous with each entry in a new row within a column of circa 4000 entries.

My outcome:
I would like to concatenate text with the following outcome for the above examples:
EXT -2m wide frontage., -approximately 190sqm vacant lot., -no measure of soft landscaping.
GIA area measured based on 1F, verified by GR.
EXT approximately 12.2sq.m.

please note each entry should be separated by ", "

I have a formula which almost does what I want except I can only limit the cells to a range, which results in duplication of entries. I would like the formula to look for key words: "EXT" and "GIA" before concatenating the text that follows.

=IF(OR(ISNUMBER(SEARCH({"EXT","GIA"},$C$2:$C$4000))),CONCATENATE(C2,", ",C3,", ",C4,", ",C5,", ",C6, ", ",C7))

my data is in column C and I would like the result to be in cell G.

Thanks for all your help in advance!

 

[Rick Rothstein]

I do not think you will be able to do what you want with formulas... here is a macro that will do what you asked for (it assumes your data starts in cell C2 and that the output will go to Column D)...

[table="width: 500"]
[tr]
	[td]Sub EXT_GIA()
  Dim X As Long, LastRow As Long, Ar As Range, Result As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Columns("C").Replace "EXT", "=EXT", xlWhole, , True
  Columns("C").Replace "GIA", "=GIA", xlWhole, , True
  ReDim Result(1 To LastRow, 1 To 1)
  Application.ScreenUpdating = False
  For Each Ar In Range("C2:C" & LastRow).SpecialCells(xlConstants).Areas
    X = X + 1
    If Ar.Count = 1 Then
      Result(X, 1) = Mid(Ar(1).Offset(-1).Formula, 2) & " " & Ar.Value
    Else
      Result(X, 1) = Mid(Ar(1).Offset(-1).Formula, 2) & " " & Join(Application.Transpose(Ar), ", ")
    End If
  Next
  Range("D2").Resize(X) = Result
  Columns("C").Replace "=", "", xlPart
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (EXT_GIA) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ppembers]

Rick,

Firstly, I would like to thank you for your very quick response. I am a beginner VBA user and very interested in finding out the meaning of the code, perhaps I shall purchase a book or two...

So far so good though, the code has complete the task, however, it seems to be combining data from the cells outside of the set parameters, i.e. below table is a copy of what I see in excel. Column 1 is where I requested the outcome from the code to be returned:

ID	REF	Notes	Column1
1	101	EXT	otes =EXT, -2m wide frontage., -approximately 190sqm vacant lot., - allowance for handicapped ramp should be provided., -no measure of soft landscaping., =GIA, area measured based on 1F, verified by GR, =EXT, approximately 12.2sq.m.
		'-2m wide frontage.	GIA 2 available GF plan and 2 available 1F plan, 2) Only Front Elevation is available., =EXT, 1.) Approximately 12.01 sq.m, 2.) Approximately 35.02 sq. m
		'-approximately 190sqm vacant lot.
		'- allowance for handicapped ramp should be provided.
		'-no measure of soft landscaping.
2	102	'GIA
		'area measured based on 1F
		'verified by GR
		'EXT
		'approximately 12.2sq.m.
3	103	GIA
		2 available GF plan and 2 available 1F plan
		2) Only Front Elevation is available.
		'EXT
		1.) Approximately 12.01 sq.m
		2.) Approximately 35.02 sq. m

<tbody>
</tbody>

Here is the outcome I am after:

ID	REF	Notes	Column1
1	101	EXT	EXT, -2m wide frontage., -approximately 190sqm vacant lot., - allowance for handicapped ramp should be provided., -no measure of soft landscaping.
		'-2m wide frontage.
		'-approximately 190sqm vacant lot.
		'- allowance for handicapped ramp should be provided.
		'-no measure of soft landscaping.
2	102	'GIA	GIA, area measured based on 1F, verified by GR
		'area measured based on 1F
		'verified by GR
		'EXT	EXT, approximately 12.2sq.m.
		'approximately 12.2sq.m.
3	103	GIA	GIA 2 available GF plan and 2 available 1F plan, 2) Only Front Elevation is available.
		2 available GF plan and 2 available 1F plan
		2) Only Front Elevation is available.
		'EXT	EXT, 1.) Approximately 12.01 sq.m, 2.) Approximately 35.02 sq. m
		1.) Approximately 12.01 sq.m
		2.) Approximately 35.02 sq. m

<tbody>
</tbody>

Thank you once again for your assistance with this.

P

 

[Rick Rothstein]

Here is the outcome I am after:

ID	REF	Notes	Column1
1	101	EXT	EXT, -2m wide frontage., -approximately 190sqm vacant lot., - allowance for handicapped ramp should be provided., -no measure of soft landscaping.
		'-2m wide frontage.
		'-approximately 190sqm vacant lot.
		'- allowance for handicapped ramp should be provided.
		'-no measure of soft landscaping.
2	102	'GIA	GIA, area measured based on 1F, verified by GR
		'area measured based on 1F
		'verified by GR
		'EXT	EXT, approximately 12.2sq.m.
		'approximately 12.2sq.m.
3	103	GIA	GIA 2 available GF plan and 2 available 1F plan, 2) Only Front Elevation is available.
		2 available GF plan and 2 available 1F plan
		2) Only Front Elevation is available.
		'EXT	EXT, 1.) Approximately 12.01 sq.m, 2.) Approximately 35.02 sq. m
		1.) Approximately 12.01 sq.m
		2.) Approximately 35.02 sq. m

<tbody>
</tbody>

That is not how you showed you wanted the output in Message #1 (there you showed the output one row directly under the other). Here is my code revised to keep the concatenations next to their EXT and GIA breakpoints. Note that I still put the output in Column D because I am not sure what you meant by Column1 because Column 1 already contains data (your ID's). If you need the output to go somewhere else, please clarify what should be done with the existing data.

[table="width: 500"]
[tr]
	[td]Sub EXT_GIA()
  Dim Ar As Range
  Columns("C").Replace "EXT", "=EXT", xlWhole, , True
  Columns("C").Replace "GIA", "=GIA", xlWhole, , True
  Application.ScreenUpdating = False
  For Each Ar In Range("C2", Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlConstants).Areas
    If Ar.Count = 1 Then
      Ar(1).Offset(-1, 1) = Mid(Ar(1).Offset(-1).Formula, 2) & " " & Ar.Value
    Else
      Ar(1).Offset(-1, 1) = Mid(Ar(1).Offset(-1).Formula, 2) & " " & Join(Application.Transpose(Ar), ", ")
    End If
  Next
  Columns("C").Replace "=", "", xlPart
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[ppembers]

Thanks Rick

Column D is exactly where I would like to place the output.

There appears to be a slight issue with the data the formula brings back. When I apply the formula to the data, the result appears to loose the first letter of the first word for first entry only. The text that follows is perfect: "

XT - 2m wide frontage was included, - approximately 190 sq. m. vacant lot at the rear of property was included, - allowance for handicapped ramp should be provided., - no measurement for soft landscaping considered. "

<tbody>
</tbody>

The second entry I get the following:
"GIA - Area was measured on 1F, verified by GR., EXT, approximately 12.2sq.m."

and the same applies to the rest of the data- it combines the GIA and EXT from two consecutive records.

May I please ask you to take another look at it please?

P