Excel (2007) Macro or Formula

[trussman]

Our inventory program is garbage. We have to export the information to Excel and modify the spreadsheet to get the information we need. That is not a big deal, except it can take 4-5 hours to modify the report.

Below is the what a normal row looks like in Excel.

Part #: Sq ft Qty:
2123/5018BRZ/4800/28800 14,400

The last two groups of numbers in the part # are the panel sizes. The inventory program cannot take the total sq ft on hand and give a panel qty. So in the example above, the panel sizes are 48" x 288" (4' x 24'), and the total panel count should be 150.

Can a macro be written to look at the last two sets of numbers in the part # and determine the final panel count?

Copy and pasting a report into a workbook with individual formulas set up for each line item will not work. We are constantly adding different sizes.

Any help would be appreciated. Thanks!!

 

[AnotherHiggins]

-> Macro or Formula

I'd say both, in reverse order.

First figure out how to get the spreadsheet in an acceptable format in as few steps as you can.

In you case, you might be able use Data > Text To Columns with [highlight]/[/highlight] as the delimiter. Or you may have to pull the components apart with formulas (Left, Mid and Right along with Find/Search). It's impossible to say unless we know what the various part number formats might look like. Are they always
[tab]4 digits - slash - 7 digits - slash - 4 digits - slash - 5 digits ?
[tab]Are there always 4 and only 4 slashes?
also
[tab]Do you need the first two sections to stay "attached" or can they be split apart into two cells? (2123/5018BRZ vs 2123 5018BRZ)

Give us more detail and we'll help you figure out this part.

Then, after you know how to do it in the spreadsheet, turn on your macro recorder (Tools > Macros > Record new Macro) and go through the steps.

Then observe the code that was generated ([Alt]+[F11]). Copy and Past that code into forum707 for help cleaning up the macro.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[trussman]

The part number varies. Unfortunately. For example: 2123/5021OPLNOUVPE/3600/12000. All the part numbers will have the / for a division. As for the number of slashes, yes those will remain the same number.

Somehow the macro or code will have to look at the end of the part number, and work backward to determine the panel size.

When we export to Excel, it comes through like any other spreadsheet. The format is acceptable. Our customers request specific quantities of sizes, not total sq ft. So we need to be able to look at the part number, description, and total panel quantities on hand.

The first two sections, actually really don't mean anything. It is the last two which need to be formulated. So if a macro can be written to split out the last two sets of numbers into individual columns, then a formula can easily be written in another column.

 

[GlennUK]

Look at what John ( anotherhiggins ) wrote:

In you case, you might be able use Data > Text To Columns with / as the delimiter.

... have you tried that?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Definitely, if you ALWAYS have the same number of delimiters (DASHES), then use Data > Text to columns...

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[trussman]

No I have not tried the Data > Text yet. I am not sure how to do that. Can you give me an example?

Thank you.

 

[trussman]

Ok. I was able to figure out how to do it. But on the one example I tried, the number did not transfer correctly. The last set of numbers is 14400, and in the destination column, the number turned to 1,100.00.

Also, is there a way to convert the 14400 to just 144?

I will keep working with it to at least get the number to transfer correctly.

Thanks for any help.

 

[trussman]

Ok. In the example I am trying to convert, it is not taking the group of numbers I want transferred. For example, the part number is 1100/7018CLR/0000/14400. I want the 14400 converted to the Length column. The number should be 14400. But it is taking the first set of numbers, 1100, and placing them in the Length column.

I choose the group of numbers I wanted converted, but it is not taking them.

 

[trussman]

I was able to figure out the Data > Text part. I actually took the information and put it in a different section of the worksheet, kind of off page. Then did formulas to bring the correct information back into the correct columns.

All the suggestions were great. Thanks for the help.

 

[SkipVought]

If there are ALWAYS three SLASHES, then

1100/7018CLR/0000/14400 becomes...
[tt]
A B C D
1100 7018CLR 0000 14400
[/tt]
there will ALWAYS be the values in each of the FOUR columns. Dependin on how you select each column's FORMAT, three of these values may be NUMERIC to TEXT.

"But it is taking the first set of numbers, 1100, and placing them in the Length column."

Text to columns places the values IN THE ORDER THEY APPEAR IN THE SOURCE STING.

Why are you getting bent out of shape about the Length column. Column placement is a nit. If you must, reocrd a macro to switch thing around after the parsing takes place, but aaaai would not wast my time!

"Also, is there a way to convert the 14400 to just 144?"

Sure!!!
[tt]
=cellref/100
[/tt]
but it will be in another column.



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[trussman]

Skip - that is what I figured out. I took that information and placed further over on the right side. The I took the final column (14400) and had that come over to my length column with the =cellref/100 formula.

Still working on trying to change this from a 4-5 hour process down to an automatic process. Later I will try to turn this into an external reference. It will look at the latest inventory report, and automatically update itself.

Again, thanks so much for the help. This is the best forum I have seen for this type of help.