Excel function

[excellrookie]

Is there a function in Excel that scans a specific column for a key phrase, once the function finds the phrase it will display the entire row of information.

For example:
the function would search the entire column B and find any cells that have the value "EXCEL FORUM," lets say it found that phrase in row 34, then it copies the entire row 34 and paste it where the formula is.

 

[mikerickson]

AutoFilter can show all the rows that contain "Excel Forum", which can then be copy pasted elsewhere if desired.

AdvancedFilter can do the whole finding and copying in one go.

A VLOOKUP formula can return the values of the first row that has "Excel Forum" in B:B, but only that one row.

Could you post a small sample of your data lay-out and your desired output?

 

[excellrookie]

AutoFilter can show all the rows that contain "Excel Forum", which can then be copy pasted elsewhere if desired.

AdvancedFilter can do the whole finding and copying in one go.

A VLOOKUP formula can return the values of the first row that has "Excel Forum" in B:B, but only that one row.

Could you post a small sample of your data lay-out and your desired output?

Hey Mike, thanks for the quick reply.

Filter would work but I need the row of information displayed on a different sheet.

The spreadsheet looks like this:

I need to have the formula find all values in column B that have SEM revenue and SEM cost.

Thanks for all the help

 

[Haseeb Avarakkan]

Hello, Try this;

Hope your data is in Sheet1 & values are in C:F

Sheet2!A1, enter Row #

Sheet2!A2, enter below formula

=MATCH("SEM*",Sheet1!B:B,0)

Sheet2!A3, enter & copy down...

=A2+MATCH("SEM*",INDEX(Sheet1!$B:$B,A2+1):Sheet1!$B$65536,0)

Sheet2!B2, enter this & copy across & down...

=INDEX(Sheet1!C:C,$A2)

 

[excellrookie]

Hey Haseeb,

I see how that works.

How would you modify the code if the values are in sheet 1, but the macro will be run in sheet 2.

The function would take the value of cell A1 in sheet 2 and search for that phrase in column B of sheet 1. Once it found a match for that phrase it would copy the entire row (ie row 34 of sheet 1) that that phrase is located and paste it in cell A5 of sheet 2. The function would do this until it found all rows that have the matching value in column B of sheet 1.

 

[Haseeb Avarakkan]

Just change the "SEM*" to "*"&$A$1&"*"

Then Change =INDEX(Sheet1!C:C,$A2) to;

=INDEX(Sheet1!A:A,$A2) & copy across & down...

 

[excellrookie]

Just change the "SEM*" to "*"&$A$1&"*"

Then Change =INDEX(Sheet1!C:C,$A2) to;

=INDEX(Sheet1!A:A,$A2) & copy across & down...

Oh you are the man!

So having followed all those steps, it keeps repeating the same row, row 18, over and over. How do I prompt Excel to move on to the next value that matches the contents of cell A1

to clarify my poor written logic.

The formulas that you suggested find the match for cell A1 in sheet 2 in column B of sheet 2. The only thing is that when the =MATCH("SEM*",Sheet1!B:B,0) formula executes, it keeps coming up with the same value instead of finding the match in row 18 and then moving on to the next match, row 24.

 

[Haseeb Avarakkan]

If you enter the below formula in A2 of Sheet2;

=MATCH("*"&$A$1&"*",Sheet1!B:B,0)

will give the first matching row.

A3, copy down..

=A2+MATCH("*"&$A$1&"*",INDEX(Sheet1!$B:$B,A2+1):Sheet1!$B$65536,0)

will give you the 2nd matching row. You need to add 1 to A2.

When you copy to A4, A5, A6.... will give the 3rd, 4th, 5th..... matching rows.

 

[excellrookie]

If you enter the below formula in A2 of Sheet2;

=MATCH("*"&$A$1&"*",Sheet1!B:B,0)

will give the first matching row.

A3, copy down..

=A2+MATCH("*"&$A$1&"*",INDEX(Sheet1!$B:$B,A2+1):Sheet1!$B$65536,0)

will give you the 2nd matching row. You need to add 1 to A2.

When you copy to A4, A5, A6.... will give the 3rd, 4th, 5th..... matching rows.

It works! In my excitement I missed the second formula.

Thank you thank you. Let me play around with this and see what I can do. Thank you so much for helping me out. I have been trying to solve this puzzle for quite a while now