Looking for Macro to copy information within a cell

[thespeth]

Hello,

I have a table with lots of work orders for my office and I'm trying to separate them out by floors. The output from our work order application reads like this:

	A	B	C	D	E
1
2
3
4
5
6	#	Floor	Work Location	ID	Task Name
7	01		\Locations\DCCII\9th Floor\N9-01J	111,111	Cold Call
8	02		\Locations\DCCI\9th Floor\W9-05L	111,112	Light Out
9	03		\Locations\DCCIII\5th Floor\E5-01A	111,113	New Chair

I've added the column B myself, and I'm trying to have two things happen:

1st, I want to perform a search on Column C and pull out the floor information for Column B. For the cells in my example, if would pull out N9, W9 and E5. However, there are 15 floors at my office, E4, E5, N1 - N11, W8 and W9. I tried to have this searched with a ISNUMBER, but it got goofy with so many choices. The reason I can't just have it say floor is because we have multiple buildings, so there are, for instance, two 9th floors (one called W9 and another called N9).

2nd, I'd like to perform a search on Column B and then input the entire line onto another worksheet. That way, I could have a separate worksheet for each floor in my office. I have no idea how to go about this one.

Any help would be appreciated, and if I'm missing something or if I need to provide more information, let me know. Thanks!

[swoop99]

Well, the E5 N9 etc bit is the bit you're interested in, right? So ignore the floor and ignore column B.

you just need a text parser to bring out the N9 E5 whatever bit. If the very end of each line in column C is always going to be -01J (for example), ie 4 characters, then try this:

Please Login or Register  to view this content.

[jaslake]

Hi thespeth

Welcome to the Forum!

I'd think this could be done with the Split Function if Column C is always of the same structure. If you'd like to post a sample of your file rather than a picture I'll be happy to look at it.

[thespeth]

Hi thespeth

Welcome to the Forum!

I'd think this could be done with the Split Function if Column C is always of the same structure. If you'd like to post a sample of your file rather than a picture I'll be happy to look at it.

Here is my workbook report, slimmed down to just a few examples.

workbook.xls

[jaslake]

Hi thespeth

Try this code...I believe it does as you describe. Let me know of issues.

Please Login or Register  to view this content.

[thespeth]

It works for the most part - however it does run into an error if the structure of the location isn't the same. Sometimes our locations do have the floor only and don't end with the N1-01A structure. Is is possible to skip over these if it doesn't fit that structure? Right now it errors out if it hits one of these non-conforming locations. Thanks!

[jaslake]

Hi thespeth

Show me a file that has these non-conforming items. Include in the file any other anomalies that may exit.

[thespeth]

This one has what I'm referring to...

[jaslake]

Hi thespeth

I see 5 records that don't have the trailing type N1-01A structure. So, should these be totally ignored or what do you wish to do with them? Are there any other anomalies?

[thespeth]

Really they could just be ignored unless you have an idea on how to sort them. I could run a conditional format on the list and have it jump out, picking the oddballs out.

These are the only anomalies I have on this list, but I could certainly have more in the future for future work orders.

[jaslake]

Hi thespeth

Well, I'm really looking for direction from you...I can ignore them, delete them, copy them to a separate worksheet that has only these anomolies...what do YOU want to do with them? Conditional formatting doesn't help me but it may help you if you decide to ignore them. You decide...let me know.

[jaslake]

Hi thespeth
I decided to copy them all to a separate sheet. This code does that. If you wish something different, let me know.

Please Login or Register  to view this content.