Free Microsoft Excel 2013 Quick Reference

Concatenate and vlookup function in vba Results

Hi,

I have the following problem:

I need use the following formula in a vba function: (but keep
failing)and then place the result in a text/prn file.

=CONCATENATE('Inputs'!E2,'Inputs'B2,VLOOKUP('Inputs'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inpu ts'!G2))

Eg.1

E2 = 012 (text field, usually has leading zero)
E2 = 05050 ((text field, usually has leading zero)
Vlookup answer = " " (5 spaces)
F2 = 01012005 (date)
G2 = 2377000 (salary of 23770.00 - formatted)

The result would be:

01205050 010120052377000

Eg.2

E2 = 010 (text field, usually has leading zero)
E2 = 12345(text field, usually has leading zero)
Vlookup answer = " " (6 spaces)
F2 = 05112005 (date)
G2 = 5555000 (salary of 555550.00 - formatted)

The result would be:

01012345 0511200555555000

I then need to do this for all the rows of data in the input sheet,
and then export the data as a text/prn file.

Your help would be much appreciated.

Kind Regards

Flick

Hello all -

I've spent the whole day playing around with VBA to automate a project that I have to undertake weekly at work and which usually eats up several hours. I'm very pleased to say that it now necessitates only seconds. Although everything works, there's one element in the process which I couldn't work out as precisely as I would like and so I've found an interim solution to work around it. Being a perfectionist (or, at least, somebody who gets obsessive about detail) I'd like to do it the proper way.

Because referring to the real scenario would involve lots of jargon, several sheets and distractingly convoluted formulas, I've dumbed down the example I'm going to give. Instead of referring to my work project, I'm going to pretend that I'm interested in finding out what animal the Chinese named the year after when a selection of US presidents took office.

I have two sheets in my workbook: Sheet "Presidents" features the year in column A and the name of the president who took office in that year in column B. In column C I will use a VLOOKUP function to match the year in column A of that row to the entries in column A of sheet 2 ("Chinese") and import from the relevant row of column B, which shows what animal that year was named after.

Here's my dilemma: In my real project, the number of rows in both sheets will vary between weeks. I can, without a problem, identify the relevant ranges in my VBA. If I want to apply a formula in column C of sheet "Presidents" for every row in which there is content in column A, then that's no problem. I simply set a range rowsPresidents and use concatenation to change "ActiveSheet.Range("c2:c100).Formula = ... " or "ActiveSheet.Range("c2:c$24530").Formula = ..." to ActiveSheet.Range("c2:c$" & rowsPresidents).Formula = ... "

The problem is when identifying the range in which to look for matches in the VLOOKUP function I can't work out how to factor in rowsChinese, so have to resort to entering a defined range such as "a2:b400" or "a2:b45120" instead of some version of ("a2:b" & rowsChinese) as I would wish to.

Here's my example:


	VB:
	
 ChineseYearsWhenPresidentsTookOffice() 
     
     'The number of rows that the lists of presidents and Chinese years occupy on their sheets vary over time
     'so we need to set a range in each particular instance.
     'There are headers in sheet "Presidents" so I start my range at A2 rather than A1. There are no headers in sheet
"Chinese" so the range starts at A1.
    rowsPresidents = Sheets("Presidents").Range("a2").CurrentRegion.Rows.Count 
    rowsChineseYears = Sheets("Chinese").Range("a1").CurrentRegion.Rows.Count 
     
    Sheets("Presidents").Select 
     
     'If I wanted to change the contents of column A on sheet "Presidents" to display in italics I could use one of the
following
     'making use of rowsPresidents via concatenation to apply the command to that particular range, even though the number
     'of rows won't necessarily be the same every time.
     
     'With Range("a2:a$" & rowsPresidents)
     '.Font.Italic = True
     'End With
     
     'Range("a2:c$" & rowsPresidents).Select
     'Selection.Font.Italic = True
     
     'Here's where I'm stuck:
     'Let's go to the list of presidents and apply a formula in column C in every row that a president is featured.
     'Fortunately, we've already defined the number of rows and so can insert the final row number in the range via
concatenation
     'using the variable rowsPresidents. So: "ActiveSheet.Range("c2:c$" & rowsPresidents).Formula =" is the start and works
fine.
     
     'I would like to do the same in the VLOOKUP function, telling it to look in columns A and B of sheet "Chinese"
     'from row 1 to the end of the pre-defined range using rowsChineseYears.
     
     ' I can't see how, though, so have to instead enter an arbitrary row number which I hope will cover all the range.
     
    ActiveSheet.Range("c2:c$" & rowsPresidents).Formula = _ 
    "=VLOOKUP(a2,Chinese!a$2:b$400,2,FALSE)" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would welcome people's advice and suggestions on what to do. In my real project the range to search in for the VLOOKUP function can be 9000 rows long in some weeks and 900 in another. The same is true for all the content held on the various other sheets in that workbook, since their content is imported weekly. Ideally I'd like to be able to define at which row the range should end, instead of thinking to myself "Insert a really large number just to be sure that no content is missed out."

I've attached an example spreadsheet to help with visualising what I'm talking about.

Thanks in advance,

Tim

Presidents.and.Chinese.Years.xlsm

Hey guys,

I have the following formula on sheet1 -

'=INDEX(INDIRECT(VLOOKUP(D9,INDIRECT(CONCATENATE("solver_",D10)),2,FALSE)),1,VLOOKUP(D11,solver_columncounter,2,FALSE))

D9, D10 and D11 are all user selected values (drop downs)

The line works perfectly - based on the selections, returns the value of a specific row/column in the named range.

Can someone help me translate this to VBA so I can have this run on the backend? I will be setting the returned value to a variable and using it in solver. I'm trying to avoid having to reference the cell on the page for the variable value.

Thanks for the help!!

Hi All,
Im trying to do a VLOOKUP and return the value if it is true or 0 if it is false which works fine, but the problem is im trying to put the formula in when a macro is run and its having issues with the CONCATENATE function as I have to add string values to Column A. Well at least thats what I think the problem is:

	VB:
	
 ActiveSheet.UsedRange.Rows.Count - 4 
    LeaversYTD = "=IF(ISERROR(VLOOKUP(CONCATENATE(A" & i & "," & " " & " " & "," & "Total" & "),'[Leavers_Report.xls]YTD
Summary LOS'!$B:$D,3,0))=TRUE,0,VLOOKUP(CONCATENATE(A" & i & "," & " " & "," & "Total" & "),'[Leavers_Report.xls]YTD Summary
LOS'!$B:$D,3,0))""" 
Next i 
Cells(i,2).Value= LeaversYTD 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
ok This returns the value in the formula with:
CONCATENATE(A2, ,Total) when it should read CONCATENATE(A2," ","Total")

so I suppose my question is how do I add " into the formula.

Any help would be appreciated.

Hello
I'm new to non-record VBA, good with Excel. Any help would be appreciated.

The description of the requirement;
The user will be selecting a cell they want named, I intend to set up a button to activate this procedure and give the cell a different name (as opposed to the user naming the cell via the drop down menus, as the name they are trying to apply contains numeric characters, which Excel does not appear to accept in specified names)

So, the button will activate a input box, the user will enter the name they need e.g "R00551".

Problem 1
I wish to map the numeric characters to alpha characters, without the user seeing, so that the name can be created and applied to that cell.
e.g Mapping 1=A, 2=B etc... up to 0=J.
So "R00551" input results in that cell being named "RJJEEA".

The reason I am doing this, is that I need a vlookup function on another sheet to reference this cell's data, and I have no scope for altering the spreadsheets contents or immediate cells around the cell I need the data from. The users will only know the numeric code to name the cell, of which there are thousands, so no pre input-mapping.

Problem 2
Also I wish to include a drop down selection after the first input box, for a further input, allowing selection from a predefined list e.g Var, Profit etc..
This would then be concatenated to the first re-mapped input to also be included in the cell name.
So the user inputs "R00551", and is then asked e.g "Which category" and is given the five category choices of which they select from e.g "Profit", the result would be "RJJEEAProfit" and that would be the name that I need applied to the cell.

Problem 3
I then need that created name to be applied to that cell as a name.

And then the code is finished and back to the user.

Hope that's enough info.
Thanks

I have been searching throughout the site this morning for the answer to this - found a lot of threads, but none that answered my issue, so here goes.

I am using a VBA form to insert a new row containing data into a worksheet. The first step of the code inserts a concatenation of the information into column A, then performs (or attempts to perform) a few Vlookup and MID functions based on the concatenation. The functions work just fine if I do them directly in the worksheet, but not if I try to do them through VBA.

If somebody could please cast an eye over the code and let me know where I am going wrong it would be greatly appreciated. As you can see, there are two different syntax's for the VLOOKUPS, neither of which work.


	VB:
	
 
Private Sub Submit_Click() 
     
    Dim NewRow As Long 
    Range("A7:A8").Select 
    Selection.AutoFilter 
    Selection.AutoFilter Field:=1 
     
    NewRow = ActiveSheet.Range("A812").End(xlUp).Row + 1 
     
    With ActiveSheet 
        Range("A" & NewRow) = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value & ComboBox5.Value & ComboBox4.Value 
        UserForm1.Hide 
         
        Range("B" & NewRow).FormulaR1C1 = "=Mid(RC[-1],10,5)" 
         
        Range("C" & NewRow) = Application.WorksheetFunction.VLookup(Range("A" & NewRow), Sheets("Lookup").Range("N:Q"), 2,
False) 
         
        Range("D" & NewRow).FormulaR1C1 = "=Mid(RC[-3],7,3)" 
         
        Range("E" & NewRow).FormulaR1C1 = "=Vlookup(R[0]C[-4],Lookup!N:Q,3,False" 
         
        Range("F" & NewRow).FormulaR1C1 = "=Mid(RC[-5],15,25)" 
         
    End With 
     
     '    Range("A7:A999").Select
     '    Selection.AutoFilter
     '    Selection.AutoFilter Field:=1, Criteria1:=Left(Range("A3"), 3) & "*", Operator:=xlAnd
     
    MsgBox "Please Re-Filter Rows", vbDefaultButton1, "SGA Template" 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I want to do is have the functions performed by VBA, leaving only the value in the cell.

Cheers!

Paul

I have a total of 30 nested formulas that (ofcourse) dont work in excel.
Can someone help me with the VBA code?
Here is an example of the first 7 nested IFS

(1) IF(MID(A7,8,9)="GIR",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),
(2) IF(MID(A7,8,9)="NL",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),
(3) IF(MID(A7,8,9)="BE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),
(4) IF(MID(A7,8,9)="AT",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
(5) IF(MID(A7,8,9)="DE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
(6) IF(MID(A7,8,9)="PH",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
(7) IF(MID(A7,8,9)="AUBE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

I tried this code but it is not working.

Function LookupRetrieve(pVal As String) As Long

If pVal = "(MID(A7,8,9)="GIR" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="NL" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="BE" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="AT" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="DE" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="PH" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="AU" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

Else
LookupRetrieve = "isingcode missing"
End If

End Function

The formula is a vlookup formula (Index/Match but with multiple criterias such as the use of Mid).
I tried Concatenate but the formula is too long and has a lot of characters and VBA seems better.

Does anyone have an idea please?

(Pardon me in case this post appears muliple times. Google gives me
PCBD error on submitting the post)

Hi,

I have base data in column A, B, C and D extending from row 2 to row
500. The data in coln A and B together constitute an unique ID.

I also have data in Column G and H and these 2 together correspond to
the I formed from Column A and B. I want a formula which can return the
value from Column D for a particulr ID combination in Column G plus H.

Ideally I would concatenate the data in A and B ( = A2&B2) and use this
ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in
this case due to the naure of the data layout (and some other factors)
I cannot afford to create new columns for concatenation.

Hence, I want to know as to how I can perform a 2 way lookup without
doing any VBA coding.

I thought that if I can create an array "on the fly" and pass as
arguments which could form a table_array then my problem could be
solved. Something like

(probably this needs to be entered as an array formula)

J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2500)),3,false)

I know that the above formula is invalid and Union function exists only
in VBA, but I wrote this to just explain as to what I want to do.

Please guide me.

Regards,
Hari
India

PS : I know some amount of VBA and can do the above using it, but I
just want to know if excel function magic can solve this.

What Excel functions changed your life/career?

I have been an observer of this forum but recently joined (because I needed some help!).
And, I was just able to offer my first piece of advice back to the community. Woo Hoo!
The evidence

I have a long way to go in my Excel journey I was just wondering what functions changed people's life in terms of career and outlook on how to use Excel.

IF
Where would I be without If?
Such a great function that allows multiple options in one cell.
Changes the way I processed information

VLOOKUP
I was transferring data manually in one job and my manager at the time and asked how I was doing.
I was saying it was going finr. Although, I was kind of new to the job and was struggling to see how I could do this all in a data especially without mistakes.
He came round and asked how I was doing it. He asked if I knew what the VLOOKUP function was.
I said no. He sat down with me and I was literally shocked, surprised and amazed.
When I show other people who have a similar problem the look of wonder that appears on their face is child-like!
Deffo one of the best functions I think that shows people why Excel can be so powerful.

SUBSTITUTE/TRIM/CONCATENATE/LEFT/RIGHT
I am almost always using the above functions to cleanse my datasets
Often getting them ready to be vlookupped!

INDIRECT
This is such a neat little feature. I must have gone a solid year without ever having come across it.
Sometimes you may have ten different ranges e.g. Admissions to different hospitals over a year separated by months.
You have a formula you want to run and then you want to give the user option to see the different values dependent on what they choose in filter.
But, your formulas are dependent on a range. How do you change the range dyamically?
I'm doing a rubbish job at explaining it but follow this video here http://www.youtube.com/watch?v=L6Ow1wLfw2Q

Anyway, just thought I would ask: What Excel functions changed your life?

I am particularly interested what got you to use VBA as I have tried to learn it but I usually give up and decide let me go with a formula instead.

I have the following vba code and the FuncSpaces variable (always
either 9, 10 or eleven spaces) will not concatenate to the other
strings.

Function FuncSpaces(PayGp)

Dim x As String
Dim NoOfSpace As Variant
Set NoOfSpace = Sheets("EmpInfo").Range("Spaces")
Code = 780

If IsEmpty(ActiveCell) Then
Exit Function
End If

x = Application.VLookup(PayGp, NoOfSpace, 2, False)
FuncSpaces = x

End Function

Sub CreatePrn()

Dim EmpNo, PayGp, Code, Prn, EffDate, Sal As String

Range("A2").Select
EmpNo = ActiveCell.Offset(0, 1)
PayGp = ActiveCell.Offset(0, 4)
Code = 780
EffDate = ActiveCell.Offset(0, 5)
Sal = ActiveCell.Offset(0, 6)

Do
Call FuncSpaces(PayGp)

Prn = (PayGp) & (EmpNo) & (NoOfSpace) & (Code) & (EffDate) & (Sal)

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

I have watched all the variables and when the function runs it
displays in watch window as " " but when the sub concatenates
all the variables the spaces arent included.

Also, how do I place each instance of the concatenation in a different
sheet.

ie i want the final result of eg 00512345 7800101200415500 to be
placed into new worksheet, so I can then export as text file.

Your help is much appreciated.

Flick. x

Hello everyone. I have the following snippet of code that I use to assign a cell in a worksheet a variable from VBA:

Sub Update_Formulas_Data()

    Dim sPath1      As String
    Dim wb1         As Workbook
    Const sFileInp1 As String = "Formulas.xlsm"
    
    sPath1 = fPath & fDate & "_157Support_Summaries"
    
        Set wb1 = Workbooks.Open(sPath1 & sFileInp1)
        Range("AV1").Value = fPriorDate
        Range("AV1").NumberFormat = "dd-mmm-yyyy"
     
End Sub
It works. I need help creating a vlookup formula which will use that cell refernce. First I have to concatenate the cell reference with the suffix "_CDS", and I must do so while mainting the dating format, dd-mmm-yyyy. Thats is where I am currently stuck. After that is done, I believe I should be using the Indirect worksheet function, but I am not sure how that is done. Any help is greatly appreciated.

(Pardon me in case this post appears muliple times. Google gives me
PCBD error on submitting the post)

Hi,

I have base data in column A, B, C and D extending from row 2 to row
500. The data in coln A and B together constitute an unique ID.

I also have data in Column G and H and these 2 together correspond to
the I formed from Column A and B. I want a formula which can return the
value from Column D for a particulr ID combination in Column G plus H.

Ideally I would concatenate the data in A and B ( = A2&B2) and use this
ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in
this case due to the naure of the data layout (and some other factors)
I cannot afford to create new columns for concatenation.

Hence, I want to know as to how I can perform a 2 way lookup without
doing any VBA coding.

I thought that if I can create an array "on the fly" and pass as
arguments which could form a table_array then my problem could be
solved. Something like

(probably this needs to be entered as an array formula)

J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2:D500)),3,false)

I know that the above formula is invalid and Union function exists only
in VBA, but I wrote this to just explain as to what I want to do.

Please guide me.

Regards,
Hari
India

PS : I know some amount of VBA and can do the above using it, but I
just want to know if excel function magic can solve this.

Hi,

I have base data in column A, B, C and D extending from row 2 to row
500. The data in coln A and B together constitute an unique ID.

I also have data in Column G and H and these 2 together correspond to
the I formed from Column A and B. I want a formula which can return the
value from Column D for a particulr ID combination in Column G plus H.

Ideally I would concatenate the data in A and B ( = A2&B2) and use this
ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in
this case due to the naure of the data layout (and some other factors)
I cannot afford to create new columns for concatenation.

Hence, I want to know as to how I can perform a 2 way lookup without
doing any VBA coding.

I thought that if I can create an array "on the fly" and pass as
arguments which could form a table_array then my problem could be
solved. Something like

(probably this needs to be entered as an array formula)

J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2:D500)),3,false)

