Find part and add values

I see you are trying to compare two values, one with a added suffix.
If you know how many characters are used in the suffix you can use the ? for a wildcard if statement, else there's * for an unlimited value.

Here's a example

Please Login or Register  to view this content.

you have to be careful though, the use of wildcard is under the assumption that the next change is going to be unique.

If you use ?, have a 101a, and a 101b and are trying to compare it to 101. It will assume both are the same
If you use *, have a 101abc, and 101ab and are trying to compare it to 101. it will assume both are the same

It might help any potential helpers if you could add to your example an (at least partial) illustration of your expected results.

Thanks for the reply, slx.

I don't think the wildcard approach will work, for the very reasons you mentioned. For example, rows 74 thru 78 in the attachment have the same initial part numbers, but the suffixes are all different. The plain part crosses to the "V" part, the "C" part crosses to the "PP" part, etc. This will often be the case.

There will probably also be instances where the UsagePN is completely different from the original PN. Part number GM1225188 may roll up to GM1230145, or something like that.

Any other suggestions on how to make this work?

Are you trying to match values in column D to values in column B? because if you are then it is a straight forward comparison the only thing u need to do is a nest looped that compares two different values on different rows.

I am trying to ADD values for a part found in column D to the values of the same part found in column B. The values I am trying to add are in columns G thru K.

does that make sense?

It looks like it may be working in my test, but if so it is deleting the wrong rows. It looks like it is deleting the NEW part number, (without a number in column D), when I need it to delete the OLD number, the one with the number in column D.

When I tried to edit the script by commenting out "Rows(i2).Delete" it appears to have stuck the macro in an endless loop. I had to ctrl-alt-del to get out of Excel.

How would I modify this to delete the OLD part number, so that I can test it further?

I stand correctd, slx. After further testing, the script appears to be working perfectly as is. I was hopeing the code would be something I would recognize, so I could customize it to work on the full worksheet. I trimmed down my example worksheet to make it easier to understand, and to protect sensitive data.

So, I've copied the code below as I'm using it, and notated it to show my understanding of it, could you, or someone, please let me know if I'm even close?

Please Login or Register  to view this content.

If there is anything you can do to help an idiot understand this script, and how it works, I would be greatly appreciative.

Thanks.

Good morning again, Gurus.

After further testing on the code, on a larger sample file, there still appears to be some errors. The zipe file contains the sample file, with a worksheet both before and after running the macro. It also contains the macro as it is being used.

One of the big errors I have noticed is that if 2 parts roll-up to the same part number, none of the data appears to be added.

If anybody has a suggestion that would make this work, please post. Meanwhile, I'm going to start working on another method utilizing the subtotal command.

Thanks for your time and efforts!

Aside from the fact that the code didn't appear to add the values if 2 parts rolled up to the same part number, I'm curious if this code would work if I ran it a 2nd time on the same data? Would the 2nd time through catch the 2nd instance of the part number? Why did the values not add to the roll-up number if there were 2 parts rolling up, but did if there was only 1?

The reason I ask..., if we could get this code to work, and I could learn it well enough to modify it to meet various page layouts, as needed, this is a code I could probably use several times daily. I'm always comparing sales or inventory on thousands of part numbers, and needing to combine totals for parts where the earlier part number has been superceded.

Again, thanks for the help so far, slx, and please let me know if you or anyone has an idea on how to make this code work.

Hutch

Thanks, T-J.

In my limited testing, the code seems to work really well. I'll have to get some time to test on a larger sample size before I can tell for sure, and have to figure out how to handle the exceptions.

Thanks for the help!!

Hi hutch,

Just to make you aware, I spotted a small error in the code above and have made an amendment.

The 2 lines containing xlDown have been changed to xlUp (highlighted in red).

The original code works ok, but was checking all cells in a column, the new code only checks up to the last row of data, so is more efficient.

Good luck with the next part.

Thanks again, T-J. I had noticed on a larger sample group that the macro was pretty slow. I'll give the revision a try and see if that speeds me up any. (I'm sure it will.)

Thanks again!!