Code Bug Fix: Excel lookup function taking only first cell

Original Source Link

I have a personals & holiday table. I want to transfer data to another table. But a personal (emre) has two holiday date. Lookup function taking only first data.

main table

transfer table

Can you try it please? You should enter Ctrl + Shift + Enter to activate the array formula.

 =INDEX(Sayfa1!$B$2:$B$6,MATCH(TRUE,EXACT(A2,Sayfa1!$A$2:$A$6),0))

Useful Link:
https://www.extendoffice.com/documents/excel/2699-excel-vlookup-find-first-2nd-match.html

If it is too complicated for you research xlookup.

https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Tagged : / / /

Code Bug Fix: VLOOKUP to compare two columns

Original Source Link

I am trying to use the result of vlookup and then evaluate the result of another column.
I have the following two tables:

Roster:

   ID  Current Employee
   12  Yes
   13  No 

TableB:

   ID  In Roster & Current Employee
   12  ?
   13  ? 

To make the check for if the employee is in Roster, I can do a simple Vlookup:
=IF(NOT(ISNA(VLOOKUP(A2,Roster!A:A,2,FALSE))),"Yes","No")
How do I check the two columns simultaneously to get the result of “In Roster & Current Employee”?

I find COUNTIFS easier here:

=IF(COUNTIFS(Roster!A:A,A2,Roster!B:B,"Yes")>0,"Yes","No")

Tagged : /

Linux HowTo: Merge rows and join matching values

Original Source Link

I have two sets of data on the first set there is one column that has a list of IDs like below.

enter image description here

I have another set which could have several rows matching that same ID like below

enter image description here

I am trying to get all the ‘Values’ shown in the second worksheet for each ID so I end up with something like

enter image description here

How do I write the equation in the B column to do the cross matching?

There aren’t any Excel functions that return a range of cells. There are two possible other ways I can think of:

  1. Write a VBA macro. You could build the first column using the Remove Duplicates feature. Then the code could loop over the range looking for matching values that match the ID and build a comma separate string.

  2. Use a Pivot Table to do that:

    1. Select the ID, Year and Value range of cells and select Pivot Table on the Insert tab.

    2. Add the ID and Value fields to the Row Labels

    3. Configure Field Settings to No Subtotals and ‘Show Items labels in tabular form’

    4. Get a layout something like this. You might be able to get it to layout in a single row but I couldn’t find the option for that.

pivt

Tagged : / /

Making Game: Merge rows and join matching values

Original Source Link

I have two sets of data on the first set there is one column that has a list of IDs like below.

enter image description here

I have another set which could have several rows matching that same ID like below

enter image description here

I am trying to get all the ‘Values’ shown in the second worksheet for each ID so I end up with something like

enter image description here

How do I write the equation in the B column to do the cross matching?

There aren’t any Excel functions that return a range of cells. There are two possible other ways I can think of:

  1. Write a VBA macro. You could build the first column using the Remove Duplicates feature. Then the code could loop over the range looking for matching values that match the ID and build a comma separate string.

  2. Use a Pivot Table to do that:

    1. Select the ID, Year and Value range of cells and select Pivot Table on the Insert tab.

    2. Add the ID and Value fields to the Row Labels

    3. Configure Field Settings to No Subtotals and ‘Show Items labels in tabular form’

    4. Get a layout something like this. You might be able to get it to layout in a single row but I couldn’t find the option for that.

pivt

Tagged : / /

Code Bug Fix: is it possible to lookup a cell value and populate a drop down box with the results in a separate cell

Original Source Link

I have a google sheet with all current staff information. I have a second sheet for when some piece of data needs changed. Last name, Location, job ect.

on second sheet I am looking to have someone:

  Enter the user's first name in column B, 
  and or Last name in Column C,  

I would like a drop down box generated in column D from the given info to contain all usernames found in the sheet with current information for all staff that have the given first and or last names provided.

