only conditional formatted data need to move to new sheet

IMNEWTOEXCEL

New Member
Joined
Apr 26, 2018
Messages
5
Hi,

this is my first post and totally new to excel, I have conditional formatted data it looks for any data which has these words (interface TenGigabitEthernet), (ip address) and (interface Bundle) and highlights it in Yellow, i want to move only this data to another sheet. example below. there are 6000 lines in total and only 50 lines which are highlighted after the conditional formatting and those i need moved.

interface Modular-Cable3/3/2:11
description Node 1
cable bundle 1
cable rf-bandwidth-percent 30

<colgroup><col></colgroup><tbody>
</tbody>
interface TenGigabitEthernet3/3/0
ip address 1.16.1.1 255.255.255.252
interface Bundle1
ip address 1.16.1.1 255.255.255.252


Thanks for the help
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,410
Office Version
365
Platform
Windows
Welcome to the Board!

Why not just use the same formula you use for Conditional Formatting in a new column, that indicates which rows you want. i.e.
Code:
=IF([I]conditions[/I],"MOVE","")
Then you can use this calculated field to determine which rows to move.
You could use Filters, or copy/paste/sort, etc. Whichever method you like best.
 

IMNEWTOEXCEL

New Member
Joined
Apr 26, 2018
Messages
5
Hi Joe,

thanks for the reply, so all my data, all 6000 lines are in column A, and i want to move selected conditional data to column B and it has to be sequential, what would the syntax be, your help is appreciated, since i am totally new to this. i am using office 2007.

this is what i tried =IF(A2=conditions,B2,"") but giving me name error.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,410
Office Version
365
Platform
Windows
I thought you wanted to move it to another sheet, not another column on the same sheet!

Note that you did not use my formula correctly. My instruction was to set up a helper column to identify which rows to move.
In the formula, you would replace "Conditions" with the Conditional Formatting formulas/conditions you are currently using.
What are you current Conditional Formatting rules?

If you set up the formula properly in the structure I initially proposed, all the Conditional Formatting rows would have the word "MOVE" in column B, while the other rows would be blank.
Then, you could filter on this column to return/move the rows you want.

To see how to use Advanced Filters to move those rows to a new range, check out the "Move Data To Another Worksheet" section in this article: http://www.contextures.com/xladvfilter01.html
 

IMNEWTOEXCEL

New Member
Joined
Apr 26, 2018
Messages
5
Thanks Joe, that worked perfectly for me :). Thanks also for the link give me a lot of additional information.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,410
Office Version
365
Platform
Windows
You are welcome!
Glad I was able to help.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,113
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top