Cross Reference two columns and return common values?

So I've been messing around with this script for a while now.

I have two columns organized/formatted like so:

COLUMN A

C180.0001
C190.0022
C306.2201


COLUMN B
C180.0001.0234
C806.1123.1234
C190.0022.1234
C306.2201.4567
C306.2201.5431



I want this script to essentially take the Values in Column A, check them against Column B (note the xlPart in the code) then select all the values in Column B that contain anything from A. These values are then to be listed in column C

Please Login or Register  to view this content.

The code isn't working properly, I get an error "Subscript out of Range" on this line:

"Set r3 = Range(Range("C2"), Range("C" & UBound(iArray())))"

Also, when I DO get the script to work (sometimes it does) - it's not doing what I want, and it's not collecting the proper values.

Any help is greatly appreciated

Try:

Please Login or Register  to view this content.

Thanks for the reply!

I see what you're doing in the script, and when I ran it sort of worked - however, I think I need to re explain what I'm trying to do.


I have about 7000 rows of data like this, and I need to select (in column B) all the values that have the same first 8 characters (technically nine with the period, ex C100.0001) as ANY of the values in Column A. So I want to cross reference the entire column to one another.

Is there any way to modify the script to do something like this?

Change this line:

Please Login or Register  to view this content.

to this line

Please Login or Register  to view this content.

I tried this, and it only works when the data in Column A is right beside (offset -1) the data in column B. I need the script to work by outputting/selecting ALL the values in Column B that have the same first 9 characters as ANY of the values in Column A.

Would that be possible at all?

I'm not sure what you mean. If your data is in columns A and B, then the code should work. To make it more clear, post a copy of your file with a sheet containing your source data and another sheet that shows what the data should look like after the macro is run.

here's a sample:

SampleBook.xlsx

Fixed it. By removing the line

Please Login or Register  to view this content.

I managed to achieve the outcome I was looking for.

Thank you!

Glad it worked out.