VBA combine two column to find the parent item

[isasa74]

Hi Guys,

I hope that someone can help me

Please I strongly would need a piece of code that:

• lookup for all the values in Column H in Column B
• when the value is found the code should look at column A to search up (towards the top) the first higher level row.
• and from the higher level row pick up the parent item in column B and combine it to the values in H
• create a combination in E:G of the value in H plus the Parent in B and the Parent description in C

I have attached an example because of course it is much easier to understand if you look at the file.

few important points are:

1. The list in H doesn't have duplicates
2. A value in H can have more than one parent in B
3. Both H and B can be very long list (thousands of items)

PLEASE HELP

[Arkadi]

isasa74,

I looked at your file. One of the column H values (3d one), has a match in column B on row 11 but also row 25.
Your example finds the parent, using the match from row 11.... how would the program know to use row 11 instead of row 25?

[isasa74]

Hi Arkadi,

thanks for your reply

In my example I actually have "completed" the check for the first finished product that goes from ROW 2 to 15, but actually the VBA should go over the entire list or products (thousands) and creating all the possible combination Parent-Child, and even repeating the same combination when encountered.

let me know if it is clear please

[Arkadi]

And the "parent" is found by matching column B, then checking A and finding the first row UP where A = A from match -1 ?

[isasa74]

in coloum A you have the Level of the items (0 Finished product;then lv. 1;2;3...) , so the parent will be found looking up the column A for the first number < of the one in the row of the match.
So if the child is found in a row with a lv 2 in coloumn A the parent will be the first item towards the top with lv 1 in A.

thanks so much

[Arkadi]

How about this:

Please Login or Register  to view this content.

[isasa74]

THANKS! it works fine almost always but unfortunately it fails when in H there are values that have no match in H , debug puts in yellow the below string:

Set myMatch = Range("B2:B" & lrB).FindNext(myMatch)

[Arkadi]

Check my previous post again.... I added 2 red lines that hopefully fix the issue.

[Arkadi]

That last fix was lazy... the proper way would just be to do one check to see if the range is nothing, just move the endif to the end:

Please Login or Register  to view this content.

[Arkadi]

Isasa,

Werkt het nu? If the problem is solved can you please mark the thread as closed?

[isasa74]

You are great!! the code works very well, I don't know how to thanks you!!

The only thing is that in H I should be able to distinguish those values that haven't been found in B , it is my fault because I didn't take into consideration that since the begin sorry about that.

would it be easy to have the not found values in H in red?
I'm asking that but I have no idea about how long it could take for you to implement this function in the code, so please if it is too much work don't worry I will find a solution myself

A minor issue is also that at some point with a long list the spreadsheet grays out and it looks like it is hanging even if it is not.

Dank u wel

[Arkadi]

Yes the "Hanging" effect is unavoidable... you could, at the very start put a message that this may take some time .... something like MsgBox("Please be patient, the process may take some time.")

As for the not found values in H turning red... very easy (the red line of code below):

Please Login or Register  to view this content.

[Arkadi]

And if you want to thank us when we help you can do 2 things... we appreciate it if you hit "Add Reputation" below our post... but this is optional.
The most important thing is that once your problem is solved, please mark the thread as "Solved"

[isasa74]

I added reputation for you already thanks you are a magician!!

I will add the message... I thought that the hanging effect was unvoidable...

Today I've added a piece of code that I recorded via MACRO to get rid of possible duplicates in H before starting your magic.
however because the Sheet is protected it didn't work.
I must protect basically only the headers because I made them a bit better looking adding some objetcs and logos.

I have also tried to unprotected the sheet via VBA before getting rid of the duplicates and then protect it back at the end of the code.
Unfotunately the result is that when the VBA protects back the sheet I have many locked cells, so not only the headers ike I wanted


do you have any suggestions on how to do that please? THANKS!

[Arkadi]

When VBA protects sheets, or when you protect manually, all cells that have "locked" property will be locked. So basically, unprotect your sheet by hand, then click on the top left of the sheet so that all cells are selected, and right click, choose "format cells" then go to the "Protection" tab, and clear the "locked" checkbox. Then select only the cells you really want lock, right-click again, and put a checkmark in the box next to "locked". Now when you protect the sheet again, only those cells will be locked.

Cells being locked or not has no effect at all until you protect the sheet.

[isasa74]

I did like you explained because i know how it works, the only cells that I've locked via Format are those that I actually wanted to protect (headers) and if I use the ReviewTaB to protect/unprotect it works fine.

However if I do that via VBA, when the code has done its job, I end up with many other unwanted locked cells...

What I did exactly is:

added to the begin of your code a VBA command to unprotected the sheet
added a piece of code ,recorded by macro, to delete the duplicates in H
Added a command to the end of your code to protect back the headers.

unfotunately the result as I said is many shattered unwanted locked cells all over the sheet, and that way, if someone save the file after using the tool, the next time copying/pasting data in the sheet will be impossible because the random locked cells.

At the momement I'm at home so I don't have with me the version of your code with the small changes that I've added, but really nothing wrong with them, I think they were ok because very simple commands and because I recorded them via MACRO.

