Help....!

[EC_08]

Hello...

My problem is ...

I m having 10000 raw data (Like sample Below) ...

Person	Red	Amber	Green	Violet	Pink	Saffron	White	Output
A	Yes		Yes		Yes		Yes	Red/Green/Pink/White
B		Yes				Yes		Amber/Saffron
C				Yes			Yes	Violet/White
D	Yes	Yes						Red/Amber
E	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Red/Amber/Green/Violet/Pink/Saffron/White

<tbody>
</tbody>

in A column person A,B,c....having different colour (Coloumn B to H)

i want output in cell I like if person A having Red & Green Colour Output is Red/Green..
if person B having Amber & Pink & white then Output should be Amber/Pink/White..etc..

Person	Red	Amber	Green	Violet	Pink	Saffron	White	Output
A	Yes		Yes			Yes	Yes	Red/Green/Saffron/White
B		Yes			Yes		Yes	Amber/Pink/White

<colgroup><col span="8"><col></colgroup><tbody>
</tbody>

 

[Supes77]

=IF(B2="Yes",B1&" / ","")&IF(C2="Yes",C1&" / ","")&IF(D2="Yes",D1&" / ","")&IF(E2="Yes",E1&" / ","")&IF(F2="Yes",F1&" / ","")&IF(G2="Yes",G1&" / ","")&IF(H2="Yes",H1&" / ","")

 

[azumi]

Assumed your data table in ranges A1:H6 as your example :

Put in I2 and copied down:

=SUBSTITUTE(TRIM(SUBSTITUTE(IF(B2<>"",$B$1&"/","")&IF(C2<>"",$C$1&"/","")&IF(D2<>"",$D$1&"/","")&IF(E2<>"",$E$1&"/","")&IF(F2<>"",$F$1&"/","")&IF(G2<>"",$G$1&"/","")&IF(H2<>"",$H$1&"/",""),RIGHT(IF(B2<>"",$B$1&"/","")&IF(C2<>"",$C$1&"/","")&IF(D2<>"",$D$1&"/","")&IF(E2<>"",$E$1&"/","")&IF(F2<>"",$F$1&"/","")&IF(G2<>"",$G$1&"/","")&IF(H2<>"",$H$1&"/",""),1)," "))," ","/")

 

[Peter_SSs]

Two options ..

1. Similar suggestion to Supes77. My suggestion eliminates the extra "/" but assumes the data row contains only "Yes" or blanks. See cell J2 below.


2. A user-defined function (UDF). The UDF is more easily expandable and also allows an easy change if looking for a different word in the data row. The UDF assumes the heading range and data range are both single-row ranges but has no error checking to ensure that is true.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below (cell I2) and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetHeadings(rHdrs As Range, rData As Range, Optional sText As String = "Yes") As String
  Dim s As String
  Dim i As Long
  
  For i = 1 To rData.Cells.Count
    If rData.Cells(i).Value = sText Then s = s & "/" & rHdrs(i).Value
  Next i
  GetHeadings = Mid(s, 2)
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Person	Red	Amber	Green	Violet	Pink	Saffron	White	Output	Output
2	A	Yes		Yes		Yes		Yes	Red/Green/Pink/White	Red/Green/Pink/White
3	B		Yes				Yes		Amber/Saffron	Amber/Saffron
4	C				Yes			Yes	Violet/White	Violet/White
5	D	Yes	Yes						Red/Amber	Red/Amber
6	E	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Red/Amber/Green/Violet/Pink/Saffron/White	Red/Amber/Green/Violet/Pink/Saffron/White
Extract Headings

BTW, a more descriptive topic title will likely get you more helpers and faster help.

 

[EC_08]

Error in Macro..."Compile Error" invalid outside procedure.......

I m using Excel 2013.

 

[EC_08]

Assumed your data table in ranges A1:H6 as your example :

Put in I2 and copied down:

=SUBSTITUTE(TRIM(SUBSTITUTE(IF(B2<>"",$B$1&"/","")&IF(C2<>"",$C$1&"/","")&IF(D2<>"",$D$1&"/","")&IF(E2<>"",$E$1&"/","")&IF(F2<>"",$F$1&"/","")&IF(G2<>"",$G$1&"/","")&IF(H2<>"",$H$1&"/",""),RIGHT(IF(B2<>"",$B$1&"/","")&IF(C2<>"",$C$1&"/","")&IF(D2<>"",$D$1&"/","")&IF(E2<>"",$E$1&"/","")&IF(F2<>"",$F$1&"/","")&IF(G2<>"",$G$1&"/","")&IF(H2<>"",$H$1&"/",""),1)," "))," ","/")

Pls explain Logic....

 

[Peter_SSs]

Error in Macro..."Compile Error" invalid outside procedure.......

I m using Excel 2013.

1. What module does the VBE show when it reports the error? What line does it select?

2. Are you using Excel 2013 32-bit or 64-bit?

3. Do you have any other vba code in your workbook?

 

[EC_08]

Sorry my mistake...now working fine..thanks
Thanks all its save my lost of time...

 

[EC_08]

Sorry my mistake...now working fine..thanks
Thanks all its save my lost of time...

Now my Problem is i want only balnk cell as o/p.

erson	Red	Amber	Green	Violet	Pink	Saffron	White	Output
A	Yes		Yes		Yes		Yes	Amber/Violet/Saffron
B		Yes				Yes		Red/Green/Violet/Pink/white

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[EC_08]

Means output display heading of only blank cell