macro to copy text

marshalxxl

New Member
Joined
Nov 17, 2018
Messages
12
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 ?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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

New Member
Joined
Nov 17, 2018
Messages
12
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

Active Member
Joined
Jan 14, 2014
Messages
303
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

New Member
Joined
Nov 17, 2018
Messages
12

ADVERTISEMENT

A sample would help me a lot thanks in advance :)
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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

New Member
Joined
Nov 17, 2018
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,632
Messages
5,523,994
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top