PDA

View Full Version : Sum Variable types



NWE
02-06-2019, 10:09 AM
Hello,

I have a table that has 5 attributes, with a mixture of different string values. On the 6th attribute, the combination of the first 5 string values will equal a template. For example

A1 A2 A3 A4 A5 A6
a+b+c+d+e= T1
a+b+x+d+e= T2

I am assuming this might be a case function, but I need to replicate this over 10000 rows. I am wondering if anyone has had any experience with this...Thanks!

Paul_Hossler
02-06-2019, 10:16 AM
The CONCAT() worksheet function is fast and can be filled down

23704

Or were you looking for a VBA approach? Try using Join() although I'd probably pull the data into an array first for speed



Sub test()
Dim v As Variant

v = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Trans pose(Range("A1:E1")))
MsgBox Join(v, "")
End Sub

NWE
02-06-2019, 10:27 AM
Ok here is the tricky part..each combination equals a different Template. So for example a template may contain abcde and the next one may contain abcdf. They are different by one letter, but it is two separate templates, did I explain that right?

Paul_Hossler
02-06-2019, 10:58 AM
Ok here is the tricky part..each combination equals a different Template. So for example a template may contain abcde and the next one may contain abcdf. They are different by one letter, but it is two separate templates, did I explain that right?

Yes, I thought I understood, but now I have my doubts. Did you want to take the 5 and map the concatenation to a template (A+B+C+D+E ---> T1) ?

Worksheet or VBA?

You could CONCAT() the 5 into a 'helper' column and VLOOKUP to find a template

23706