Match columns and list cell

[sk8shorty01]

Hello all,

I am attempting to match two columns in a document and I have figured out how to get it to look for a match with the following code line:

Please Login or Register  to view this content.

It lists in column "C" when the entry in column "A" (same row) matches up with any of the entries within column "B". This is a start but not exactly what I would like to have happen.

For my general use I would like it to continue to match but if possible, instead of just saying "MATCH" it would be nice to know where the match came from. Is there a way to get it to either just list the cell that the match would be found in, or possibly say "MATCH, Bxx" so that I can quickly find the matched up cells. I am attempting to compare about 36,000 line items so I would hope to narrow that down as much as possible.

Another note, I would like the above code to drag all the way down the column "C" so that A1 match displays in C1, A2 in C2, etc. and I cannot figure out how to get that to happen. If anyone could help there I would greatly appreciate that as well.


Thanks and have a great day!


Mike

[Steffen Thomsen]

Hi,

insert this formula in c1and drag down

=ADDRESS(MATCH(B1;$A$1:$A$23;0);1;4;TRUE)

Adjust the range to look in in the formula

[sk8shorty01]

The formula did not work as entered. It has an error in it somewhere.

Side Note/Question:

I am writing a macro in VBA to copy/paste columns from another document and then I want to compare the columns, A and B, as I said above.

Is there a way I could implement the code (or a variation of it) into my macro so that I do not have to insert it and drag it down?

Here is what I have so far:

Please Login or Register  to view this content.

[sk8shorty01]

I am still unable to get that code line to work. I have googled it and haven't found out why it gives me an error. I have tried changing the range, cells, etc and nothing seems to work.

Any thoughts?

[sk8shorty01]

I researched this pretty much all night last night without any luck.

If anyone has any links or possible solutions for me I would greatly appreciate it, I might end up having to do all of this by hand and I was just hoping to avoid that if possible, if not then I can just start looking through everything and seeing what I can come up with.

[Steffen Thomsen]

Can you upload an example workboook with the raw data (Dummy data, should reflect teh actual data structure) and with a sheet of the desired outcome, then i'll have a look at it later to day.

Steffen Thomsen

[sk8shorty01]

Here are a few dummy files that should give you an idea, at least I hope. Let me know if you need anything else.


The three files attached would be the two I am comparing, and the one called FINALTEST is the one that I would like to copy everything too and then run the actual comparison on, if that would be the easiest way.

There is a possibilty that some of the matches could find a number more than once, if that is the case and it is difficult to list each cell location I would be happy with it just saying Match or whatever the case is so that I know there is more than one match.

Hope this helps you help me (haha) because I am surely at the end of my rope. I don't know what I am doing anymore haha.


And just so you know I can use a macro I have to copy paste from a previous macro and just modify the code so that it will copy/paste to the right columns. If you would like that code it is listed below:

Please Login or Register  to view this content.

[Steffen Thomsen]

Have you dragged the formula all the way down?

As the formula is now it will return #N/A if the value is not found:

Try this formula instead

This formula will return blank if there is no value to match, it will return not found if the values not found and it will return the cellreference if there's a match.

Use this in column c:

=IF(B2>0;IFERROR(ADDRESS(MATCH(B2;$A$1:$A$23;0);1;4;TRUE);"NOT FOUND");"")

Use this in column g:

=IF(F2>0;IFERROR(ADDRESS(MATCH(F2;$E$1:$E$23;0);5;4;TRUE);"NOT FOUND");"")

Use this in column k:

=IF(J2>0;IFERROR(ADDRESS(MATCH(J2;$I$1:$I$23;0);9;4;TRUE);"NOT FOUND");"")

You place these formulae in the respective cell 2 and drag it all the way down.

If the other rows gets updated, so will the match!

[sk8shorty01]

I still get an error when I attempt to do it, when I paste the code into C2 it does nothing, just goes in as text, when I double click the cell to edit and then hit enter it activates the formula but comes back and says:

The formula you entered contains an error.


And thats it.

Does it work for you?

The code I posted above seems to work fine for me, the first one where it shows the word "MATCH". Not sure why I cannot get that one to work.

Sorry for the issues, I am no programmer, thats for sure haha.

[Steffen Thomsen]

Maby your version of excel uses comma-seperation as to mine!

Try changing colon in the formula with comma

[sk8shorty01]

I changed the code to:

Please Login or Register  to view this content.

Now it says "You have too many arguments..."


So it went from just saying I had an error to now actually specifying the error. Believe you are on the right track. Wish I knew what I was doing haha, I might be able to self diagnose a little better and not bother you so many times over little things like this.

[Steffen Thomsen]

Please Login or Register  to view this content.

You removed to much

Try this

[sk8shorty01]

That worked, now I have another quick question.

How can I input that code line into my above macro "COMPARE" so that it will put the code line in C1 and then drag it down the column automatically?

I will end up having about 100 different compares to run and if I can I would like to make this process as automatic as possible. If its to much trouble its no big deal, you already answered my question I had so that is good enough, but if I can add it I would love to in order to make this process a little more streamlined.

Thanks again!

[Steffen Thomsen]

Add this to your code

Please Login or Register  to view this content.

Do the same for the other 3 columns

[sk8shorty01]

Thank you very much, you have been a huge help!

[Steffen Thomsen]

Your welcome,

If this has solved your question, then please mark the thread as solved!