Macro to extract cell content and "rejig" columns

[SubwAy]

Hi folks,

I have been tasked to process on a regular basis several large files (~200,000 lines) after spending all day yesterday to work on one only, I'm thinking there must be a way to do it via VBA. I was hoping someone could point me in the right direction as I've searched the web for answers and couldn't find anything

Basically, the file lists in column A all the assets we own; in column B, the stock items that make these assets; in column C, the stock items that make these stock items in column B; in column D, the stock items that make these stock items in column C, etc until column F

Unfortunately I can't change the way this data is extracted and need to change the layout for processing separately: what I need to achieve is a list over two columns only that basically say that if something was in column C, it needs to be brought back in column B against the same asset (in column A), if something was in column D, it needs to be brought back as well in column B against the same asset.

Other than this massive rejig, I would need to exclude from column A all the numbers and subsequent letters that are after the third "-", and extract from columns B, C, D, E, and F only the first 9 characters.

As I'm writing this I realized how confusing it is so I have attached an example which hopefully explains it.

If it is not doable, then so be it and I'll stick to manual processing but geeeee it is an awful process!

Thanks to anyone who will read this
S

[JBeaucaire]

Assuming the workbook has two sheets in it named BEFORE and AFTER, put the data as shown into the BEFORE sheet and run this macro:

Please Login or Register  to view this content.

I highlighted in red a line of code that is removing the duplicates, so each A:B set of values will be unique. Remove that if you want the dupes.

[SubwAy]

holy moly that's impressive, like really impressive.

I got a syntax error on the red duplicate line but would love to be able to use it. The only thing is that the same stock code could be against different assets so I would like to keep these, but delete when the same stock code is listed several times against the same asset.

Finally, what would I change in the code if I wanted (for example) to have the output stock codes in column C rather than B?

I can't thank you enough, you turned a 8-hour process into something that literally takes seconds.

[JBeaucaire]

Code in post #2 has been fixed and won't error now. The "Remove Duplicates" thing is removing duplicated rows of A:B combined, when an Asset and Stock Code appear together more than once. So I think it should stay in based on what you said.

What happens to column B? You can just cut/paste the output to column C, but what is happening in column B instead?

[JBeaucaire]

Here's the version that outputs to A and C.

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks. It is customary to give feedback to those that have helped you by clicking on the asterisk (Add Reputation) at the bottom of their post.

[SubwAy]

Hi there,

For the time being I'm using the macro that extracts in column B (C will be in a couple of weeks based on new data) but it seems something has gone wrong since your edit.

I now get an "run time 1004 error" as well as no data in column B (the title "Stock Code" disappears) and the last digits still present in column A. Did something change significantly or am I doing this wrong?

[JBeaucaire]

Here's the file I was testing on.

[SubwAy]

The fact that I had some fields populated in the "After" sheet obviously stuffed it up cause I did what you add and it worked so thank you very much Only problem is that it does not extract the letters after the second "-".

One final question: if I wanted to look up a section of the first column (for example in AGITATOR-KW008-LST00N I look for the 008 knowing that it could be longer than 3 characters) in column A of a separate sheet and return the adjacent value in column B, would I be better of having a macro saying the following or finding a macro that does the search on its own:

Please Login or Register  to view this content.

Thanks again.
S

[JBeaucaire]

Glad it's working. Not sure how to respond to that final question in context. Perhaps if I could see that need in your workbook demonstrated I could answer.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks. It is customary to give feedback to those that have helped you by clicking on the asterisk (Add Reputation) at the bottom of their post.

[SubwAy]

I'll create a new thread for my other question then

Could you please tel me what can I delete to remove the truncation part of the asset code? I found a way to get exactly what I needed via formulas but still need the rest of the macro to work to rejig all the columns.

Thanks again

[JBeaucaire]

I'm not sure 100%, my untested thought is to change:

Please Login or Register  to view this content.

...to this:

Please Login or Register  to view this content.

[SubwAy]

Hi JBeaucaire,

Apologies for the late reply, I was out of the office. Brilliant, your recommendation worked perfectly.

Thank you
S