Once a username has been selected, I can get all the pertinent data from that user with a vlookup from the sheet containing all staff info. For example if someone typed Scott in column B, I would like a drop down generated in column D with all staff that have a first name of Scott. Likewise if the last name was supplied and of course if both first and last was supplied.

Validation need to be done that’s it,

1) Go to Staff Changes'!B2 and Data Validation> List from range & choose range from 'Staff Info'!B2:B

2) Select cell'Staff Changes'!D2 and Data Validation> List from range & range will be filter formula i.e 'Staffing Changes'N2:N.

Result

result2 result1

Few tweaks in vlookup:
I am not sure if you need username column twice if you don’t need then change your vlookup formula from =vlookup(D2,'Staff Info'!A:K,10,0) to =VLOOKUP(D2,'Staff Info'!$G$1:$K,2,0)

Please provide access to trix in future so that changes & result can be shown in it.

UPDATE:

Use data validation as before & hide filter colum or use seperate sheet for filter formula & use query function in cell A3 =QUERY('Staff Info'!A:J,"select ' ',A,F,' ',' ',G,' ',I,' 'where A='"&A2&"' AND F = '"&B2&"' LABEL ' ''Date',' ''Current Last Name',' ''New Last Name',' ''New Building',' ''New Classification'",1) result wil be something like this
enter image description here

QUERY

Tagged : / /

Code Bug Fix: Range Error when using application.worksheetfunction.Vlookup in VBA excel

Original Source Link

i have the following piece of code:

Function MyLookup(var1 As Variant, range1 As Range, var2 As Integer) As Double
MyLookup = Application.WorksheetFunction.VLookup(var1, range1, var2, False)
End Function

I use this function to calculate some of the parameters in the following function

Function Test(IntMethod As String, CFDate As Date, ValDate As Date, _
              CF As Double, RepFreq As Integer, DiscToDate As String, _
              DiscType As String) As Double
Dim InterpPeriod As Double, PrevDateRate As Double, _
    CurrDateRate As Double, PrevCurvMat As Integer, _
    CurrCurvMat As Integer, rate As Double
Dim Yield_Curves As Range, PrevValDate As Date

Set Yield_Curves = DISC_CFS.Range("Yield_Curves")
PrevValDate = Last_Period(ValDate, CFDate, RepFreq)
'-----------------------------------------------------
InterpPeriod = Application.WorksheetFunction.YearFrac(CFDate, ValDate, 1)
PrevCurvMat = YearsDiff(ValDate, CFDate)
CurrCurvMat = YearsDiff(ValDate, CFDate) + 1
PrevDateRate = MyLookup(PrevValDate, Yield_Curves, 2)
CurrDateRate = MyLookup(ValDate, Yield_Curves, 2)

when i get to MyLookup function, the range is not recognised and it exits the function.
Any hints?

thank you in advance!

I have finally managed to resolve the issue. The error was in one of my lookup variables, which was a date. The date should be converted to long type.

the correct code is the following:

Function MyLookup(var1 As Variant, range1 As Range, var2 As Integer) As Double
MyLookup = Application.WorksheetFunction.VLookup(var1, range1, var2, False)
End Function

And for the main code:

Function Test(IntMethod As String, CFDate As Date, ValDate As Date, _
              CF As Double, RepFreq As Integer, DiscToDate As String, _
              DiscType As String) As Double
Dim InterpPeriod As Double, PrevDateRate As Double, _
    CurrDateRate As Double, PrevCurvMat As Integer, _
    CurrCurvMat As Integer, rate As Double
Dim Yield_Curves As Range, PrevValDate As Date

Set Yield_Curves = DISC_CFS.Range("Yield_Curves")
PrevValDate = Last_Period(ValDate, CFDate, RepFreq)
'-----------------------------------------------------
InterpPeriod = Application.WorksheetFunction.YearFrac(CFDate, ValDate, 1)
PrevCurvMat = YearsDiff(ValDate, CFDate)
CurrCurvMat = YearsDiff(ValDate, CFDate) + 1
PrevDateRate = MyLookup(CLng(PrevValDate), Yield_Curves, 2)
CurrDateRate = MyLookup(CLng(ValDate), Yield_Curves, 2)

