Comparing two ID's values on unique named entities in VBA - Then highlight & show rule violations

[MurdochQuill]

Hi all,

I have a challenge where I am dealing with large sets of data for an astronomy project (mapping anomolous planets)! I would prefer not to do any Vlookups, as there is currently a cell-by-cell approach to comparing this array that is very slow.

I'm trying to compare the ID's in col B according to sets of rules in col N. I would preferably like to include a dictionary of rules for these data comparisons, or pop them into a VBA module as a large list of rules, as there may be upwards of 50 - 200. The datasets of planet names may be up to 500 or more columns, and there may be up to 500 rows of ID's. Also note, the ID's will always change position with each new dataset, but I would like them to follow the logged rules wherever they may be. The planet names will also change on each new dataset, but these will never have rules attached as there will be a huge amount of planets being listed which may all be unique.

Basically, I'm trying to flag the values which violate rules in col N in the array, and populate another column with the results of the violations.

Please see below as an example of what a small table may look like.

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N
1			NAMES OF PLANETS/STARS ETC
2	ID:		Saturn	Jupiter	Sol	Earth	Mercury	Mars	Mars II	Neptune		Flagged Names:	Rule Dictionary:
3	AA		33	8	25	19	17	29	46	31		Jupiter (AA>FD)	AA>FD
4	FD		8	20	12	21	43	1	6	20		Earth (AA>FD)	AA+FD=HG +/- 20%
5	HG		47	4	17	8	20	13	30	12		Mercury (AA>FD)	DF+V= <HY
6	DF		20	27	22	22	2	24	22	31		Jupiter (DF+V= <HY)	etc
7	VC		50	24	13	36	13	1	10	42		Sol (DF+V= <HY)	etc
8	HY		16	46	31	42	32	46	31	38		Earth (DF+V= <HY)	etc
9	GHF		47	45	20	45	18	36	41	1		Mercury (DF+V= <HY)
10	FGJHG		18	3	28	16	41	13	29	12		Mars (DF+V= <HY)
11	SR		36	43	43	32	6	26	33	4		Mars II (DF+V= <HY)
12	EWTR		35	42	18	1	26	10	31	22		Neptune (DF+V= <HY)
13	HGF		50	43	23	23	37	38	37	46
14	UYK		11	35	21	3	32	35	47	25
15	CVB		2	10	27	37	33	49	26	31
Sheet1

Fyi I would love to have this 100% in VBA, and if anyone has some genius ideas on comparing this data while being light on CPU usage would be a godsend!!!
& any links that may be valuable for learning how to optimize my VBA code for CPU usage would be appreciated.

 

[MurdochQuill]

Bump ! Please let me know if any more data is required

 

[MurdochQuill]

Anyone got advice on how I can approach this problem?