1st sheet is giving me partial results with a vlookup - only gives me the first match to my ref number.
2nd sheet is the source data and contains refs and one or more matches.
NB: This is just a sample of the data, however in my original excel file with 25000 records there can be one or even up to 10 matches.
What I'd like to achieve;
Sheet One; Column B should give all matches for the ref number found in Column A.
It should merge all these matches to fit one cell so that all matches are sorted one under each other with line breaks.
As there is 25000 records to treat I would rather not have to do anything manually!
Am I asking too much?
I've read on the forum that it's best not to merge but I don't know how to get around the fact that I need all the matches per ref no in one cell to reimport into my database.
I am using vlookup to return data from a range that could have the same name in column A.
There are 8 columns Column 1 is Name Column 2 is Sun Column 3 is Mon and so on.
The problem I have is that a name can appear in two places in column1 with relevant information in different columns: Fred appears twice, on Wed he is on a course the rest of the week he is on leave. I need a C to be returned for Wed & A/l for the rest of the week.
2nd File Location: U:My Work FileName: 333.xls Worksheet Names: Pro _Ser1 Ser1 (A1) value= db Ser1 (B1) value=1
What I want is a master spreadsheet to 'suck up' data from all the Ser sheets within all Workbooks and populate the master spreadsheet with Column A =A1 Values from all the Workbook, Worksheets Column B=B1 Values from all the Workbook, Worksheets
So I know vlookup wont work for this, I could do an array index but that wouldn't easily return exactly what I need.
I have two sheets in the same book. One has three columns
Area Room ID A-1 1101 BG11
A-1 1101 BG12 A-2 1102 BG12
The other has a bunch of columns with different information for the ID field
ID INFO INFO INFO ETC...
[Code]....
Sheet1 with the three columns will often have multiple rows for the same ID z with different room/area infor. Sheet2 only has one instance of each ID.
What I would like is something, either macro or in a straight formula that will merge both of these. I'd like to add the Area and Room columns to the second sheet (or merge everything into a third sheet) while keeping all the other info and having repeating lines if the ID shows up more than once.
I have a sheet with Product IDs and amounts. A Product ID may occur more than once. I want to sum all amounts for all occurrences of each Product ID. (see attached file)
I have written the following code, but as you can see, it is returning an error, so I've done something wrong. I'm also getting a Compile error: "For Each control variable must be Variant or Object"
Function GetTotal(PO_ProdID As Range, LURng As Range) ' ' Function to Lookup Concatenated PO # & ProdID ' & Return the Total of all dollar amount listed ' Dim ce As String, tot As Long tot = 0 For Each ce In LURng tot = tot + ce.Offset(0, 1).Value + ce.Offset(0, 2).Value Next ce End Function
I have a workbook that I use to calculate sales by agents. In the workbook I have the following sheets:
Monthly_Report - Where the results are return to the from end user team_ref_sheet - Where team / manager details are added / updated data_sheet - Where my raw data is added
On my team_ref_sheet I have created 4 columns that have been renamed using name manager as follows:
On my "Monthly_Report" sheet I have created a table where I want m data to be displayed to the front end user. The table starts in column C8.
In column D10 through to all the other rows I would like my sheet to reference to my "team_ref_sheet" and return all agent names in the range, up until it reaches a blank row.
I have a series of daily data in columns sorted by date (see attached .xls).
I am trying to create a table that extracts the value on the last day of a given month, and of the first day of that month, for each of the months in the data series.
I have been trying to get lookups and match functions to work but to no avail.
I'm trying to use this function which was posted as an answer (looking up a single value and returning multiple results, concatenating those results in one cell):
Function getfiles(DRng As Range, LURng As Range) For Each ce In LURng If ce.Value = DRng Then holder = holder & ce.Offset(0, 1).Value & ", " End If Next ce getfiles = Left(holder, Len(holder) - 2) End Function
What is "ce" here? Auto Merged Post;additional info:
the original question was posted by jwhite68, Feb 27th 2007, "Return Multiple Values From Lookup To Single Cell"
i have this file that has about 12000 rows of numbers (a individual number can appear more than one). i'm trying to use a lookup to find if that number appears and if it does then bring back the amount next to the number.. however because the vlookup sees that the number more than one in the list it will bring back #N/A every time. Is there a way to get around this??
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
I am trying to do a two way lookup with multlple results. In the example attached I want to know the names of the people who were in Boston on 01/02/09. I have tried a number of index, match formulas to no avail.....
I have been searching all day about this topic and while there are many "solutions" none of them fits my criteria and I can't figure out how to tailor it.
I am using a very basic data sheet to populate a purchase order. I want to be able to search a style and have all the data (color, units and price) automatically fill in upon entering the style number, I have been using vlookup but this only works if there is one color per style.
I have found different lookup functions that give the value +1 when the data is sorted but it returns the value even if it doens't fit the search criteria, I only want to return the second value if it correlates to the initial lookup
I am trying to find a function that will scan down a column to find a particular criteria. I would like all the cells that meet this criteria to have their entire row pulled through to a new sheet.
For Example, In the attached spreadsheet, I would like everyone in sheet 1 to have their details pulled through to sheet 2 if they are in class 1, sheet 3 if they are in class 2, and sheet 4 if they are in class 3.
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
I have some data arranged in columns/rows as follows:
Location Name --------- ------ United States Sarah Buchannan United States Walter Smith France Phil Barney Italy Anna Wilson Germany Philip Watson France Neil Anderson
I want to have some function in my spreadsheet that will neatly present the names of the people at a given location in a separate part of the worksheet
e.g. a display of all people in France would have something like:
France ------- Phil Barney Neil Anderson
Ideally I would like to populate cells rows in a different column with the multiple results of the lookup ("persons at a given location") and that change in location for any individual would result in the list of persons at a given location being automatically updated.
Did alot of digging around and managed to construct a UDF that would display the results of the lookup in a single cell - it is however not very readable. But, I found out that a cell based UDF is not allowed to populate other cells other that the one where the function is entered!
I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.
An offset sumproduct series of functions provides me the total score percentage for the top level categories. There are 5-6 in total and can easily be charted in a radar.
The second step is to maintain the 'dynamic aspect' (being able to delete rows) and lookup the high-level category, and the subsection and return the score for the individual question. This way I can create a chart for each of the 5 top level categories and show the survey score for each answer in the category. Note each question has a maximum score of 5 and are scored 1-5.
I have a few errors looking up values and percentages coming back as 500% with the addition of new columns (in red in attached). The first sheet is error free, the second sheet is the 'build'.
I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula: =IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]], SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]] ='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.
I have this formula below it will return multiple results is it possilbe to modified it that it will return multiple results but ignore duplicates. I would like it to be a stand alone formula no helper cells or helper columns.
I'm working on an invoice generator, and I want to add a "lookup by phone number" feature. I've got an invoice database with invoice info stored horizontally for past invoices. On a separate sheet, I want to be able to type in the phone number in range ("O11:Q11") and have a list of invoice numbers and dates matching that phone number appear in Columns R and S respectively.
Here is some workbook info:
Invoice generator is in 'phonelookup' Invoice database is in 'invoice database' and the table is called CompInvoices In CompInvoices, Invoice numbers are col1, dates are col2, and phone numbers are col7.