It looks like the issue is coming from using the VBA to protect/unprotect the sheet.
is there a way to get rid of the duplicates from H even with the sheet protected?
thanks Arkadi

[Arkadi]

isasa,

My guess is the code to protect the sheet is causing this... maybe it has more than it needs. All you need is:
1. Unlock all cells, and lock only ones you want protected. (manual)
2. in code, at start just after Set ws = Worksheets("TEXT") put:

Please Login or Register  to view this content.

then at the end:

Please Login or Register  to view this content.

instead of password use the real password of course... haha
If you just use those 2 lines then it should not lock any new cells. I assume the recorded macro had more stuff in it?
I hope it works this way, let me know and if any problems I can help more tomorrow... I guess by now it is getting pretty late in Nederland, 9:30pm by my calculations. (I was born there btw)

[isasa74]

Hi Arkadi,

waar in het Nederland ?

I have studied all your code and I understood almost everything thanks to your notes THANKS!
Nothing to do I tried everything ...there is now why I can use the code with a protect sheet without giving up to:

1) delete duplicates in E:G and H
2) having the red fonts for the unmatched values in H

If you want just out of your curiosity I've attached the file with the tool.

I have also a question please! is there a way, like a string that I can change to have not only the exact matches but also the small variant, an example will clarify everything:

For some part numbers I have also the "next version" so : 1xx.xxx.xx (is version one) 1xx.xxx.xx.2 (version 2)
so I wonder if is it possible to do a search with the star at the end 1xx.xxx.xx*

P.S.
whenever you get bored by this thread give me a sign and I'll close it

[Arkadi]

Hi isasa,

In Zaandam... howel ik maar 3 jaar ('73-'76) in NL gewoond heb, ik heb er nog veel familie.

I'll be happy to look at your file, I am about to leave work (early today) so I am not sure if I can get to it today but will look at home tonight or tomorrow... yes, stars are possible when searching.

Question: How do you copy/paste the data? Is it possible you are copying locked cells from somewhere else and that that is the reason you end up with locked cells after protecting via code?

You could try: unprotect at start.... then at the end :

Please Login or Register  to view this content.

[isasa74]

Hi Arkadi

We have almost the same age 1974

here the code that I'm using.

About The unprotect/protect

commands are working fine, the red font for the unmatched works fine as well thanks!

Issues:

1)Major issue :Not all the duplicates in E:G and in H are deleted (???), is that probably because for duplicate deletion I've used the very simple code coming fron a macro recording?

2)Minor issue (probably linked to the duplicate issue too) the code messes up with the cell format in E:G and in H- basically the cells turns from all bonders to whithout borders



About the search with the star.

It is actually possible to do it but with an issue:

Issue

1)in the combinations in G the reported value will be always 1xxx.xxx.xx* with no distinction between 1xxx.xxx.xx.2 ; 1xxx.xxx.xx.3 etc.
Similarly if I search for 7* I will have only 7* in G, and not 7abcc ; 7cccss etc.

I've looked at the code and probably this happens because the code is reporting in G the values in H and not the value of B, right?
Is there a string that I can add to have the matches in B exactly reported to G please?

Arkadi I feel I will have a very powerful tool thanks to you and I don't know how to thank you for this, if you want something from Holland just name it and I will ship it off to you

Please Login or Register  to view this content.

[Arkadi]

Hi again,

1)Major issue :Not all the duplicates in E:G and in H are deleted (???), is that probably because for duplicate deletion I've used the very simple code coming fron a macro recording?
I don't fully understand.... your code should delete all the duplicates in H only, but if your header is in row 1 then it will not remove duplicates of row 2. That is because your range starts on row 2 (and will only check down to row 548), but if you say header:=xlYes then it thinks row 2 is header... just change the range to start at H1. If you remove duplicates in column H then there should be no doubles in col E:G? For E:G if you remove duplicates again, start at row 1 if you have headers in row 1.
2)Minor issue (probably linked to the duplicate issue too) the code messes up with the cell format in E:G and in H- basically the cells turns from all bonders to whithout borders
I think removeduplicates deletes the entire cell that has a duplicate... and all the blank cells in the range count as a match too... best way is probably to just add borders in the code at the end.

The last part with * search I also am having trouble understanding I think.... but if I DO understand then you want to report match value in B on G instead of the original search value from H. Try this copy of the code and then let me know what is still wrong? (This code hopefully fixes your * search alo). I also cleaned up the removeduplicates code a bit...

Please Login or Register  to view this content.

[isasa74]

Hi Arkadi,

I'm absolutely speechless...you have read into my mind and despite my not always precise indications you managed to sort all the issues out and I have now a perfect tool.
I will be grateful forever for this and again mate whatever you may like from your home country please don't hesitate to contact me with a pvm and I will ship it off for you asap.

thanks a lot again and again...you are genius and kind at the same time...

[Arkadi]

Hi isasa,

Thanks for your kind words, but I'm not sure I deserve such praise. Quite a few members of this forum make me look pretty dumb when it comes to coding, but I do enjoy it, and helping others solve problems.
I'm glad I was able to help you out, and will gladly do so again if you need more help in the future