Noob Help Please

MRT The Duck

New Member
Joined
Nov 12, 2019
Messages
4
I am hoping someone can help.

It seems like such an easy thing to do and I just can't find the answer anywhere.

So, I would like to search a range of cells F2:F6 (APPLICATION) for the word "Yes"

The word will be returned in multiple cells and I would like to copy the B2:B6 corresponding cell to another location. I have set out as best I can an example below

DateLead NameSourceSub SourceSub Source CompanyApplication
28/01/2019Example 1Self GeneratedExample Source 1Example Sub Source 1Yes
30/10/2019Example 2Self GeneratedExample Source 2Example Sub Source 2No
31/01/2019Example 3Self GeneratedExample Source 3Example Sub Source 3Yes
12/02/2019Example 4Self GeneratedExample Source 4Example Sub Source 4No
13/02/2019Example 5Self GeneratedExample Source 5Example Sub Source 5Yes

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,397
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Lead Name</td><td style=";">Source</td><td style=";">Sub Source</td><td style=";">Sub Source Company</td><td style=";">Application</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">28/01/2019</td><td style=";">Example 1</td><td style=";">Self Generated</td><td style=";">Example Source 1</td><td style=";">Example Sub Source 1</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style=";">Example 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">30/10/2019</td><td style=";">Example 2</td><td style=";">Self Generated</td><td style=";">Example Source 2</td><td style=";">Example Sub Source 2</td><td style=";">No</td><td style="text-align: right;;"></td><td style=";">Example 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">31/01/2019</td><td style=";">Example 3</td><td style=";">Self Generated</td><td style=";">Example Source 3</td><td style=";">Example Sub Source 3</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style=";">Example 5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">12/02/2019</td><td style=";">Example 4</td><td style=";">Self Generated</td><td style=";">Example Source 4</td><td style=";">Example Sub Source 4</td><td style=";">No</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">13/02/2019</td><td style=";">Example 5</td><td style=";">Self Generated</td><td style=";">Example Source 5</td><td style=";">Example Sub Source 5</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">02</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$6,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$B$2:$B$6</font>)-ROW(<font color="Teal">$B$2</font>)+1</font>)/(<font color="Purple">$F$2:$F$6="yes"</font>),ROWS(<font color="Purple">H$2:H2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Another option.
- Put AutoFilter on column F and Filter for 'Yes'
- Manually select all of what you see below the column B heading and Copy/Paste it to wherever you want
- Remove AutoFilter (or Filter for Select All)
 

MRT The Duck

New Member
Joined
Nov 12, 2019
Messages
4
Hi & welcome to MrExcel.
How about
ABCDEFGH
1DateLead NameSourceSub SourceSub Source CompanyApplication
228/01/2019Example 1Self GeneratedExample Source 1Example Sub Source 1YesExample 1
330/10/2019Example 2Self GeneratedExample Source 2Example Sub Source 2NoExample 3
431/01/2019Example 3Self GeneratedExample Source 3Example Sub Source 3YesExample 5
512/02/2019Example 4Self GeneratedExample Source 4Example Sub Source 4No
613/02/2019Example 5Self GeneratedExample Source 5Example Sub Source 5Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
02

Worksheet Formulas
CellFormula
H2=IFERROR(INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($B$2:$B$6)-ROW($B$2)+1)/($F$2:$F$6="yes"),ROWS(H$2:H2))),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Amazing, thank you! I had to play around with it as there are actually 1000 bits of data to look through but works perfectly.
 

MRT The Duck

New Member
Joined
Nov 12, 2019
Messages
4
Welcome to the MrExcel board!

Another option.
- Put AutoFilter on column F and Filter for 'Yes'
- Manually select all of what you see below the column B heading and Copy/Paste it to wherever you want
- Remove AutoFilter (or Filter for Select All)
Thanks Peter, Unfortunately the AutoFilter plays havoc with the sheet for some reason. If i use it I am then unable to enter data in certain cells :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
Thanks Peter, Unfortunately the AutoFilter plays havoc with the sheet for some reason. If i use it I am then unable to enter data in certain cells :)
That doesn't make any sense to me. The suggestion involved applying AutoFilter copying what you wanted and removing Autofilter. I cant see how that could possibly affect what you subsequently want to enter. Never-the-less you seem to have something that you are happy with & that is the main thing.
 

MRT The Duck

New Member
Joined
Nov 12, 2019
Messages
4
That doesn't make any sense to me. The suggestion involved applying AutoFilter copying what you wanted and removing Autofilter. I cant see how that could possibly affect what you subsequently want to enter. Never-the-less you seem to have something that you are happy with & that is the main thing.
I know, I didn't create the form so don't know the reasons behind the auto filter messing around with things.
It was the first thing i tried to be honest as it would have saved a load of hassle however every time I filter and then unfilter I am unable to type anything in the Yes or No box.. Very strange.

Thanks so much for the replies, it is much appreciated
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
.. every time I filter and then unfilter I am unable to type anything in the Yes or No box..
Sounds like there might be some sheet protection and/or vba code going on in your workbook. However, it seems the formula solution suggested by Fluff is meeting your needs so I guess we don't need to delve into the reasons for this particular behaviour any further. :)
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top