Solved

Excel macro to concatenate dynamically

Posted on 2010-11-11
10
962 Views
Last Modified: 2012-05-10
Dear experts, I have attached and example spreadsheet which has a number of value which are concatenated. The final purpose is to establish a new formula which will end of in a Business Objects measurement.

Column A contains a name and B a vale. The number of names can be anywhere between 2 and 500, as can the value.

I need a dynamic macro which will do the following in it's output (either as text in any cell or in a prompt window where I can copy it);

1) Start with "="
2) Concatenate all data in column C (starting at C2)
3) End with "N/A"
4) End with a number of close brackets ")" , one bracket per line populated in column A:B

So, if I only had two lines in the attached and column D would display:

If([Employee Name] = "Name1";9636;
If([Employee Name] = "Name2";5289;

The Macro would have to create this:

=If([Employee Name] = "Name1";9636;If([Employee Name] = "Name2";5289;"N/A"))

The second part of my question is less important but would be great as well to solve. As indicated the number of entries in column A an B may vary. I would be great to have a macro drag down the formula in column C as far down as A:B are populated.

Many Thanks in advance for your advice!

Target-to-BO-Conversion.xlsx
0
Comment
Question by:hendrkle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 34118371
See if this helps:
Sub x()

Dim r As Long, n As Long, s As String

n = Range("A" & Rows.Count).End(xlUp).Row

Range("C3:C" & n).Formula = "=CONCATENATE(""If([Employee Name] = "","""""",A3,"""""","";"",B3,"";"")"

r = 3

Do Until UCase(Cells(r, 3)) = "N/A"
    s = s & Cells(r, 3)
    r = r + 1
Loop

s = s & "N/A" & WorksheetFunction.Rept(")", r - 3)

Range("A" & n + 2) = "=" & s

End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34118377
Try this. The string gets too long to put in a cell, so I dump it to a text file. (Make sure you use a valid path. i.e. if C:\temp does not exist, create it or use a different folder)

Private Sub CommandButton1_Click()
Dim lastRow, i, fnum As Integer
Dim str, ends, fileName As String

lastRow = Sheet1.UsedRange.Rows.Count

fileName = "C:\temp\out.txt"

fnum = FreeFile()

Open fileName For Output As fnum

Cells(3, 3).AutoFill Range(Cells(3, 3), Cells(lastRow, 3))

str = "="
ends = ""

For i = 3 To lastRow
  str = str & Cells(i, 3)
  ends = ends & ")"
Next

str = str & "N/A" & ends

Print #fnum, str

Close fnum

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34118428
Correction, the formula in C was wrong.
Sub x()

Dim r As Long, n As Long, s As String

n = Range("A" & Rows.Count).End(xlUp).Row

Range("C3:C" & n).Formula = "=CONCATENATE(""If([Employee Name] = "","""""""",A3,"""""""","";"",B3,"";"")"
                       
r = 3

Do Until UCase(Cells(r, 3)) = "N/A"
    s = s & Cells(r, 3)
    r = r + 1
Loop

s = s & "N/A" & WorksheetFunction.Rept(")", r - 3)

Range("A" & n + 2) = "=" & s

End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 34120329
Does this give the correct string?

In the message box it appears cut off, but I think that's a message box limitation - when I copy it to a text editor it looks fine.


=If([Employee Name] = "Name1";9636; If([Employee Name] = "Name2";5289;"N/A")))
=If([Employee Name] = "Name1";9636; If([Employee Name] = "Name2";5289;"N/A"))


Dim strFormula

    arrTargets = Range("C3:C33")
    arrTargets = Application.Transpose(arrTargets)
    
    strFormula = "=" & Join(arrTargets) & Chr(34) &  "N/A" & Chr(34)& String(UBound(arrTargets), ")")
    
    MsgBox strFormula

Open in new window

0
 

Author Comment

by:hendrkle
ID: 34135476
Hi Tommy,

Your solution works great for me, thank you!

I was wondering if it is possible to modify the code to;

1) Prompt the user where he/she wants to save the output
2) Prompt the user after code execution, whether or not they want to open the text file (yes/no)

Thanks!
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34136162
That can be done easily.

I assumed you want the output opened in notepad and that notepad opens without the path.
Private Sub CommandButton1_Click()
Dim lastRow, i, fnum As Integer
Dim str, ends, fileName As String
Dim openText As VbMsgBoxResult

lastRow = Sheet1.UsedRange.Rows.Count

fileName = Application.GetSaveAsFilename(InitialFileName:="concat.txt", _
                                         FileFilter:="Text Files, *.txt", _
                                         Title:="Choose output file")

If fileName = "False" Then 'The user hit cancel
  Exit Sub
End If

fnum = FreeFile()

Open fileName For Output As fnum

Cells(3, 3).AutoFill Range(Cells(3, 3), Cells(lastRow, 3))

str = "="
ends = ""

For i = 3 To lastRow
  str = str & Cells(i, 3)
  ends = ends & ")"
Next

str = str & "N/A" & ends

Print #fnum, str

Close fnum

openText = MsgBox("Open output file?", vbYesNo, "Done!")

If openText = vbYes Then
Shell ("notepad.exe " & fileName)
End If

End Sub

Open in new window

0
 

Author Comment

by:hendrkle
ID: 34136236
Like a charm :)

Just one small thing, is it possible to activate the Notepad window, currently I can see that is open but the active window is still Excel
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34136311
Sorry. I should have seen that.
Change
Shell ("notepad.exe " & fileName)
to
Shell  "notepad.exe " & fileName, vbNormalFocus

You do need to remove the parentheses.
0
 

Author Closing Comment

by:hendrkle
ID: 34136824
Thank you for you complete and fast response!
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34136860
Did you accept the right solution? Please make sure to accept everything that helped as part of the solution, both for the experts and anyone who finds this later.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Auto-Complete lines 4 32
Excel -  lookup two values and return a value 3 30
Vlookup Help 3 30
Excel Conditional formatting - icon sets 5 15
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question