I know that the above formula is invalid and Union function exists only
in VBA, but I wrote this to just explain as to what I want to do.

Please guide me.

Regards,
Hari
India

PS : I know some amount of VBA and can do the above using it, but I
just want to know if excel function magic can solve this.

Hi excel experts,

I am a newbie to writing vba, so far only really updating/changing code that I’ve recorded using the record macro function. However I’m trying to achieve a result which is beyond my current skills. I’ve tried to describe below as best I can what I’m trying to achieve, any help greatly appreciated, and hopefully I can learn a few things along the way….

Each month I receive a payroll file containing every employee’s pay data (example file attached). Every employee has a cost centre number, e.g. 600 – finance, 610, marketing etc. The cost centre managers have to check the data for the employees they manage, so I need to send them a summary sheet. So I need to . 1) create a new worksheet within the file for each cost centre containing the pay details of the employees within that cost centre, and 2) create a new workbook (saved in a newly created folder) from each of the worksheets so that I can email them to the managers.

I've listed below what it is I'm trying to get the macro to do - hopefully this gives everyone a clear idea of the result I’m trying to achieve…

Stage 1: Create new worksheets within source file:

Apply autofilter to column titles in Row 1 of worksheet Pay_DataLoop through the autofilter on column c (cost centre) for each of the cost centres in named range “cost_centres”, copy/pasting the autofilter result into a new worksheetThe copied data to always be pasted into cell A8 of each new worksheetCopied data to be PasteValuesEach worksheet to be named per the cost centre number being filtered onCell A2 of every worksheet to contain the title “Payroll Data”Cell A4 of every worksheet to contain the cost centre number of the pasted dataCell A6 of every worksheet to contain the cost centre managers name by using vlookup of range “cost_centres” and returning column 3.In each created worksheet, insert a total row at the bottom of each set of pasted data to total each column.Hide columns that total zeroSave workbook (this will then be used as a master file to reference back to)
Stage 2: Create new workbook from each new worksheet:

