Forum Discussion
Formula if a cell contains a certain text then return that text in a different cell, otherwise move
Hi
I need to create a formula that will copy the text from a cell in a different sheet if it contains the text "7X1", otherwise move to the cell below and if that one contains "7X1" copy that one, otherwise move onto the cell below, and so on until it copies on the new sheet everyone that only belongs to 7X1. Does anyone have any idea of how to do this please?
5 Replies
Not clear what exactly is required. One more variant for
is
=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,1/ISNUMBER(SEARCH("7X1",$B$2:$B$8))*(ROW($B$2:$B$8)-ROW($B$1)),ROW()-ROW($B$1))),"")If all you need to do is summarize the occurrences of "7X1" in another sheet, then why not use a PivotTable? There's no need for complex formulas, or separating the data that way.
Hi Sandra,
Supposing that the original data starts from cell A1 in Sheet1, please put this formula somewhere in the second sheet and then drag it down:
=IF(ISNUMBER(SEARCH("7X1",Sheet1!A1)),Sheet1!A1,"")Regards
- Hi
Thank you very much for your responses. Filtering will not help though. I tried the ISNUMBER formula and it’s not working. I need the formula to write the next 7X1 on the first sheet on a consecutive below cell on the second sheet with no black cells in between.
Thank you very much though
Hi Sandra,
if you just want to know the data then use "Filter -> Contains". If you really need to copy then use "Advanced Filter".
