macro to copy text

[marshalxxl]

Hello at work we are using one program that basicly tells us what kind of variants to use for some specific objects for example :

https://imgur.com/a/ug8OcaT

The macro needs to copy from column : AO(V1) to CP(V54) [AO,AP,AQ,AR,AS....ETC] only the last three numbers and then only the last letter and for AO(v1) it will be # cuz it has no letters as you can see in the example:

VAR 246:#,F,N,P,Q

Also some other objects can have more rows of variants as you can see in this example:
https://imgur.com/a/tXZ5Ssp
And now for this object there will be :
Var 162:#,A,B,D,F,G
Var 181:#,A,C,D,F,G
VAR 325:#
VAR 327:A,B,D,E,G,H,N,P,Q,R
Var 920:#,E,F,H

Is there any way to copy that specific text ?

 

[Col Delane]

As your data syntax and business rules are very consistent, have you considered using Excel's powerful Advanced Filter (AF) functionality (on the Data / Sort & Filter ribbon menu) to quickly produce an extract of the rows and columns from the master database where the key field (the Object Name) matches a criterion (selected Object)?

AF will copy the required data (in columns AF & AO:CP) for the matching row/s (the target Object Name) to a separate area/sheet
You could then use Text related functions (Left, Mid, Right, Find, Search, Substitute, etc.) in formulas in a second (Final Output) table to "reduce" the original variant ID in the extract range to the string/s you want.

For the "Copy to another location" option, the Advanced Filter uses three ranges:

1. List range (= the variant database, or just a contiguous range thereof holding the required fields)
2. Crtieria range (= a single column in an out-of-the-way area with a heading ("kurzname"?) exactly matching the Object Name field heading (AF1) in the Variant database + a blank ow in which you enter the target Object Name, like "kb_1039")
3. Copy to range (= a range in an out-of-the-way area with column headings ("kurzname", "V1", "V2", etc.) exactly matching those of the fields in the Variant database holding the variant IDs you seek (AF1, AO1:CP1)

These ranges would be best arranged as dynamic "Defined Names" / "Named Ranges" so re-specifying them is not require, and referencing them in any code is easy.

The only code you need (if any) is to run the AF rather than manually doing so.

 

[marshalxxl]

As your data syntax and business rules are very consistent, have you considered using Excel's powerful Advanced Filter (AF) functionality (on the Data / Sort & Filter ribbon menu) to quickly produce an extract of the rows and columns from the master database where the key field (the Object Name) matches a criterion (selected Object)?

AF will copy the required data (in columns AF & AO:CP) for the matching row/s (the target Object Name) to a separate area/sheet
You could then use Text related functions (Left, Mid, Right, Find, Search, Substitute, etc.) in formulas in a second (Final Output) table to "reduce" the original variant ID in the extract range to the string/s you want.

For the "Copy to another location" option, the Advanced Filter uses three ranges:

1. List range (= the variant database, or just a contiguous range thereof holding the required fields)
2. Crtieria range (= a single column in an out-of-the-way area with a heading ("kurzname"?) exactly matching the Object Name field heading (AF1) in the Variant database + a blank ow in which you enter the target Object Name, like "kb_1039")
3. Copy to range (= a range in an out-of-the-way area with column headings ("kurzname", "V1", "V2", etc.) exactly matching those of the fields in the Variant database holding the variant IDs you seek (AF1, AO1:CP1)

These ranges would be best arranged as dynamic "Defined Names" / "Named Ranges" so re-specifying them is not require, and referencing them in any code is easy.

The only code you need (if any) is to run the AF rather than manually doing so.

I tried this but i can`t manage to make it to work....it is too complex for me

 

[Col Delane]

There are a couple of tricks when the three AF ranges are not all on the same sheet, but other than that it's pretty straight forward (though I must admit I struggled when I first tried to use it many years ago).

I could try to build a proof of concept solution with some sample data from your linked files - but need a few days to do so as I've got a bit on over the weekend.

 

[marshalxxl]

A sample would help me a lot thanks in advance

 

[Col Delane]

A few more questions:

I noticed a pattern in some (but not all) of the alphabetic variant suffixes - they're in alphabetical order and in separate columns (i.e. AP = A, AQ = B, AR = C, etc.)
Q1. Is this pattern meant to be the same across all objects and all variants (noticing that KB_1002 and the last entry for KB_1039 doesn't follow this pattern)?

Q2. What fields are required and what is the format of the ultimate output - just a simple listing like for KB_1039 in your OP?

• Var 162:#,A,B,D,F,G
• Var 181:#,A,C,D,F,G
• VAR 325:#
• VAR 327:A,B,D,E,G,H,N,P,Q,R
• Var 920:#,E,F,H

Q3. Will you be running this string extract for one object at a time or for multiples (if so how many?)

Q4. What's the maximum No. of variants for any one entry (row) for an object?

 

[marshalxxl]

Answer Q1:No the letter dosen`t correspond in alphapetical order whit columns as you can see whit object:kb_1039 at AR(V4) at 181 once you have C and at 920 you have E.
Q2:Yea that simple list is perfect i literally have to write that down for every specific variants and we have thousands.
Q3:For one object at a time the one that i search at AF(Kurzname)
Q4:It dosen`t matter the maximum letter it would be good if when one entire row of variants the next one to be below example:Var 162:#,A,B,D,F,G those are all variants so next row will be below Var 181:#,A,C,D,F,G

• Var 162:#,A,B,D,F,G

• Var 181:#,A,C,D,F,G
• (in general we have maximum of 30 variants for one string for example for other object at Var 162:#,A,B,D,F,G,BA,BB,BC,AF,AG,AH ETC i never seen a object whit more

Thank you so much