A new folder to be created within the same file path as the originating workbook called “Pay Extracts”Copy each new worksheet (so excluding worksheets Pay_Data and Cost_Centres) to a new workbookEach new workbook to be saved (and closed) in the newly created “Pay Extracts” folderFilename for each workbook to be a concatenation of columns 1, 2 and 3 within the named range “cost_centres”, e.g in my example file “c:my documentsSep Pay FilesPay Extracts600 Cost Centre 600 Manager 33.xlsx”Each file to be .xlsx
Payroll example 2.xlsx

Many thanks
Alan

I am trying to organize over 5000 rows containing 3 cells (sets) of values.
The data needs to be separated into 12 catagories (groups) and ranked within
each group based on one, or more, of the values of the set.

I have tried various combinations and presently am using 12 rows, each
testing for one of the 12 catagories (groups) and then concatenating those
results to display a unique text display for each. I will then sort on them
to arrange the groups.

For the ranking I plan on using another series of rows testing for the
specific value, assigning an associated value and then sorting them.

They say a picture is worth a 1000 words and in my instance, probably 5 or 6
thousand. So:

A B C D
1 798 623 111 +IF(AND(A1=500,C1A1),"Group1","")

2 121 697 001 Copy of D1

3 065 000 982 Copy of D1

4 721 348 216 Copy of D1

