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>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi & welcome to MrExcel.
How about

Book1
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
02
Cell Formulas
RangeFormula
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))),"")
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
.. 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. :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top