Using if or lookup finction to identify coloured cells

[djdbg]

Hi I am using Excel 2010.

I have a very large spreadsheet, 20,000 rows and 15 colums.

In column "F" i have various types of named accounts and I have used conditional formatting to highlght red some of these accounts. (I want to delete all customers whom have these accounts).
In column "D" I have customer IDs. Customers may have more than one type of account so the ID's don't appear in colum "D" just once, there will be between 1 and 10 entries depending on the number of accounts held.
What I want to do is highlight the customer ID's when it is connected to any cell that is red.
For example, if customer has 4 accounts, then the ID will appear 4 times in colum D. If one of the accounts in Column "F" is highlighted red, i want all the ID entries to be highlighted.
Purpose of this is to remove from the spreadsheet all the customer ID's when assocaited with account type that is highlighted red in column "F".

Thanks for any help.

 

[Akashwani]

Hi and welcome to MrExcel.

What is the Conditional Formatting formula used to highlight in Red?
Can you use this formula in a "Helper" column to then filter by the result?

Sample Data and Example Results....

Excel Workbook
	C	D	E	F	G	H	I	J
1	*	Cust ID	Data	Acc Name	Helper	*	CF Formula	*
2	*	12345	100	Test1	1	*	TRUE	*
3	*	12346	200	Test1	1	*	TRUE	*
4	*	12345	300	Test2	0	*	FALSE	*
5	*	12347	400	Test1	1	*	TRUE	*
6	*	12346	500	Test2	0	*	FALSE	*
7	*	12345	600	Test3	0	*	FALSE	*
8	*	12348	700	Test1	1	*	TRUE	*
9	*	*	*	*	*	*	*	*
Sheet16

The formula in the "Helper" column needs to be copied down.
You can then filter your data based on 1.

To Conditionally Format the Cust ID the same as Column F....

Press alt H L R
Click in the Applies to box just after = and select your range in Column D type a comma , between the two ranges, eg...
This =$F$2:$F$8 becomes this =$D$2:$D$8,$F$2:$F$8
Click Apply.

I hope that helps.

Ak

 

[kevatarvind]

can you show the current condition which you using to highlight account ?

 

[djdbg]

Hi and welcome to MrExcel.

What is the Conditional Formatting formula used to highlight in Red?
Can you use this formula in a "Helper" column to then filter by the result?

Sample Data and Example Results....

Sheet16

*	C	D	E	F	G	H	I	J
1	*	Cust ID	Data	Acc Name	Helper	*	CF Formula	*
2	*	12345	100	Test1	1	*	TRUE	*
3	*	12346	200	Test1	1	*	TRUE	*
4	*	12345	300	Test2	0	*	FALSE	*
5	*	12347	400	Test1	1	*	TRUE	*
6	*	12346	500	Test2	0	*	FALSE	*
7	*	12345	600	Test3	0	*	FALSE	*
8	*	12348	700	Test1	1	*	TRUE	*
9	*	*	*	*	*	*	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 30px"><COL style="WIDTH: 76px"><COL style="WIDTH: 30px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula G2 =IF(F2="TEST1",1,0) I2 =$F2="Test1" <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

The formula in the "Helper" column needs to be copied down.
You can then filter your data based on 1.

To Conditionally Format the Cust ID the same as Column F....

Press alt H L R
Click in the Applies to box just after = and select your range in Column D type a comma , between the two ranges, eg...
This =$F$2:$F$8 becomes this =$D$2:$D$8,$F$2:$F$8
Click Apply.

I hope that helps.

Ak

Thanks for the quick responses and warm welcome, much appreciated. I have used site for few years but never been quite as badly stuck as this.

