Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search for Values in Columns with criteria (VBA)

0

Hello guys,

Need some help for VBA, I attached a file for example

I have a huge list of ID_Numbers, for which I have to perform two checks:

Check 1: For each ID Number in sheet ID, I have to verify if the codes below the ID Number are in the sheet Check 1 (in Check1 each code is linked to an ID Number, therefor the check must take into account this criteria). If found should return "YES" in Column Check 1, otherwise "NO"

Check 2: Same verification, but in a general list of codes (in Check2 the codes are not linked to an ID Number). If found should return "YES" in Column Check 2, otherwise "NO"

I mention that the match has to be approximately: for e.g. if the code is K2C , and in the list we are searching K2CE13 is encountered, the result should be "YES". Also, it doesn't matter how many times is encountered. 

I've tried a lot with *LIKE* function, but I am not familiar enough with VBA

Many thanks for your support

excel
MS Office Version: sebas14
posted by: sebas14 (rep: 16) on Sun Nov 15, 2020 at 6:37 pm
Answer
Discuss

Answers

0
Selected Answer

Your Check 1 is a standard task for the COUNTIFS() function.

=COUNTIFS(Check1!C:C,A2,Check1!B:B,A1)

This function counts the number of instances that the value in A2 appears in Check1!C:C where Check1!B:B = A1. Of course, A1 doesn't ever appear anywhere on your Check1 tab because "ID_No1" is spelled with a space instead of an underscore. I also prefer the space to the underscore but in this case spelling must be identical. So, I suggest you change the spelling in cell ID!A1.

The formula will return a count. Your requirement is that the result should be returned as "Yes" or "No". That can easily be done by embedding the function in an IF() function.

[A2] =IF(COUNTIFS(Check1!$C:$C,A2,Check1!$B:$B,A$1), "YES","NO")

The $-signs have been set so that you can copy the formula from its original position at A2 both down and to E2, H2 etc. Just remember to adapt the column captions in D2, G2 etc.

Your Check 2 requires just a single COUNTIF() but the lookup value must include wild cards. This is acciomplished by adding an asteric to the lookup value, like A2&"*".

[C2] =IF(COUNTIF(Check2!$C:$C,A2&"*"), "YES","NO")

The formula is designed to be copied to columns F and I as well as down.

posted by: Variatus (rep: 4889) on Mon Nov 16, 2020 at 2:31 am
Discuss

Discussion

Thanks a lot for your details. I already tried to integrate formulas in a  macro(below e.g.), using this  MATCH formula, COUNTIF might be better, macro working by entering somehow in manual the formulas into the code VBA. Now, my problem is that I have hundreds of columns with IDs, and would be fine to have a macro which would make formula work for hundreds of columns, by using for eg the header information to know what formula to put in specific columns.

Sub MatchValues()   Dim ws1 As Worksheet, ws2 As Worksheet Dim LR As Long Application.ScreenUpdating = False Set ws1 = Sheets("Checklist1") Set ws2 = Sheets("Checklist2")   On Error Resume Next LR = ws2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row On Error GoTo 0 'LR = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row              ws2.Range("C23:C" & LR).Formula = "=IF(B23="""","""",IF(ISNUMBER(MATCH(B23," & ws1.Name & "!C:C,0)),""YES"",""NO""))" ws2.Range("E23:E" & LR).Formula = "=IF(D23="""","""",IF(ISNUMBER(MATCH(D23," & ws1.Name & "!C:C,0)),""YES"",""NO""))" ws2.Range("G23:G" & LR).Formula = "=IF(F23="""","""",IF(ISNUMBER(MATCH(F23," & ws1.Name & "!C:C,0)),""YES"",""NO""))"  
sebas14 (rep: 16) Nov 16, '20 at 4:43 am
If you write code to deal with a situation that is almost the same as the one you are quoting as an example you will get code that almost works. My friends know me for saying, "almost the same is different".
So, where is your question? It's been asked and answered. Now you have a new question, based on the changes you made because of the last answer. Therefore I suggest you ask a new question where you have room and format to say what you want.
You may have hundreds of ID columns, none of them like the one you published. With tghe new question, don't repeat the same mistake.
The big question you have to answer before you ask is whether to use code to write formulas or results. When fixing your car you wouldn't normally call a mechanic to call a mechanic to do the job. But if such is your need here you would need to explain why letting code write formulas is better than letting it write results. You can't do that without showing the result you want, and neither would it be possible to either design the formulas or the code that writes them.
Variatus (rep: 4889) Nov 16, '20 at 5:08 am
You are right, noted. I will try to make it more clear in another question. Many thanks,
sebas14 (rep: 16) Nov 16, '20 at 5:27 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login