Copy lines of text cells, to another area, based on color of text.

**richard11153** · 01-09-2016, 02:45 PM

Hello experts!
I have a long worksheet with lines of "text" going down column C (C200 down to C1300)
What I would like to do is have any lines of text in the color "Red", "copy down" into another section
in the worksheet. (in column CC1320 down to CC1360)

Can this be done with a formula?
(Excel 2003)
(the amount of text lines in red may vary from just a few lines to maybe 40 lines)

Thanks in advance for any help!

**Tony Valko** · 01-09-2016, 02:58 PM

If the formatting was applied using conditional formatting you could write a formula based on the logic of the conditional formatting to extract the data.

If the formatting was manually applied then the only way to do this is using VBA code.

**richard11153** · 01-09-2016, 04:28 PM

Well, I do run VBA for other items, (check & x boxes, etc.)
Its a bit long and I'm sure I cant modify it myself.
The text lines are "typed in" so can this be done with a formula?
If not, I'm sure someone could help me modify my VBA.

Thanks

**Tony Valko** · 01-09-2016, 04:37 PM

You need to extract some data based on the color of the text being red.

WHY is the text red? Is there some piece of the data that meets a condition that it gets colored red?

If there is some logical reason why the text is red we can probably come up with a formula to extract the desired data.

Another option is to use another column and mark those rows with an "X" (or whatever) then write the formula to look for rows marked with "X".

**richard11153** · 01-09-2016, 05:09 PM

Well, I could "code" the text I want to pull down, with several things.
The text line could start with a ~ or > or whatever. (I though color may work the easiest).
I'm just trying to automatically pull "some" of the long list of text lines, down to "summary section".

**richard11153** · 01-09-2016, 05:11 PM

Yes. I could have a cell, just left of the text that I could put an X in (when I want that line
copied in the summary section).
How would that work?

**Tony Valko** · 01-09-2016, 06:31 PM

Like this...

Data Range

	A	B	C	D
1
2	X	Ben Dover		Ben Dover
3		Robin Banks		Anne Teak
4		Joe King		Kerry Oki
5	X	Anne Teak		Justin Thyme
6	X	Kerry Oki		Misty Rain
7		Kris Kringle
8		Dinah Mite
9	X	Justin Thyme
10		Mighty Mouse
11	X	Misty Rain
12	------	------	------	------

This array formula** entered in D2:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(B:B,SMALL(IF(A$2:A$11="X",ROW(A$2:A$11)),ROWS(D$2:D2)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

**richard11153** · 01-10-2016, 02:42 PM

Yes, I see how it works, but I cant seem to "past down" (formulas that work) below and get the lower lines to work.
Can you send me a worksheet with it done?