The data I have conditionally formatted red is based on the cell containing certain text for accounts I do not want.
(Using work computer and i can't download software to include screen dumps sorry.)

7901382 </SPAN>	Test 1</SPAN>	CURRENT ACCOUNT PLUS</SPAN>
7901382</SPAN>	Test 1</SPAN>	INSTANT ACCESS ACCOUNT</SPAN>
7901382</SPAN>	Test 1</SPAN>	SAVINGS ACCOUNT PLUS</SPAN>
7901382</SPAN>	Test 1</SPAN>	STANDARD BUSINESS</SPAN>
7912512</SPAN>	Test 2</SPAN>	SIGNATURE CURRENT ACCOUNT</SPAN>
7912512</SPAN>	Test 2</SPAN>	READYCASH ACCOUNT</SPAN>
7930050</SPAN>	Test 3</SPAN>	CURRENT ACCOUNT PLUS</SPAN>
7930050</SPAN>	Test 3</SPAN>	STANDARD BUSINESS</SPAN>
7940968</SPAN>	Test 4</SPAN>	INSTANT SAVINGS</SPAN>
7940968</SPAN>	Test 4</SPAN>	STANDARD BUSINESS</SPAN>
7940968 </SPAN>	Test 4 </SPAN>	STANDARD BUSINESS I would be looking to highlight all of the IDs 7901382 Red as one of the accounts is Red in 3rd column. Same goes for 7930050 and 7940968. </SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[Akashwani]

Hi,

Do you mean something like this?...

Sample Data and Example Results...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Cust ID	Data	Acc Name	Helper		Criteria		CF Formula
2	7901382	Test 1	CURRENT ACCOUNT PLUS			STANDARD BUSINESS		TRUE
3	7901382	Test 1	INSTANT ACCESS ACCOUNT					TRUE
4	7901382	Test 1	SAVINGS ACCOUNT PLUS					TRUE
5	7901382	Test 1	STANDARD BUSINESS	7901382				TRUE
6	7912512	Test 2	SIGNATURE CURRENT ACCOUNT					FALSE
7	7912512	Test 2	READYCASH ACCOUNT					FALSE
8	7930050	Test 3	CURRENT ACCOUNT PLUS					TRUE
9	7930050	Test 3	STANDARD BUSINESS	7930050				TRUE
10	7940968	Test 4	INSTANT SAVINGS					TRUE
11	7940968	Test 4	STANDARD BUSINESS	7940968				TRUE
12	7940968	Test 4	STANDARD BUSINESS	7940968				TRUE
13
Sheet17

The formula in the "Helper" column needs to be copied down.
The formula in H2 can be used for your Conditional Formatting.
If you don't want to use cell F2 to hold your criteria, you can change the INDEX/MATCH formula to this...

=IFERROR(INDEX(A2:A2,MATCH("STANDARD BUSINESS",C2:C2,0)),"")

There is probably an easier way/method/formula, but I'm sorry, that's the best I can think of.

I hope that helps.

Ak

 

[djdbg]

Thank you Akashwani. I can make that work for me. Cheers.

 

[djdbg]

One last thing, With the matching criteria, you have set a single cell and this works for me, however i have multiple matchig criteria and it won't acknowledge the range. How should tis be entered? You have $f$1, i have tried ($f$1:$f$64) but only provides a blank.

 

[Akashwani]

Hi,

Can you give me the range of ALL your data and ALL the different criteria you have?
You can post a sample of your data using the link in my signature or..

The HtmlMaker here...
https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Or by using this method...
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

Ak

 

[djdbg]

hiya, thanks for your patience here,

Your formula of =IFERROR(INDEX(D2:D2,MATCH($U$1,F2:F2,0)),"") was just about perfect except I need $u$1 to be a range from $u$1 to $u$64.

There are 64 types of account i want to highlight, not just one as the above tabel posted earlier shows.
The whole sheet is A1:O20728, this doesn't include the additional coluns added to accomdate your solution.

 

[Akashwani]

Hi,

I'm sorry, I still don't understand your situation or requirement.
It really would be very helpful for you to display a visual representation of your data with the results you expect.
You can try this to post Sample Data and your Expected Results like I did above...

How to

You DO NOT have to show your actual data, just something that represents it....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Data1	Data2	Data3	Cust ID	Data	Acc Name	Helper	Data4	Data5	Data6	Data7	Data8	Data9	Data10	Data11						STANDARD BUSINESS
2	Red	Car	Yes	7901382	Test 1	CURRENT ACCOUNT PLUS		100	A	Data6	01/04/2013	X	10	19	11
3	Blue	Bus	Yes	7901382	Test 1	INSTANT ACCESS ACCOUNT		200	B	Data6	02/04/2013	X	19	19	14
4	Green	Car	No	7901382	Test 1	SAVINGS ACCOUNT PLUS		300	A	Data6	03/04/2013	X	16	15	11
5	Red	Train	Yes	7901382	Test 1	STANDARD BUSINESS	7901382	400	A	Data6	04/04/2013	Y	11	20	10
6	Blue	Car	Yes	7912512	Test 2	SIGNATURE CURRENT ACCOUNT		500	B	Data6	05/04/2013	X	11	20	19
7	Pink	Bus	No	7912512	Test 2	READYCASH ACCOUNT		600	A	Data6	06/04/2013	Y	10	20	11
8	Yellow	Car	Yes	7930050	Test 3	CURRENT ACCOUNT PLUS		700	A	Data6	07/04/2013	X	15	11	19
9	Red	Train	Yes	7930050	Test 3	STANDARD BUSINESS	7930050	800	B	Data6	08/04/2013	X	13	15	16
10	Blue	Car	No	7940968	Test 4	INSTANT SAVINGS		900	C	Data6	09/04/2013	Y	17	19	19
11	Green	Bus	Yes	7940968	Test 4	STANDARD BUSINESS	7940968	1000	B	Data6	10/04/2013	Y	18	14	13
12	Pink	Car	No	7940968	Test 4	STANDARD BUSINESS	7940968	1100	C	Data6	11/04/2013	X	13	14	16
13
Sheet17

Ak