Help with Conditional Compare Needed

J

Jack_58

Board Regular

Joined

Dec 3, 2002

Messages

208

• Oct 14, 2004

• #2

here is an example of what I need to do,

The Spreadsheet below is the tab "increased by Star"

On Order Differences.XLS
	A	B	C	D
1	Item	POQty	StarBumpQty
2	004283	24	144
3	082709	30	48
4	084727	42	45
5	084741	168	170
6	087193	21	22
7	087193	63	64
8	087193	21	22
9	143242	9	48
10	166797	10	12
11	232064	3	5
12	348508	318	360
13	348565	138	144
14	427153	75	84
15	430579	468	480
16	469724	6	12
17	583377	108	112
18	653121	100	102
19	675892	24	98
20	755203	752	756
21	775056	60	72
22	836726	27	30
23	847012	12	60
Numbers increased by Star

I need to be able to compare The Item Numbers Against the following Tab on the same spreadhsset

On Order Differences.XLS
	A	B	C	D	E
11	Byr	SupplierNumber	SupplierName	Div	ItemNumber
12	16	77450	J-O-MPHARMSERVICES	003	952426
13	16	77450	J-O-MPHARMSERVICES	008	342329
14	16	77450	J-O-MPHARMSERVICES	008	130401
15	16	77450	J-O-MPHARMSERVICES	010	130005
16	16	77450	J-O-MPHARMSERVICES	017	133306
17	16	77450	J-O-MPHARMSERVICES	020	011957
18	16	77450	J-O-MPHARMSERVICES	024	667939
19	16	77450	J-O-MPHARMSERVICES	024	296624
20	16	77450	J-O-MPHARMSERVICES	027	538835
21	16	77450	J-O-MPHARMSERVICES	027	931081
22	16	77450	J-O-MPHARMSERVICES	027	132589
23	16	77450	J-O-MPHARMSERVICES	027	215046
24	16	77450	J-O-MPHARMSERVICES	027	902926
25	16	77450	J-O-MPHARMSERVICES	030	296954
Compare

and have the Numbers that are the same on the "increased by Star" Tab, Hightlight the numbers that are the same on the Compare Tab.

Hope this helps some.

Thanks Again!



Jack

 

Upvote 0

J

Jack_58

Board Regular

Joined

Dec 3, 2002

Messages

208

• Oct 14, 2004

• #3

Correction, I only want to be able to highlight tthe Items Number in the compare Tab, not both.

 

Upvote 0

O

onlyadrafter

Well-known Member

Joined

Aug 19, 2003

Messages

5,703

Platform

1. Windows

• Oct 14, 2004

• #4

Hello,

Must be quick - need to go home.

In the Compare sheet in COlumn F enter this formula

=IF(ISNA(MATCH(E1,'Numbers increased by Star'!A:A,0)),"",MATCH(E1,'Numbers increased by Star'!A:A,0))

then Select cells A1:E1 select FORMAT --> COMDITIONAL FORMATTING and change cell value is to Formula is and enter

=$F1>0,

format as necessary,

Copy F1 downas far as necessary, and format other rows usiung the format painter.

 

Upvote 0

J

Jack_58

Board Regular

Joined

Dec 3, 2002

Messages

208

• Oct 14, 2004

• #5

I am not sure about this line code code,

then Select cells A1:E1 select FORMAT --> COMDITIONAL FORMATTING and change cell value is to Formula is and enter

=$F1>0,

format as necessary,


Do I enter it in the Compare Tab or the other Tab. In the Conditional Formatting or ?


Thanks!!!


Jack

 

Upvote 0

Zack Barresse

MrExcel MVP

Joined

Dec 9, 2003

Messages

10,881

Office Version

1. 365
2. 2010

Platform

1. Windows
2. Mobile
3. Web

• Oct 14, 2004

• #6

A possible solution posted here ...


http://www.vbaexpress.com/forum/showthread.php?t=1132

 

Upvote 0

J

Jack_58

Board Regular

Joined

Dec 3, 2002

Messages

208

• Oct 14, 2004

• #7

When I use the Conditional Formatting, It highlights all my entries, not just the ones from the other tab. Any Ideas?


Thanks



Jack

 

Upvote 0

Zack Barresse

MrExcel MVP

Joined

Dec 9, 2003

Messages

10,881

Office Version

1. 365
2. 2010

Platform

1. Windows
2. Mobile
3. Web

• Oct 14, 2004

• #8

Jack,

If you want conditional formatting, this will work ...


Select your Numbers increased by Stars sheet, select the column with your numbers in it, and name the range 'Numbers' (Insert -> Name Define). Do the same for your Compare sheet, but name that range 'Compare'.

In your Numbers increased by Stars sheet, select column A (or your item number col), select conditional formatting and type this in the Formula Is ...

=(A1<>"")*(ISNUMBER(MATCH(A1,Compare,0)))

Now go to your Compare sheet and select the entire column of item numbers there. Again choose conditional formatting, Formula Is ...

=(E1<>"")*(ISNUMBER(MATCH(E1,Numbers,0)))

This works for me. So does the macro I wrote (link above). HTH

 

Upvote 0

J

Jack_58

Board Regular

Joined

Dec 3, 2002

Messages

208

• Oct 14, 2004

• #9

I would like to be able to sort my Spreadsheet by Highlighted color. How can I do this? Is it possible to build it into the code (that is on the link that firefytr has posted on this thread), or maybe have the MAtching item numbers that are now being highlighted in green appear at the beginning of the worksheet.

Thanks Again



Jack

 

Upvote 0

Zack Barresse

MrExcel MVP

Joined

Dec 9, 2003

Messages

10,881

Office Version

1. 365
2. 2010

Platform

1. Windows
2. Mobile
3. Web

• Oct 14, 2004

• #10

For that Chip Pearson has some good info here Jack ...

http://www.cpearson.com/excel/SortByColor.htm

 

Upvote 0