If Statement? Yes or No? What should I use

[khesselbacher]

Hi, I am wondering what would be the best way to go about this.

I have a column of names,another column of subject and in another column either yes or no.


I want data like this........... To become like this

Name	Subject	Action		Ran Walk Hike Swim Kelly Yes Yes No Yes Sharon Yes Yes No Yes <tbody> </tbody>
Kelly	Ran	Yes
Kelly	Walk	Yes
Kelly	Hike	No
Kelly	Swim	Yes
Sharon	Ran	Yes
Sharon	Walk	Yes
Sharon	Hike	No
Sharon	Swim	No
	Ran	Walk	Hike	Swim
Kelly	Yes	Yes	No	Yes
Sharon	Yes	Yes	No	Yes

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

 

[James006]

Hi,

With your source table holding data in range A2:C9 ...

In cell F2 =OFFSET($A$2,(ROW()-2)*4,0)
In cell G1 =OFFSET($A$2,COLUMN()-7,1)
In cell G2 =OFFSET($A$2,COLUMN()-7,2)

Then from cell G2 you can copy your entire range ...

HTH

 

[Ruddles]

Could be a pivot table if you get rid of all the No values. Highlight the table and Insert PivotTable. Row label should be Subject, Column label should be Action, Values should be Count of Value.

Is that something you could tinker with?

 

[khesselbacher]

Thank you for your response, but I don't think that will work has I have serveral names that may not have had any of those activities. This is data I am making up. The main thing is to get the Activity as its own column.
Should I do a vlookup? Or a macro, if Kelly ran, then go look at this column to get the notes?

Kelly ran Yes Ran Swam Hike Bike
Kelly Swam No Kelly Yes No 1/23 Yes
Kelly Hike 1/23 Susan Yes
Kelly Bike Yes Mike Yes 2/3 No
Susan Ran Yes Bob 2/1
Mike Ran Yes
Mike Hike No
Mike Swam 2/3
Bob Hike 2/1

Thank you, I look forward to one of you geniuses out there responding with the best formula or macro.

 

[James006]

Hi,

In cell F2, in order to get your list of Unique individuals ...

=INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$10),0,0),0))

HTH

 

[khesselbacher]

DATA is this way, in no order		I want it to drop comments into the correct column
	Subject	Comments			Pursuit Strategy	Client Demo	Pursuit Support Budget, CO, PoC Demos	Client Intro Meeting
Bob's Chop	Pursuit Strategy	Yes		Bob's Chop	Yes	Yes	1/10 David confirmed services for SFDC Opp	Yes
Bob's Chop	Client Demo	Yes		Mary Sue's		Yes	Yes	Yes
Bob's Chop	Pursuit Support Budget, CO, PoC Demos	1/10 David confirmed services for SFDC Opp		Fritz and Fuba		Yes
Bob's Chop	Client Intro Meeting	Yes		Tricia Pet		Yes 12/7		Yes
Mary Sue's	Client Demo	Yes		Irene Home	Yes	Yes	Yes	Yes
Mary Sue's	Client Intro Meeting	Yes
Mary Sue's	Pursuit Support Budget, CO, PoC Demos	Yes
Mary Sue's	Pursuit Strategy	Yes
Fritz and Fuba	Client Demo	Yes
Tricia Pet	Client Demo	Yes 12/7
Tricia Pet	Client Intro Meeting	Yes
Irene Home	Client Demo	Yes
Irene Home	Pursuit Support Budget, CO, PoC Demos	Yes
Irene Home	Pursuit Strategy	Yes
Irene Home	Client Intro Meeting	Yes

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>

Hi,

In cell F2, in order to get your list of Unique individuals ...

=INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$10),0,0),0))

HTH

 

[James006]

Hi,

It appears that, in each new post, you are slowly unveiling your actual constraints ... :wink:

If you are looking for a text based pivot table with VBA ... see https://www.homeworkhelponline.net/blog/programming/excel-pivot-sub-group

HTH