Matching Values and Copying to A new sheet.

[adienspade]

I have been searching through the forums for examples and haven't been able to find exactly what I am looking for.

Ok here is my issue. I need to compare the Conf # and line number on both sheet 1 and 2. So basically if the Conf # and the Line number match between page 1 and 2 then it copies the Conf #, Item Number(Sheet 1), and Detail(Sheet 2) to sheet 3 in a row. This needs to continue till the end of the Sheet 1. I have a pseudo report created with no code because I don't have a clue as to where to start.

If anyone can give me a push in the right direction I would greatly appreciate it.

[MatrixMan]

This will be fairly straight-forward, but a couple of questions before I start ...
1. Conf # .. is this Order in your example?
2. Line Number ... is this Line Note?
3. Since you're only looking for (presumably) Order & Line to be the same across sheets 1 & 2, and since there are only 3 cols on each sheet with the third one being the only difference ... and since you only want the 3rd from sheet 2 ... then why can't you just take all the details from sheet 2? Let me know if the answers to any of these is "no" ... otherwise, you just need Order & Line (cols A & B) to be the same and then take the whole record from sheet 2 ...

[adienspade]

Correct on both, conformation Number = Order and Line Number = Line Note.

Hopefully I will anwser the 3rd question well enough to help out.
You can take the details from sheet 2 but the reason for sheet one is to match the part number with the detail column on sheet 2. I assumed you would have to match the two together to get the part and detail to match on sheet three, unless I am way overthinking it here.

[MatrixMan]

OK - let's make our terms consistent; you have:
Sheet1 - Order, Line Note & Part Number
Sheet2 - Order, Line Note & Detail.

You want to find all records where Order & Line Note are the same across Sheets 1 & 2 and you want Order, Line Note & Part Number (which can all be taken from Sheet1) copied to Sheet3 ... Is this right? Or do you want the Detail from Sheet2 as well?

[adienspade]

Correct on terms for sheets 1 and 2, and I appoligize for making this confusing.

The actual line not doesn't need to be copied over the the 3rd sheet. The only things needed for sheet three is Order, Part Number, and Detail.

[MatrixMan]

OK - here you go ...

Please Login or Register  to view this content.

[adienspade]

Thank you so very much that is exactly what I was needing! You are a hero in my books. I have been struggling with this for a week now on this issue and you made it look so easy, my hat is off to you.

[adienspade]

I ran into a small problem, lol there is a possiblility that there might not be a match. Basicly I have an order which says it has a line note(page 1), if you go to the Ordernotes(page2) tab there is no order. On the attachment I threw a order with note on page one and left it out on the second tab for an example if anyone could help.

Second I know it is possible I just can't think of it, if there is no match after going through all the orders, then I need it to copy the order and part over and then go to the next order.

I haven't change the code to much as of yet. Only thing I have done is change the rows, sheets locations. I have attached the original code, and my minor modification as of yet. I commented out all the code that I have worked on.

[MatrixMan]

Hi Aidenspade ... sure - no problem: here's an amended code that will trap this for you; I've annotated what's going on in the comments so you can follow it ... I've also added a boolean variable called booMatchFound.

Please Login or Register  to view this content.

[adienspade]

I ran the code and it stops after Orders with number 00004 it doesn't capture the rest of the orders in the sample which are 00005.

[MatrixMan]

Oh dear Egg on face ... sorry about that ... yes, if you can't be sure of a match then you can't have the internal counter (j) commencing from the last outer counter (i) ... and I also picked up that there was an Exit For that needed to be removed ... Here's the code and the file is attached:

Please Login or Register  to view this content.

[adienspade]

Your amazing as always! Thank you so much works prefectly.

[adienspade]

I'm back with another minor issue(hopeing). I ran into while working with the code. There are orders coming in with mulitple detail notes.

I would assume that it would be a major undertaking to capture all detail notes per Order, but i'm not sure. Since the pseudo file is there basicly I didn't know if you(or anyone who anwsers) would need a revamped one for a test. So I am attaching one again with the slight modifications with the issues I am seeing.

Thanks,
AdienSpade

[adienspade]

Ok...sorry for bugging everyone, After staring and the code for another 15mins I figured out how to get the mulitple lines. I know its possible to make it cleaner, but how would I go about I am not sure.

If possible the format I would like to see is:
Column A--------------Column B----------Column C
Order Number----------Part Number---------Detail
-----X-----------------X--------------More Detail
Order Number----------Part Number---------Detail
-----X-----------------X--------------More Detail
---- X-----------------X--------------More Detail

And so on if it is possible, otherwise I can live with it

[MatrixMan]

I think if you take out the "Exit For" on line 48, that should do it (the one that says "assuming only 1 match is possible") ... That was only in there to make it more efficient. Let me know if you run into a problem So - this:

Please Login or Register  to view this content.

[adienspade]

Yeah I feel like an idiot about that lol.

Is it possible to have the report format like I was hoping in previous post? Just curious is all.

[MatrixMan]

Yep - here you go: just make the first paste of the first two columns conditional on booMatchFound being false and remove the sort at the end.

Please Login or Register  to view this content.

[adienspade]

Thank you so much for your help again.