Mark Cell Based on Two Columns

[bobredford]

Hi all,
I'm trying to find a way to mark a cell based off two matching columns.

A colleague has suggested two nested "Do Whiles" but the original sheet goes over about 7000 cells which would take even the macro a bit, even with optimized code.

I've thought about even just using formulas, but I can't find out how to logic that either based off how one of the tables is set up (The one on the right)

I've attached a doc with a "Before" and "After" mockup.
I've been stuck on this for too long to admit...

[Special-K]

In C6
=IF(ISNA(MATCH($B6,IF(C$5=$H$6:$H$10,$G$6:$G$10),0)),"","X")
Array formula, use Ctrl-Shift-Enter
copy across and down as far as E8

Bear in mind this is an array formula so it may be slow if you have 7000 cells.
Try it and see I guess.

[karedog]

Using code :

Please Login or Register  to view this content.

[bobredford]

You guys solved it so quick!
Both codes worked perfectly!

I hate to trouble you more, but would you guys be able to explain them a bit?
Thank you both so much

[karedog]

You are welcome.

My method is using the "collection" object to do fast lookup.
You can googling using keywords "excel vba collection object introduction" to know more about it.
The first loop is to load the data (G5:H10) into this collection object.
The second loop then checking each queries (C6:E8) against the collection, whether they are exists or not.

Regards

[bobredford]

I'm looking into collections now through a few guides, but I have a couple questions.
I've added some annotations based off what I can identify and what I am curious about.

Please Login or Register  to view this content.

[karedog]

'Adding what is in column 1 and 2 from a's region. What is "Chr$(2)" and why is Item empty?

>> What is "Chr$(2)"
When we contatenate two string, we need to add a delimiter character(s), to make the contatenated string keep unique.
For example :
ROBIN SONDAKH
ROBINSON DAKH
These are two different people, if we just contatenate them, both will be : ROBINSONDAKH, so how can we spot them ?
By adding a delimiter character, for example if the delimiter character is "#", then they become :
ROBIN#SONDAKH
ROBINSON#DAKH
Now, after they are concatenated, we can still spot them.
The delimiter character, should be something that is uncommon used in daily words.
For example, if you choose "@" for the delimiter character, they become ROBIN@SONDAKH and ROBINSON@DAKH, it is very like an email address.
How if there is/are other item(s) that also use this "@" character in the words ? This become a problem again.
So for the safest method, we need to use an almost impossible used character (as delimiter) for this purpose.
A "normal" characters used in daily words, is start from Chr$(32) (a space) and higher, so numbers before 32 are considered safe.
Then why choose 2 ? Well, I'm pretty new joined in this forum, and my senior here is used to be use that character, so I just follow

>> why is Item empty
Because we only need to know whether the key is exists or not.
For example, we create a collection for company, we add :
coll.Add Key:="Manager", Item:="John"
coll.Add Key:="Supervisor", Item:="Mike"
When we need to know the "associated item" with words "Manager", we look in the item property, so "John" is the result.
But how if we only need to know, for example, if the "Manager" (the key) is already added to collection (exists) ? In this case we don't need to know the "associated item".
So if from the beginning we already knew that we need the "key" part only (we don't need the "associated item"), we can add "Empty" as the item, like :
coll.Add Key:="Manager", Item:=Empty
coll.Add Key:="Supervisor", Item:=Empty
This way, we can save the memory (RAM) from unnecessary things.

---

'Admittedly lost here...

Lets look at this sub :

Please Login or Register  to view this content.

If you run the above sub, the program is successfully print out the c("One"), but is failed for c("Three"), because this key is not added yet (not exists).
So the "collection way" to check whether a key is exists or not, is by wrapping inside "On Error Resume Next ... On Error Goto 0" code block, then examine whether an error is occured.
If no error occoured ("Err.Number = 0" means no error) then the searched key is exists, if an error is occoured (Err.Number = 5) this means the search key is not exists.

Please Login or Register  to view this content.

so your comment :
'If theres an error above; fill in with "X"
should be :
'If no error occurred (Err.Number = 0, which means the searched key is exists), fill in with "X"

---

'What does this do?

It is the way to write back an array to Excel sheet.
For example if we have 3x2 array size like this

Please Login or Register  to view this content.

If we try to put the array to Excel sheet like above on cell M1, only the topleft array is put there ("A").
We need to resize the range as the same size as the array, but using .Resize method, like this :

Please Login or Register  to view this content.