thank you for the provided answers and assistance

Tagged : / / / /

Code Bug Fix: Retrieve cells with a specific formatting from other sheets into a new sheet in Excel

Original Source Link

I have a several sheets containing different data of interest. I have highlighted the cells with a specific colour, and it will be helpful to have it shown in a “consolidated” sheet that will fetch the highlighted data from anywhere in the other sheet as long as it has the highlighting.

I would like to show the green interesting cells from sheet2, sheet 3, sheet 4, into column A of Sheet1, based on that specific colour. NB. contents is different, the screenshot is just an example.

I would like to show the green interesting cells from sheet2, sheet 3, sheet 4, into column A of Sheet1, based on that specific colour.

I am a bit confused as if I should use conditional formatting, VLOOKUP, IF, or a mix of all, and I am a bit lost with this.

Tagged : / /

Linux HowTo: Excel matching between combination of columns

Original Source Link

I have the following data on an excel :

Sheet1 :

id  effectiveDate   rateValue   CrTime
20  20130627        0.08        6/28/2013 23:05
20  20130626        0.1         6/28/2013 6:23

Sheet 2:

id  effectiveDate   rateValue   CrTime
20  20130630        0.08        6/30/2013 18:14
20  20130628        0.08        6/28/2013 9:38
20  20130627        0.1         6/27/2013 18:14

I want another 1 column on Sheet 2 which should do the following match – If there is a corresponding match in Sheet 1 for the combo of id+effectiveDate, then output the value.

So expected output on Sheet 2

id  effectiveDate   rateValue   CrTime             Sheet1RateValue   
20  20130630        0.08        6/30/2013 18:14      Absent
20  20130628        0.08        6/28/2013 9:38       0.08        
20  20130627        0.1         6/27/2013 18:14      0.1

If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we’re dealing with numbers.

I assume that the data are in columns A through D and starting at row 2.

=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
    "Absent",
    SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)

Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.

So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That’s the difference you’ll get from a vlookup.

Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.

Tagged : /

Making Game: Excel matching between combination of columns

Original Source Link

I have the following data on an excel :

Sheet1 :

id  effectiveDate   rateValue   CrTime
20  20130627        0.08        6/28/2013 23:05
20  20130626        0.1         6/28/2013 6:23

Sheet 2:

id  effectiveDate   rateValue   CrTime
20  20130630        0.08        6/30/2013 18:14
20  20130628        0.08        6/28/2013 9:38
20  20130627        0.1         6/27/2013 18:14

I want another 1 column on Sheet 2 which should do the following match – If there is a corresponding match in Sheet 1 for the combo of id+effectiveDate, then output the value.

So expected output on Sheet 2

id  effectiveDate   rateValue   CrTime             Sheet1RateValue   
20  20130630        0.08        6/30/2013 18:14      Absent
20  20130628        0.08        6/28/2013 9:38       0.08        
20  20130627        0.1         6/27/2013 18:14      0.1

If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we’re dealing with numbers.

I assume that the data are in columns A through D and starting at row 2.

=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
    "Absent",
    SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)

Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.

So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That’s the difference you’ll get from a vlookup.

Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.

Tagged : /

Code Bug Fix: Performing a vlookup with a dynamic lookup_value

Original Source Link

enter image description hereI would like to perform a vlookup with a dynamic lookup_value. The reason for this is that the actual lookup_value row will be different everytime I apply run my VBA code. Since the Vlookup formula will shift up or down every time ideally, I wuold like to use OFFSET in my VLOOKUP to reference whatever the new row it is on.

Here you can see the vlookup is referencing a cell in a column that is -5 columns away. The table_array is on sheet2:

ActiveCell.Formula = “=VLOOKUP(OFFSET(activecell,0,-5),’BB Lookup’!S:W,5,FALSE)”

Any ideas on how to get this to work?

Tagged : / / /