concatenate strings in multiple cells based on criteria, without actually using concatenate

[dfida]

Hi, I would like to generate a string by concatenating cells from a header (first row of cells), based on whether the cells beneath the header meet certain criteria.

For example, in the worksheet below, the number 2 is repeated beneath the headers c, 1, 2, 3. So, the formula I need should generate c123.

Basically, I am trying to generate a compact code that refers to each row, based on its entries. That code will be passed to another worksheet, and will allow me to simply look at the code and understand its meaning, without having to keep looking the original cells. Make sense?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	a	b	c	1	2	3	4	5	e	f	g	code generated
2			2	2	2	2						c123
3		4	4	4	4							bc12
4						3	3	3	3	3		345ef
Sheet1

Excel 2007

 

[hiker95]

dfida,


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	a	b	c	1	2	3	4	5	e	f	g
2			2	2	2	2
3		4	4	4	4
4						3	3	3	3	3
5
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	a	b	c	1	2	3	4	5	e	f	g	code generated
2			2	2	2	2						c123
3		4	4	4	4							bc12
4						3	3	3	3	3		345ef
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GenerateCode()
' hiker95, 03/31/2011
' http://www.mrexcel.com/forum/showthread.php?t=540382
Dim LR As Long, a As Long, LC As Long, aa As Long, H As String
Application.ScreenUpdating = False
LR = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
LC = 0
On Error Resume Next
LC = Application.Match("code generated", Rows(1), 0)
On Error GoTo 0
If LC = 0 Then
  LC = Cells(1, Columns.Count).End(xlToLeft).Column
  Cells(1, LC + 1) = "code generated"
Else
  LC = LC - 1
End If
For a = 2 To LR Step 1
  H = ""
  For aa = 1 To LC Step 1
    If Cells(a, aa) <> "" Then
      H = H & Cells(1, aa)
    End If
  Next aa
  Cells(a, LC + 1) = H
Next a
With Columns(LC + 1)
  .AutoFit
  .HorizontalAlignment = xlCenter
End With
Application.ScreenUpdating = True
End Sub

Save your workbook, Save As, a macro enabled workbook.


Then run the GenerateCode macro.

 

[Krishnakumar]

Hi,

In L1:

abc12345efg

In L2 and copied down,

=MID($L$1,MATCH(TRUE,INDEX(A2:K2<>"",0,0),0),COUNTA(A2:K2))

HTH