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

Hi Claus. Unfortunately, that's still returning a #VALUE! error in my
workbook. I initially tried it in my workbook, and I thought it might
be because the values in column 3 are values that are pulled from
another sheet. But I copied/pasted values to get rid of the formulas,
and that didn't help. However, I made the workbook you created look
exactly like my workbook, and it works just fine (I initially got the
#VALUE error, but when I changed the order, it apparently triggered
the macro and it worked.

There's one other thing about this that I forgot to mention. The
reason I am trying to do this is to create flexibility for users to
label their log entries however they like. Some people do use all 4
items, but others only use 2 or 3. The numbers will be in range
C5:C11, so would I need to loop through that range, and then also loop
through i = 1 to 4?

I hope I'm not making this too complicated, but I am absolutely
confident this will be pivotal for user adoption as users already have
logs of 100s and 1000s of entries that they've set up a certain way.
Again, and not ever enough, thank you for your help.

Frank

On Tue, 30 Jun 2015 13:51:52 +0200, Claus Busch
wrote:

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.

Hi again. Not sure the order these responses will come in. Need to
read the other one I sent a little while ago first, though.

I tried this below, and I figured it wouldn't work since myRng is
being passed as a range. I think the concept might be right, but how
would I get the value of each cell in myRng passed through to be
evaluated in j = 1 to 7?

And this is just to hopefully help with he flexibility, but it doesn't
matter if I can't resolve that #VALUE error. But it's part of the
whole.

Thanks!

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

For j = 1 To 7
If myRng.Value = "" Then Next
For i = 1 To 4 ' use this if want to expand range:
myRng.Rows.Count
myStr = myStr & "-" & _
Evaluate("=VLOOKUP(" & i & "," & myRng.Address &
",3,0)")
Next
Next
Title2 = Mid(myStr, 2)
End Function


On Tue, 30 Jun 2015 13:51:52 +0200, Claus Busch
wrote:

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.

Hi Frank,

Am Tue, 30 Jun 2015 21:09:19 -0400 schrieb Phrank:

There's one other thing about this that I forgot to mention. The
reason I am trying to do this is to create flexibility for users to
label their log entries however they like. Some people do use all 4
items, but others only use 2 or 3. The numbers will be in range
C5:C11, so would I need to loop through that range, and then also loop
through i = 1 to 4?

in this case try:

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

With Application
myCnt = .Count(.Index(myRng, 0, 1))
End With

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


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

Hi Frank,

Am Tue, 30 Jun 2015 21:26:19 -0400 schrieb Phrank:

I tried this below, and I figured it wouldn't work since myRng is
being passed as a range. I think the concept might be right, but how
would I get the value of each cell in myRng passed through to be
evaluated in j = 1 to 7?

have another look
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Concatenate"


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

Very interesting. And simple. And it works superbly! I do have a
tendency to make things much more complicated than they usually are.
Thank you VERY much for your help with this!

Best regards,

Frank


On Wed, 1 Jul 2015 21:43:38 +0200, Claus Busch
wrote:

Hi Frank,

Am Tue, 30 Jun 2015 21:26:19 -0400 schrieb Phrank:

I tried this below, and I figured it wouldn't work since myRng is
being passed as a range. I think the concept might be right, but how
would I get the value of each cell in myRng passed through to be
evaluated in j = 1 to 7?

have another look
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Concatenate"


Regards
Claus B.