Comparing and matching data

[twofootgiant]

hi everyone

I am trying to compare a list of product codes on sheet 1 column A, with a larger list of product codes on sheet 2 column B.

My aim: when i click to button on sheet one the part numbers that are in both lists (sheet 1 and 2) Should be moved into sheet 3 columns B and be rearranged so that they match horzontally with the full sheet two pricelist which will have now moved to column A sheet 3. This should leave gaps in column B where there was no matching part number.

I hope that makes sense.


here is what I have so far:

Please Login or Register  to view this content.

Its not quite doing what i want it to....

any help?

[Leith Ross]

Hello twofootgiant,

From your code it looks like "Sheet1" is the master list. This macro copies sheet1Rng values to "Sheet3" after "Sheet3" is cleared. Each cell in sheetRng2 is then compared with each cell on "Sheet3". When there is a match, the price from "Sheet2" is copied onto "Sheet3" for that product. If you have a lot of product codes, say over 1000, this method will be slow and get slower as the list grows. There is a better and faster way to do this.

Please Login or Register  to view this content.

[twofootgiant]

Hi

Thanks for the help

there seems to be some problem with the NEXT R at the end of the code. When i try to compile it it says 'invalid NExt control variable reference'

[qff]

Hi

looks like there's a "next" missing.

Add Next above Next r


qff

[twofootgiant]

hi
thanks for the help

this still doesnt seem to work to full effect.

there are 1822 product codes on sheet 1 and about 3500 on sheet two. For some reason when i run this code only 1822 products are put onto sheet3 column A and some are in column B matching them, but there are gaps.

This is sort of what I wanted, but i need sheet 3 to end up with column A filled with the 3500 from sheet 2 and column b with the ones that match that list from sheet 1.

Know what i mean?

Any help would be much appreciated as i think its nearly right

[Leith Ross]

Hello twofootgiant,

Now I understand what you want. I have re-written the macro to correct the problem and also have made it more efficient. Try this version and let me know if there are any problems. It worked in my tests, but that is no substitute for the actual data.

Please Login or Register  to view this content.

[twofootgiant]

hi

thanks for the update.

however now im confused...

it seems that all this code is doing is copying the data from sheet2 to sheet3.
none of the product numbers from sheet1 that match with sheet two have been moved and lined up with their partner.

any ideas...?

[Leith Ross]

Hello twofootgiant,

It isn't clear to me from our conversations were the problem is. Post your workbook and I'll review it.

[twofootgiant]

Hi
here is the book

Sheet1 is a list of numbers which we have completed a task for each. Sheet2 is a complete list of all products. I need to be able to look at sheet3 and know which products we havnt done.

Preferably I would like the list from sheet2 in columnA and the ones that match this list from sheet1 to be in columnB lined up with there matching number.

That way I will know by looking at the list that the ones without a code in columnB are the ones that I havnt done yet.

Thanks

[twofootgiant]

anyone wna help me out?

[Leith Ross]

Hello twofootgiant,

I have made some changes to the macro. The data on "Sheet2" column "A" is copied to "Sheet3" column "A". The data on "Sheet1" column "A" is then checked against "Sheet3" column "A". Matches are placed on "Sheet3" column "B".

Example
Actual data. Numbers in blue are row numbers on "Sheet3"

Please Login or Register  to view this content.

[twofootgiant]

You sir are excellent.

thanks alot for your help.

[Leith Ross]

Hello twofootgiant,

Good to hear that it is working the way you want. If you have any questions, just ask.