Hi. I'm looking to be able to concatenate four items but enable the
user to define the order in which the items are concatenated. The
concatenation will be a 'title' in a log, and users have titled things
manually and differently for a long time, so I would like to make the
programming update I'm trying to make flexible enough to enable users
to continue using their own personal logs.

I currently have two columns, Category and Number. My thought is to
insert an 'Order' column in column A to allow the user to define the
order of concatenation.

For example, user Bob has his four categories order defined as
Catalog#-INV-CMPL-Notes, as shown below.

Order Category Number
1 Catalog# 5100
2 INV 87500
3 CMPL 87000
4 Notes Red (bush)

Title: 5100-87500-87000-Red (bush)

But user Sue has her four catergores order defined as
INV-CMPL-Catago#-Notes, as shown below.

Order Category Number
3 Catalog# 5100
1 INV 87500
2 CMPL 87000
4 Notes Red (bush)

Title: 87500-87000-5100-Red (bush)

What might be the formula I could use in the cell next to Title:?
I've thought I might need some sort of Indirect, but I haven't been
able to think how to apply this. And I haven't been able to find
anything in my search in this group or on the internet. I'd greatly
appreciate any thoughts and assistance. Thanks!

Frank

Hi Frank,

Am Sat, 27 Jun 2015 14:54:47 -0400 schrieb :

Order Category Number
1 Catalog# 5100
2 INV 87500
3 CMPL 87000
4 Notes Red (bush)

Title: 5100-87500-87000-Red (bush)

But user Sue has her four catergores order defined as
INV-CMPL-Catago#-Notes, as shown below.

Order Category Number
3 Catalog# 5100
1 INV 87500
2 CMPL 87000
4 Notes Red (bush)

Title: 87500-87000-5100-Red (bush)

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Concatenate" and download the workbook because macros are disabled
in OneDrive.
I wrote a UDF for your problem.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

That is AMAZING! Yet again, another powerful piece of Excel
functionalilty that I simply wasn't aware of. I understand all of the
UDF except the very last line (Title = Mid(myStr, 2)). I understood
how the UDF was building the concatenation via the loop, and I
expected that result to be Title. How does this last line of code
after the loop fit in?

Honestly, Claus, my mind is a blur with all the possibilities now!
Things I wondered how to do before. I know I still have a LOT to
learn, but this is an exceptional opening to something new for me.
Thank you again for your help!

Best regards,

Frank

On Sat, 27 Jun 2015 21:19:50 +0200, Claus Busch
wrote:

Hi Frank,

Am Sat, 27 Jun 2015 14:54:47 -0400 schrieb :

Order Category Number
1 Catalog# 5100
2 INV 87500
3 CMPL 87000
4 Notes Red (bush)

Title: 5100-87500-87000-Red (bush)

But user Sue has her four catergores order defined as
INV-CMPL-Catago#-Notes, as shown below.

Order Category Number
3 Catalog# 5100
1 INV 87500
2 CMPL 87000
4 Notes Red (bush)

Title: 87500-87000-5100-Red (bush)

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Concatenate" and download the workbook because macros are disabled
in OneDrive.
I wrote a UDF for your problem.


Regards
Claus B.

Hi Frank,

Am Sun, 28 Jun 2015 00:40:56 -0400 schrieb :

That is AMAZING! Yet again, another powerful piece of Excel
functionalilty that I simply wasn't aware of. I understand all of the
UDF except the very last line (Title = Mid(myStr, 2)). I understood
how the UDF was building the concatenation via the loop, and I
expected that result to be Title. How does this last line of code
after the loop fit in?

the string is concatenated with:
myStr = myStr & "-" & _
Evaluate("=VLOOKUP(" & i & "," & myRng.Address & ",3,0)")

This causes that a hyphen is the first character of the string.
Therefore the Title begins with character 2:
Title = Mid(myStr, 2)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Ah, I see. That makes sense. I've run into a (hopefully minor) snag
with implementation, though. There's a blank line between rows 3 and
4, as shown below (blocks off groups of info (not my formatting, but I
have to work with it). Would I just account for a blank cell in A(i)
and have the loop press on, like below? Thanks Claus!

Frank

1 Catalog# 5100
2 INV 87500
3 CMPL 87000

4 Notes Red (bush)


Function Title(myRng As Range) As String
Dim i As Long, myStr As String

For i = 1 To myRng.Rows.Count
If Range("A"&i).value = "" Then Next i

myStr = myStr & "-" & _
Evaluate("=VLOOKUP(" & i & "," & myRng.Address & ",3,0)")
Next
Title = Mid(myStr, 2)
End Function


On Sun, 28 Jun 2015 09:46:59 +0200, Claus Busch
wrote:

Hi Frank,

Am Sun, 28 Jun 2015 00:40:56 -0400 schrieb :

That is AMAZING! Yet again, another powerful piece of Excel
functionalilty that I simply wasn't aware of. I understand all of the
UDF except the very last line (Title = Mid(myStr, 2)). I understood
how the UDF was building the concatenation via the loop, and I
expected that result to be Title. How does this last line of code
after the loop fit in?

the string is concatenated with:
myStr = myStr & "-" & _
Evaluate("=VLOOKUP(" & i & "," & myRng.Address & ",3,0)")

This causes that a hyphen is the first character of the string.
Therefore the Title begins with character 2:
Title = Mid(myStr, 2)


Regards
Claus B.

Hi Frank,

Am Tue, 30 Jun 2015 07:36:24 -0400 schrieb Phrank:

Ah, I see. That makes sense. I've run into a (hopefully minor) snag
with implementation, though. There's a blank line between rows 3 and
4, as shown below (blocks off groups of info (not my formatting, but I
have to work with it). Would I just account for a blank cell in A(i)
and have the loop press on, like below?

if a block always has 4 filled rows you only have to change
myRng.Rows.Count to 4:

Function Title(myRng As Range) As String
Dim i As Long, myStr As String

For i = 1 To 4
myStr = myStr & "-" & _
Evaluate("=VLOOKUP(" & i & "," & myRng.Address & ",3,0)")
Next
Title = Mid(myStr, 2)
End Function

and call the function into the sheet then with:
=Title(A1:C5)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional