Need a rename macro for excel

[BhagyeshVashi]

Hello,

I am in need of a excel rename macro that helps me in renaming the file as per the level defined in the main input file.
Can someone help me

Attached is sample how the rename should work.

 

[BridgeSmith]

I've found the easiest way to create macros for many things is to record them - start recording, perform the steps you want the macro to do, then stop recording. Then, it's just a matter of adding/editing to make it repeat the steps.

 

[SkipVought]

How about a spreadsheet solution?

Added 3 columns in lt green
My results in lt blue

D3: =IF(LEFT(B3,8)="ASSEMBLY",RIGHT(B3,LEN(B3)-8),F2)
E3: =IF(ISBLANK(C3),"",IF(SUM(C2:C3)=1,65,IF(C3=E$2,E2+IF(OR(E2=72,E2=78),2,1),E2)))
F3: =IF(C3<>F$2,"",IF(AND(C3=F$2,C2=1),65,F2+IF(OR(F2=72,F2=78),2,1)))
G3: =$G$2&TEXT(D3,"000")&IF(ISNUMBER(E3),CHAR(E3),"")&IF(ISNUMBER(F3),CHAR(F3),"")

Workbook below.

Do you realize that you're limited to 24 parts inside an assembly or sub-assembly BOM with this method?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BhagyeshVashi]

This Excel workbook solution looks interesting but considering i would have approx. 1000 parts to generate names for this would be a difficult task for a person who has less knowledge on Excel formulas.

 

[3DDave]

The answer is a macro that pastes the formulas into the sheet when the user doesn't know how to use "FILL DOWN" or "COPY" and "PASTE" to duplicated the formulas.


You could also pre-populate the sheet with 2000 lines of formulas and protect the whole thing except the input column.

 

[SkipVought]

Unless your specifications are incorrect or incomplete, this could easily be accomplished if your table were a Structured Table (Insert > Tables > Table). I will send and example. Right now I'm not at my work station.

Procedural Steps
1. DELETE all but the FIRST ROW of DATA in the table. A short macro can do that.
2. PASTE your new list into the Current Assembly Name column. This will automatically cause all the formulas to propagate into all the rows of pasted data.
3. CHANGE the assembly prefix string.
4. Naturally you'll want to publish the generated names (all of which were generated by a formula) so you can COPY the appropriate columns an then PASTE VALUES into another Sheet that you can publish to the world.

BTW, I would hardly ever pre-fill rows with formulas. There are heartaches associated with such.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I did a few modifications.

1) Inserted a Structured Table
2) Moved the New Assembly Name prefix above the table as well as 2 other pieces of data

So here's the drill. The data to Copy/Paste is on the Data sheet or you can use your own 2 columns of data.

1) Copy the 3 columns of data for new part numbers
2) Paste that data into the FIRST data row of the table

Voilà y'all!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

1000 parts to generate names for this would be a difficult task for a person who has less knowledge on Excel formulas.

You need NO KNOWLEDGE OF EXCEL FORMULAS to use the workbook I uploaded above. PASTE in 10,000 names if you like. The results are nearly instantaneous!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dbill74]

This just came across my feed in YouTube yesterday.
Introducing REGEX Excel Functions

Leila has LOTS of videos on how to use Excel and does an excellent job of explaining how to do all manner of stuff in Excel.

 

[IDS]

Thanks for that dbill74. I hadn't heard about that, although it looks like they are not yet available in my copy of Excel 365.

For those who prefer to read about new stuff:

https://insider.microsoft365.com/en-us/blog/new-regular-expression-regex-functions-in-excel

And for those who don't have the new functions yet, you can do it in VBA:

https://analystcave.com/excel-regex-tutorial/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

The quote is from Jamie Zawinski, a world class hacker who I admire greatly. If he's telling us not to use regular expressions, should we even bother? Maybe, if you live and die by soundbites. But there's a bit more to the story than that, as evidenced by Jeffrey Friedl's exhaustive research on the Zawinski quote.

Zawinski himself commented on it. Analyzing the full text of Jamie's posts in the original 1997 thread, we find the following:

Perl's nature encourages the use of regular expressions almost to the exclusion of all other techniques; they are far and away the most "obvious" (at least, to people who don't know any better) way to get from point A to point B.
The first quote is too glib to be taken seriously. But this, I completely agree with. Here's the point Jamie was trying to make: not that regular expressions are evil, per se, but that overuse of regular expressions is evil.

See

https://blog.codinghorror.com/regular-expressions-now-you-have-two-problems/

Having used regex I can say the first line is often closest to the truth; the problem is that it is very easy to make rules using regex that will either miss cases you think it should catch or catch cases that you expect it would not. The problem being that to avoid such problems one has to understand the entire permutation space that a regular expression might cover. Sure, the easy stuff is easy - picking off the first word on a line by matching the start of the line, some characters and then either (and here's where trouble starts) a space, or a tab, or a comma, or a colon, or a semicolon, or a dash/hyphen, or the end of line. And I am sure there is some character that I have missed which, in large unrefined data sets, leads to really interesting results.

Is regex bad? Nope. Used it a lot within AWK to hack and slash text files from one layout to another. It's a great tool. But it's a tool that has so much flexibility it can be a challenge to understand how to get a rigid solution.
---
That said, the OP is looking for a way to encode the location of an item in an assembly structure in a way that links to the assembly the items are used in; basically one way to identify the leaves on a directed graph by way of the entire path to that item. The top assembly is the primary node, each item on the list gets that node name and the arbitrary order under the node. Each assembly becomes a new node.

The original post is confusing; there is no sub-assembly1; there are items within sub-assembly1.

It would have been nice for the OP to write rules in a text specification for how the levels are created; like why do subassemblies only get a single letter when the top assemblies get 2? What happens if there are more than 26 items in a subassembly? Which letters are allowed? Is it as many as 26? Why is there a gap in Sr.No?

 

[sdz]

Here's my solution using normal functions. No macro required.
My approach is to first divide it into bite size pieces, so I deal with each level separately and combine those into the final result. It's easier to debug this way. I have used two columns to find the text for Level1. You can go back afterwards and combine these two columns if you want.
I have used a table. The advantage is that formulas will be auto filled down a column. Rows can be easily added even in the middle of the table.
I have put the level codes into a named range so that I and O are not included. This can easily be expanded for additional levels by say adding in lower case letters.

Ooo eee ooo ah ah ting tang walla walla bing bang
Ooo eee ooo ah ah ting tang walla walla bing bang