Simplifying a formula Excel

[Excelhelpkar]

Help Please!!
It is looking at columns o-bn and is looking at the criteria below. IF the BW column contains an (in this case) E bring information in T3 column. How can I simplify this formula?

IF($O3=$BW$2,$P3,"")&IF($S3=$BW$2,$T3,"")&IF($W3=$BW$2,$X3,"")&IF($Z3=$BW$2,$AA3,"")&IF($AC3=$BW$2,$AD3,"")&IF($AF3=$BW$2,$AG3,"")&IF($AI3=$BW$2,$AJ3,"")&IF($AL3=$BW$2,$AM3,"")&IF($AO3=$BW$2,$AP3,"")&IF($AR3=$BW$2,$AS3,"")&IF($AU3=$BW$2,$AV3,"")&IF($AX3=$BW$2,$AY3,"")&IF($BA3=$BW$2,$BB3,"")&IF($BD3=$BW$2,$BE3,"")&IF($BG3=$BW$2,$BH3,"")
BW- Column
[TABLE="width: 92"]
<!--StartFragment--> <colgroup><col width="92" style="width:92pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 92"]Criteria[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]E[/TD]
[/TR]
[TR]
[TD]L[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[Excelhelpkar]

Sorry I meant from column o-bh

Help Please!!
It is looking at columns o-bn and is looking at the criteria below. IF the BW column contains an (in this case) E bring information in T3 column. How can I simplify this formula?

IF($O3=$BW$2,$P3,"")&IF($S3=$BW$2,$T3,"")&IF($W3=$BW$2,$X3,"")&IF($Z3=$BW$2,$AA3,"")&IF($AC3=$BW$2,$AD3,"")&IF($AF3=$BW$2,$AG3,"")&IF($AI3=$BW$2,$AJ3,"")&IF($AL3=$BW$2,$AM3,"")&IF($AO3=$BW$2,$AP3,"")&IF($AR3=$BW$2,$AS3,"")&IF($AU3=$BW$2,$AV3,"")&IF($AX3=$BW$2,$AY3,"")&IF($BA3=$BW$2,$BB3,"")&IF($BD3=$BW$2,$BE3,"")&IF($BG3=$BW$2,$BH3,"")
BW- Column
[TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl64, width: 92"]Criteria[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]E[/TD]
[/TR]
[TR]
[TD]L[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Does BW2 occur just once or multiple times in O3:BG3?

 

[KINGSOFTUSER]

Could you explain a little bit what you're trying to accomplish with this large formula please? Also a screenshot of the formula which displays the cells involved would be very helpful.

 

[Excelhelpkar]

In columns o-bh the letters C, E, L, O, P, S appear multiple times in different order. My formula organizes the data in the order of the Criteria below. Column O = Category and P = Organization.


The formula means if cell O3 has the letter C give me the organizations name in column P3 and add a space. Then it goes to column S3 and looks for (letter or Category) C specifically and brings me the organization in T3 and so on.
IF($O3=$BW$2,$P3,"")&IF($S3=$BW$2,$T3,"")
O P
Category Organization
S (Name of Company)
C (Name of Company)
L (Name of Company)
E (Name of Company)
P (Name of Company)
S (Name of Company)


I have another formula to extract specifically E, L, O, P, S that is indicated in column BW and its specific Row number
BW
Criteria
Row Column
2 C
3 E
4 L
5 O
6 P
7 S

I am not sure if I explained it clearly

 

[Aladin Akyurek]

Add the following function code in VBA to your workbook, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now invoke:
Control+shift+enter, not just enter:

=aconcat(IF(MOD(ROW($O3:$BG3)-ROW($O3),4)=0,
  IF($O3:$BG3=$BW$2,IF($P3:$BH3="","",$P3:$BH3),""),""))