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
[TABLE="width: 320"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Subject[/TD]
[TD] Action[/TD]
[TD] [/TD]
[TD] [TABLE="width: 320"]
<tbody>[TR]
[TD][/TD]
[TD]Ran[/TD]
[TD]Walk[/TD]
[TD]Hike[/TD]
[TD]Swim[/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Sharon[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Ran [/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Walk [/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Hike[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Swim[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharon [/TD]
[TD]Ran[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharon[/TD]
[TD]Walk [/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharon[/TD]
[TD]Hike[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharon[/TD]
[TD]Swim[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ran[/TD]
[TD]Walk[/TD]
[TD]Hike[/TD]
[TD]Swim[/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Sharon[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
</tbody><colgroup><col span="5"></colgroup>[/TABLE]

 

[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]

[TABLE="width: 1300"]
<tbody>[TR]
[TD="colspan: 2"]DATA is this way, in no order[/TD]
[TD][/TD]
[TD="colspan: 4"]I want it to drop comments into the correct column[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Subject[/TD]
[TD]Comments[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pursuit Strategy[/TD]
[TD]Client Demo[/TD]
[TD]Pursuit Support Budget, CO, PoC Demos[/TD]
[TD]Client Intro Meeting[/TD]
[/TR]
[TR]
[TD]Bob's Chop[/TD]
[TD]Pursuit Strategy[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Bob's Chop[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]1/10 David confirmed services for SFDC Opp[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Bob's Chop[/TD]
[TD]Client Demo[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Mary Sue's[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Bob's Chop[/TD]
[TD]Pursuit Support Budget, CO, PoC Demos[/TD]
[TD]1/10 David confirmed services for SFDC Opp[/TD]
[TD][/TD]
[TD]Fritz and Fuba[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob's Chop[/TD]
[TD]Client Intro Meeting[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Tricia Pet[/TD]
[TD][/TD]
[TD]Yes 12/7[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Mary Sue's[/TD]
[TD]Client Demo[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Irene Home[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Mary Sue's[/TD]
[TD]Client Intro Meeting[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary Sue's[/TD]
[TD]Pursuit Support Budget, CO, PoC Demos[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary Sue's[/TD]
[TD]Pursuit Strategy[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fritz and Fuba[/TD]
[TD]Client Demo[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tricia Pet[/TD]
[TD]Client Demo[/TD]
[TD]Yes 12/7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tricia Pet[/TD]
[TD]Client Intro Meeting[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Irene Home[/TD]
[TD]Client Demo[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Irene Home[/TD]
[TD]Pursuit Support Budget, CO, PoC Demos[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Irene Home[/TD]
[TD]Pursuit Strategy[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Irene Home[/TD]
[TD]Client Intro Meeting[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

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