Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Range?

**Norcal1** · 01-30-2019, 09:10 PM

Bit stuck on this one (had been on a roll previously).

We have some reports that require importing data into a new sheet within a workbook each month (that much is fine) and then comparing the rows against the previous month.

Example sheet attached with very basic random generated data, but what I want to do is loop through the new worksheet (19-Jan) in column B and look at the value(s) in B (IF column A is a 4, but I can probably add that later). I then want to compare each value in column B (in a row with a 4 in column A) to the range in column B on Sheet 18-Dec (last month, and again if Column A has a 4 at the same row).

This would be an If statement, with the idea being that if B4 (value=1200.01.) on sheet 19-Jan exists somewhere in the range of B4:B1570 on sheet 18-Dec, then you move on.

If value in B4 (1200.01.) on Sheet 19-Jan does not exist in range B4:B1570 on Sheet 18-Dec, then it would do something (either store in a string, prompt a msgbox, etc). This is part I am stuck on as I haven't really moved past for and if statements with VBA into index/match/search/etc which I am assuming is what we are looking for. The value has to be an exact match, and the end game would be to get a list at the end of the loop with all values in the range on 19-Jan that DO NOT exist in the range on 18-Dec.

Any help appreciated, I will keep plugging away.

Thanks,

**AlphaFrog** · 01-30-2019, 09:46 PM

You could do it with a formula. On sheet 19 Jan, put something like this in row 3 any empty column and copy down.

=IF(A3=4,IF(ISERROR(MATCH(B3,'18-Dec'!B:B,0)),"No Match",""),"")

You could have a macro insert the formula.

**bakerman2** · 01-30-2019, 10:10 PM

Or something like this.

Please Login or Register  to view this content.

**Norcal1** · 01-31-2019, 02:08 AM

Thanks for the responses, was/am trying to learn the right way to do this with all VBA but inserting the formula in a helper column may be my best bet to get this done now. I need to review more, but that snippet of VBA is (appears) way over my head at the moment.

I was trying to do it with nested for + if loops which I am sure is not the most efficient way, but even that I hit a deadend at least for now. The idea was

If A is 4, then
Check value in B
Go to other sheet
If A is 4 then
Check B against B from previous sheet, if match end loop
If no match, go to next row
Etc, etc

Will give it a go again tomorrow

**bakerman2** · 01-31-2019, 02:10 AM

The code I provided does exactly that.

**Norcal1** · 01-31-2019, 02:18 AM

Bakerman.

Thanks, I'll try and review and step through it. Beyond copy-pasting it into the editor, I don't think I could recreate it on my own yet so I'll study up and see if I can get a handle on what each line is doing so I can tweak to our final product. Thanks again

**bakerman2** · 01-31-2019, 02:30 AM

Please Login or Register  to view this content.

This creates 2 dictionaries (some sort of containers)

Please Login or Register  to view this content.

This puts the values of Column A & B from Jan-sheet into an array and loops all values.
When there's a 4 in column A it' puts the corresponding value of Column B into the first dictionary (dic )

Please Login or Register  to view this content.

This does the same but for december sheet (dic2)

Please Login or Register  to view this content.

This compares every element from the 1st dictionary (dic) with the second dictionary (dic2)
If there's a match then the value gets deleted from the 1 dictionary (dic)

Please Login or Register  to view this content.

This puts the remaining values in 1st dictionary (dic) onto your worksheet.

**Norcal1** · 01-31-2019, 01:17 PM

Awesome, thank you for the explanation. I had to change "Sheet4" to "Sheets("19-Jan") and "Sheet1" to "Sheets("18-Dec") or else I was getting a 424 error on the first with statement.

Really appreciate the step explanations - few questions if you have time:

We are setting dic and dic2 to be objects (just a location to store the arrays of the two ranges, correct?)

Is "sn" a variable of some type? Does it not need to be declared? (still very green with all the but the basics)

Is the "range within a range" just saying to start at A3, then count down the number of rows and that is your new range, then resize that range (with A3 still as the upper left reference point) no additional rows but 1 additional column wider to capture column B as well, and then store that as range as "sn"?

Assuming UBound is the "upper bound" of sn rows ie the last row? The for then loops down the range in sn and like said, if A(i) = 4 then store B(i) within the dictionary established as dic2?

What role does x0 play? Is that another variable or some type of assignment to the dictionary unique to it?

The For Each Key makes sense, I just probably never would have known that syntax existed, thanks!

Is the final transpose just because the dictionary is implicity storing those values in a 1 Row, multiple column array and we want it in a multi row, 1 column array?

Thanks again for the help. Either way this seems to work and I'll keep reviewing it, I just like to fully understand the code I've been adding so that I can understand how and where to tweak it to do different things.

Originally Posted by bakerman2

Please Login or Register  to view this content.

This creates 2 dictionaries (some sort of containers)

Please Login or Register  to view this content.

This puts the values of Column A & B from Jan-sheet into an array and loops all values.
When there's a 4 in column A it' puts the corresponding value of Column B into the first dictionary (dic )

Please Login or Register  to view this content.

This does the same but for december sheet (dic2)

Please Login or Register  to view this content.

This compares every element from the 1st dictionary (dic) with the second dictionary (dic2)
If there's a match then the value gets deleted from the 1 dictionary (dic)

Please Login or Register  to view this content.

This puts the remaining values in 1st dictionary (dic) onto your worksheet.

Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Range?

LinkBack

Thread Tools

Rate This Thread

Display

Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Range?

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Re: Loop Through 1 Range (Down a Column) and Find Values that Do NOT Exist in another Rang

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Check if other strings exist in column based on range string values

[SOLVED] Check if other strings exist in column based on range string values

[SOLVED] Find and Replace Loop that looks up values in range for Search criteria

Find text in range of cells and add to table if it does not exist in range

Lookup range of values to see if any of them exist in a single column

Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range

Need to find specific values in range using loop

Bookmarks

Bookmarks

Posting Permissions