Cells E, F, --> contain the additional conditional tests and concatenations
to comlete the task.

I have NOT managed to complete this task, hence the purpose of this post.

IS there an easier way to do this???

I have briefly looked at VLOOKUP, Pivotal Tables, and even VBA. Rather than
trying to learn all of them, I was hoping for a suggestion from this fourm
that will point me in the right direction.

Thank you for any consideration you give this request,

Best regards,

Chuck

1.I have a data table of US counties 3200 rows and 56 columns.
2.Have a store listing of 500 units
3.I am printing call record forms for use by reps that make calls on the
units.
4.The actual worksheets fill in info on this call record form, triggered by
a store number (lookups data from#2 above, e.g. address, phone#, etc), this
becomes a single page, with space for comments etc, without having to write
in all the contact data. Simply enter the store # and the form is printed
out and ready for use.
5. This call record form is 12 columns wide to contain all the info and to
fit on 8.5x11 sheet of paper.

The question: The new county data I want to include on this sheet is 15-20
columns of data; which is too wide to fit on the call record. Is it possible
to use the camera function to "snap" say columns 25-45 and paste it to the
call sheet to fit in a specified area? (This data would be static and used
only for reference, no math performed.)
Can VBA do this? Obviously, I want to automate this, so that when a new
call record form is printed, this new county data would be on the sheet.

This would be similar to using Vlookup and Concatenate to pull the coulmns
of data...I'm looking for an easier way. Could these Coulmns be Named, and
the use Vlookup to pull the named range as "one piece" so as to fit in the
space allowed?

Appreciate any thoughts.

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. If there is anything you can suggest to speed up the processing I would greatly appreciate it. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long.

Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

Thanks in advance for your help.

I realized the vlookups were not the problem it was the VBA code.

Here is the concatif funtion

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As
Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like COUNTIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function


Morning all (it's morning in the UK, at least) - this is a problem I've come across in the past and worked around, but I wonder if one of the gurus here can tell me what the issue is.

I have a fairly straightforward function to concatenate the results from (effectively) a VLOOKUP on a range where there are multiple matches ...

Function
MultiVLookup(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As
String

Dim rngMatchValue As Range
Dim sFirstAddress As String
Dim sTmpReturn As String

sTmpReturn = ""

With rngLookUpArea
  
  Set rngMatchValue = .Find(vMatchCriteria, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
  
  If Not rngMatchValue Is Nothing Then
  
    sFirstAddress = rngMatchValue.Address
    
    Do
    
      sTmpReturn = sTmpReturn & rngMatchValue.Offset(0, lOffset).Value & sDelimiter
      
      Set rngMatchValue = .FindNext(rngMatchValue)
      
    Loop Until rngMatchValue.Address = sFirstAddress
    
  End If
  
End With

If Len(sTmpReturn) > 0 And Len(sDelimiter) > 0 Then
  sTmpReturn = Left(sTmpReturn, Len(sTmpReturn) - Len(sDelimiter))
End If

MultiVLookup = sTmpReturn
  
End Function
If I call this from a VBA subroutine it works fine, and produces the expected results:

Sub TestCall()

MsgBox MultiVLookup("A", ThisWorkbook.Sheets(1).Range("A1:A3"), 1)

End Sub
If, however, I call it as a UDF it fails. A quick bit of debugging shows that this is because the .FindNext method returns an empty range, which shouldn't be possible, as there are both more matches within the range and, even if there weren't, there must have been at least one match, so it should loop back to that.

Things I've tried:

1. Naming the range explicitly, rather than using a With statement - no difference.
2. Defining another range within the function and setting it to the search range before the first search - no difference.

However, the brainwave I had half-way through typing this post was to replace the .FindNext statement with:

And that does work as a UDF.

So, I guess my question not isn't so much "How do I make this work" as it is "Why the hecky-thump didn't it work with .FindNext". Anybody care to enlighten me?

Thanks.

Hi All:

I have several dynamic named ranges (DNR) that I copy from a datasheet
to a calculation sheet. I am attempting to build a matrix where the
row-headings are the first three characters of the first DNR and the
column headings are the last two characters of the first DNR. The
matrix would then populate using concatenate & vlookup.

Right now it is working using worksheet functions for the mid, concat &
vlookup. However, the number of observations I have is changing enough
that I would like to make it dynamic in VBA.

Thanks for your help.

James

Hello all,

This site has proven to be an invaluable resource, thankyou to everyone who posts both questions and responses. This is the first time I have posted a question although I have had a bit of a look I cant guarantee it hasnt been asked before, if so please point me in the right direction.

I have developed a basic front end for a simple database using the vlookup and index search functions.

Within the front end I have an if function that shows a hyperlink if a search cell is used and a result is obtained

=IF(ISTEXT(Properties!X2), HYPERLINK(CONCATENATE("#", Properties!X2,"hazard"), Properties!Z2), ""))

I also have a combobox that you can select from.

1. Is it possible to have the combobox linked-cell changed to a particular cell (eg. x2) value when someone clicks on the hyperlink cell (only if the hyperlink is active). 2. Does it require VBA or can it be managed using a function?
3. will it update the combobox?

Thanks in advance

Justin