Extract comments to multiple rows

**drewship** · 11-02-2009, 10:41 AM

Hello all.
I have a situation I hope someone can assist with.

I have been provided a spreadsheet which has a Quantity column "H". In this column is the quantity of the items and a note containing the serial numbers of the items. The note has no real format. Here are some examples:

Example 1: Serial numbers: 161702, 161722, 161740, 161741.
Example 2: Dec 2006: s/n 63194
Jan 2007: shipped 1 widget, 52480
Example 3: Sent in 2004: 717, 718
Apr 07: shipped 63522 and 63524.
Oct 30, 2007: K. Grande reported only two widgets onboard, 63522 and 63524. 717 and 718 are missing.
Nov 2007: 160302.
Dec 14, 2007: shipped 161093.

There are thousands of serial numbers on hundreds of rows like this. I am looking for code that would copy a new row to another sheet for each serial number in the comment with the serial number placed in column "J". I don't know if the extraneous text can be filtered out, but if I can at least get everything on a seperate row, I can sort/delete what I don't need a lot faster than doing this manually.

In have uploaded an example with before and after sheets. The After sheet is what I have done manually so does not match the examples I used on the Before sheet.

Any help would be appreciated.

Thanks in advance,
Andrew

**Leith Ross** · 11-02-2009, 12:19 PM

Hello drewship,

The big problem you have here is the lack of a standardized format in the comments. The serial numbers aren't a certain number of digits in length and are indistinguishable from other numbers. The serial numbers are not always prefixed with either "SN" or "s/n" nor on separate lines. Without some standardization of the format, separation won't be easy, quick or accurate.

**drewship** · 11-02-2009, 12:45 PM

Thanks for replying Leith Ross. I understand the lack of standardization is the major problem...and I am sure there are even more variations than I provided in my examples. I started to do this manually...and may still need to do some depending on what can be designed in some code, but I am hoping there is a way to do some of this via code. The person who was managing this just had no concept of how hard he was making things for himself and the rest of us.

If anything can be done to ease my pain...it will be greatly appreciated!!!!!!

But I understand if it can't be done.

Thanks,
Andrew

**shg** · 11-02-2009, 01:17 PM

See if the attached is close; run macro ExtractComments. It will still pick up some spurious values, like the 3 in Aug 07: shipped 3 widgets.

**drewship** · 11-02-2009, 03:00 PM

SHG...You are a wizard!!! A quick look at the results is very promising!! I am not worried about single and double digits since all serial numbers are at least 3 digits, so I can sort and delete out the rest. I will run this against a copy of the spreadsheet and let you know how it fares...

I only noticed one year (like 2007) in the results. How did you prevent them?

Thanks again,
Andrew

**shg** · 11-02-2009, 03:02 PM

... since all serial numbers are at least 3 digits

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**drewship** · 11-02-2009, 03:27 PM

Well I spoke too soon about the 3 digits. I found another problem with the comments. Seems he took a shortcut and listed just the last 1 or 2 numbers in a series of serial numbers...like 13456, 57, 58, 59...etc. Not a big deal though. At least they are separated on their own lines, I can compare the comments and the new sheet side-by-side more quickly than if I had to copy and paste every one of them manually. I am going to mark this solved since your code has done what I asked...greatly reduced my pain!!!

Thanks again,
Andrew