Concatenate In A Vlookup To Search For A Resource Number
Mar 4, 2010
what i am trying to do is use concatenate in a vlookup to search for a resource number and date, then return another column in the array.
the formula looks like:
=VLOOKUP(CONCATENATE(D7,$H$6),Roster_Allocation,7,FALSE)
but only results in NA.
if i search for the resource number only, i get the correct result.
also, the res# and date are concatenated in the table array. could this be related to the way excel is storing the dates (40241?) even though both concatenated fields look the same?
i have also tried adding a new coumn which has the res# and dates concatenated as the lookup value but still all NA.
View 9 Replies
ADVERTISEMENT
Sep 4, 2009
I WANT it to search all of column A for the highest number in the range (which will change daily to a new high number above the highest currently), and return the value in column 4 (column D) that corresponds to that value from column A. This is what I have, but it is not working, it is giving me the number (1) in Cell A800 (which is the lowest valued number in my range. The numbers range in value from 1 to 762 with none repeating (they are sequential). The formula I have is :
=+VLOOKUP(MAX(A76:A2000),A76:I2000,4)
View 2 Replies
View Related
May 30, 2014
In Column A, anytime a specific "text" appears, I want to concatenate the information in the row, and place it into worksheet Data-list under a specific heading.
[URL]
The formula I would use (not vba) is =IF(A3="FN",B3,""), then I just copy it all the way down. However there are 27K cells, which is why I want a VBA Code
View 8 Replies
View Related
Sep 16, 2009
I'm using a userform to report on set of worksheets. Two of the functions are:
- reporting on the number of contacts with a client
- displaying the details of all contacts with selected client in one textbox.
I can't work out how to concatenate the text of all the contacts relating to one client in a text box (txtContactHist) on the userform.
Using the countIf function I'm trapping zero matches.
The worksheet containing the contacts has the following data columns:
Col A - Customer ID
Col B - Detail of contact
Col C- Date of Contact
Columns B and C contain the data I want to capture
Column A (the customer ID) is the column that is searched for a match ( via the userform text box (txtLic.Value))
To work out the total number of contacts, I use this bit of ...
View 6 Replies
View Related
Feb 11, 2010
I have 2 workbookts: 1) Product; and 2) Buyers.
1) In the Products worksheet, every column has data about the books' description like:
A | B | C | D | E
Serial | Desc. | Author | Pubd. | year
Column A contains serial no. of the books, ordered from 1,2,3,4,5,6,7 to .....so on...
Columns B,C,D and E, have other details of the books.
2) In the Buyers worksheet, there are two columns;
A | B
Serial | Buyer Info
These are the details people who bought the particular book order by the serial no. of the book purchased.
Required
I need to make a new column in the first worksheet i.e. Product, say F, which I want to read all the buyers' info of that purchase the book with that serial no. and concatenate them together in the same cell separated by a comma.
View 3 Replies
View Related
Jul 15, 2012
What i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.
I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.
I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).
Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)
View 9 Replies
View Related
Mar 28, 2007
Trying to do Vlookup with If but dont know how , I can do the concatenate function and then Vlookup as in Sheet 3 but that is too much of load to deliver in quick time specially i have pull data in dynamic and continuous update.
View 14 Replies
View Related
Jan 31, 2014
I would like to Vlookup an association of data in a matrix where not all information is available. e.g:
Vlookup of following data (concatenate):
A1&B1&C1&D1
A2&B2&C2&D2
A3&B3&C3&D3
but in the mapping I would have a wild card (*) in certain columns (not always the same column)
The concatenate would be for exemple:
A1&*&C1&D1
A2&B2&*&D2
*&B3&C3&*
I know it works on the other way around (Vlookup of concatenate with wilrdcards) but I can not have wildcards in my source data.
View 7 Replies
View Related
Jun 20, 2014
[Code] ......
Trying to get columns 10, 11 & 13 to join so it says "Mr Noddy Bigshoes" (Salutation, Forename & Surname).
View 7 Replies
View Related
Apr 13, 2012
I am trying to use a Vlookup and concatenate 3 cells from Sheet B onto one cell in Sheet A. I have managed to get it to work, but the leading Zero's are being dropped. I have formatted the cells on Sheet B using custom format and "00000" but they are not coming through on Sheet A.
Here is the formula I am using
=CONCATENATE(VLOOKUP($L$1,[EFT_Exceptions_20120413.xls]EFT!$A:$BE,10,FALSE))&"-"&(VLOOKUP($L$1,
[EFT_Exceptions_20120413.xls]EFT!$A:$BE,9,FALSE))&"-"&(VLOOKUP($L$1,
[EFT_Exceptions_20120413.xls]EFT!$A:$BE,11,FALSE))
View 4 Replies
View Related
Jul 13, 2006
I have drop down fields using a vlookup to grab the corresponding data in a colums next it which is being concatenated into a one big cell. The problem is a set of other columns that I need to pull data from but its dependant to a previous column. In the attachment you see which ever region is selected the following column data is grabbed and the same goe for title. Now when the location is selected the info in its column should be selected that corresponds to the row that the specific title is on. example
if selected: North America>Secretary>Texas
results: A Crazy Mix->;typical,Notepad, pen,square dance
View 7 Replies
View Related
May 8, 2014
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
View 3 Replies
View Related
Mar 20, 2009
I want to Vlook a value from a cell in another workbook then before I get the formula result I would like it to concatenate the result with another cell.
I have attached an example
AlEXAMPLE.xls
View 2 Replies
View Related
Jan 21, 2008
In the past the Manpower Company has put out a yearly spreadsheet allowing an employer to keep track of employee time off. They are not making one this year. Looking at last years sheet, all the calculations are locked. Has anyone seen this sheet in the past and know how it works? You can put in something like v8 in a dated cell and excel subtracts 8 hours from a yearly vacation total. The v in front of the 8 stands for vacation and you can also put in a p for personal time etc..
View 9 Replies
View Related
Aug 5, 2009
i am getting the error run-time error '-2146697211 *800c0005)'': the system cannot locate the resource specified
when running the following:
my guess is because sometimes the internet connection doesnt always work, or the page loads too slow, but 'on error resume next' doesnt work because the line takes awhile to execute and then excel turns off
i believe i saw somewhere here (mrexcel) how to give specific commands a limited time to run. i dont rememeber how to do this though, or any other workaround ...
View 9 Replies
View Related
Jun 10, 2014
The 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.
View 6 Replies
View Related
Feb 7, 2013
I want to write a formula that will sum the hours in a table based on the name of a resource, a task name, and the month it was entered. So my table looks something like this:
Name
Task
Month
Hours
Person 1
Meetings
201201
2
Person 2
Misc
201202
1.5
There are additional columns, and it has over 80,000 rows...
For example, I thought that the formula to sum the hours for Person 1 if the task is meetings and the month is january would be:
SUMPRODUCT((A1:AX="Person 1")*(B1:BX="Meetings)*(C1:CX=201201),D1:DX)
I also tried SUMPRODUCT(--(A1:AX="Person 1"),--(B1:BX="Meetings),--(C1:CX=201201),D1:DX)
View 9 Replies
View Related
Jul 30, 2013
I am trying to create a resource capacity planning per project and I have some issues with some functions. Here is what I did :
1/ I have a workbook for each project with 4 sheets : "Project", "Pivot", "Project Identification", "Capacity"
2/ On the Project sheet I have the volume of man days for resources (rows, can be multiple according to the resource skill) per project phases (columns, always Gate 1 to 6).
3/ On the second sheet I have the pivot table of the first sheet which sum up the volume of man days for each resources per project phases.
4/ On the third sheet I have a project identification table with the columns Date, Week Number, Duration (Weeks), Phase (Gate 1 to Gate 6, so 6 rows). I required the user to enter the date of each Gate, the week number and duration are calculated.
5/ On the last sheet I have my capacity planning with:
- the number of columns is always 52 corresponding to a full year, but can start in the middle of the year, for example start week 30 to end week 29.
- the first row is the weeks numbers, the first cell is the week number of the Gate 1 date, the following cells are the previous cell +1 ( fx=IF(B1>51;1;B1+1) )
- the second row is the phase we are in at that week ( fx=VLOOKUP(B1;'Project Identification'!$B$2:$D$8;3;TRUE) )
- the following rows are the man days for each resources (1 row per resource), this is only the volume of man days for the phase identified in the sheet "Project" divided per the number of weeks for this phase. ( fx=IF(Pivot!$A2="";"";IF(C$2="";"";HLOOKUP(B$2;Pivot!$B$1:$G$4;ROW()-1;FALSE)/VLOOKUP(B$1;'Project Identification'!$B$2:$C$8;2;TRUE))) )
This is working ok if the project execution is within a year (for example from week 20 to week 41). But if the project is over 2 years (for example from week 41 year n to week 12 year n+1) it does not work at year changing.
For example if I have Gate 3 on 19/08/2013, Gate 4 on 30/11/2013, Gate 5 on 29/01/2014 and Gate 6 on 12/02/2014 then on the "Capacity" sheet for week 34 I will have Gate 3 but on week 35 it will switch directly to Gate 6 up to week 52 and then at week 1 I will have "#N/A".
View 3 Replies
View Related
May 31, 2006
I'm using Excel 2002.
I have one workbook with data linked to another CSV file (It's about 40000rows). When I open the workbook, "THis workbook contains one or more links that cannot be updated." message appears and asks me to open csv file if I wanna to update (although I set full path for links in cells). I wonder if there's any way to update link without opening csv file? Or Excel can not update link without openning the resource file?
View 3 Replies
View Related
Oct 14, 2008
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
View 3 Replies
View Related
Feb 6, 2010
I need VBA that will make those two columns:
1 A
1 B
2 C
2 D
2 E
2 F
2 G
3 H
3 I
3 J
into those two columns:
1 A,B
2 C,D,E,F,G
3 H,I,J
View 6 Replies
View Related
Mar 25, 2014
Resouce Capacity Management .xlsx
How do I make my Pivot Table count/Sum the Threshold of each resource within department is within our 80% to 120% threshold?
View 1 Replies
View Related
Sep 15, 2014
I have two columns - A and B. In column A there are blank cells and filled cells. There is a blank cell and after it - a variable number of filled cells, then a blank cell again and a variable number of filled cells. I am trying to concatenate the filled cells from column A into a single cell in column B, which is corresponding to the blank cell in column A. Values should be comma delimited.
For example: A2-blank; A3-27; A4-63; A5-blank; A6-31; A7-blank .. and in column B should be B2-27,63; B3-blank;B4-blank; B5-31,B6-blanc and etc.
View 5 Replies
View Related
Jul 4, 2009
Example is in the attch: Book11.xls. This formula works but if there is no number 4 then I don't want comma after 1, or 2, or 3, How avoid it:
results
1,2,3,4
1,2,
1,2,3,
3,4
1,
4
2,3,
(red , need to be removed)
View 5 Replies
View Related
Jun 6, 2007
I need to cocatenate anything between and including column 4 and col (unkown until macro is run)
is there a way to do this?
Basically need to add all the cells from 4th column to whatever col is. Example if col = 12
then I need columns 4 to 11 all rows together
View 9 Replies
View Related
Sep 20, 2013
I have a sheet where some data is entered in A1 to C1 and i use concatenate to string them together. The problem is in cell C1, where I used cell format to customize the number of zeros in front of the number.
EG:
A1: Photos
B1: 2013
C1: 00001
the concatenate result is Photos2013\1, instead of the desired result of Photos2013\0001.
View 3 Replies
View Related
Sep 19, 2006
How do I get my number in one cell to show all possible combinations of it in other cells. (e.g. cell B4 = 123, now i need cells C4:H4 to display all 6 combinations of the number as follows: 123, 132, 213, 231, 312, 321). Attached is a sample file.
View 3 Replies
View Related
Jun 26, 2013
I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?
Here's an example
A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5
I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:
A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5
Each column is sorted by the numeric value, but has the name label from column 1 attached to it.
View 1 Replies
View Related
Mar 7, 2009
I have spent the last week searching this forum to find the solutions to my various problems, I'm in mental meltdown
I don't think that I can explain here what it is I need so I have attached a sample workbook/spreadsheets.
I have NO experience of Excel and have had a little help developing the attachment, my assistant has now gone AWOL
View 10 Replies
View Related
Mar 31, 2008
I am working on a spread sheet that searches for an employee's clock number, pulls the data from a particular week and displays it all on one page. I have 6 different tables of different stats, 4 weeks each and I would like to pull all of this data together. I have successfully created a page that when you plug in an employee's number, all of thier stats apear from all 6 data sets for the last 4 weeks. I do this by using ....
View 9 Replies
View Related
