Using Lookup Function To Combine Text From Multiple Rows
Oct 4, 2012
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
I've got an indexmatch that works great =IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))
But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.
I am trying to accomplish this: IF B$20 (which is a dollar amount is = X, then index, match.... IF B$20 is > Y but < Z, then index, match...
I'm trying to write a statement something like: B1 = data. B2=IF(B1/8=0,"",IF(B1/8=INTEGER,B1/8 & "8/8",B1/8+1)). This is meant to identify which relay on a relay board is associated with the given data. For example if data=3, B2 = 1 3/8 meaning the 3rd relay on board 1. If data = 77, B2 = 10 5/8 meaning the 5th relay on board 10. I could do without the 10, but the 5th relay on that board is important to me. In summary, I think my trouble is identifying when the product of B1/8 is an integer. I may also be having trouble combining value and text. Well, there it is. I may very well be going about it all wrong.
with a formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data. see examples below in Column A and the intended results in Column B.
Column A row 1: Joe 1.234, Al 6.89656, row 2: Sam 6.5,
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have a 8500 row sheet with 40 series of data given for each "data set". This data is situated in four columns and ten rows, so that rows 1-10 contain one data set, rows 11-20 contain another data set, etc...
Does anyone have a simple VBE script that can grab this data and format it so that my data sets are arranged as 850 rows and 40 columns instead of the native 8500 rows and 4 columns?
so basically, here's an example of what I have. The spaces in between represent different cells:
Name Corporation Type Group Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3
What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3
In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.
I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.
VB: Sub Patient_Detail() Dim n As Integer 'index of rows to record to Dim i As Integer 'index of column to record from
I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.
Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.
Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...
I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies.
for example
aa bb
ww xx zz
rr
gg hh ii jj
would become
aa bb ww xx zz rr gg hh ii jj
I have over 30000 lines so doing it by hand is not an option.
I have a list of companies and employees, if a company has more than 1 employee listed, I need to combine all instances of the employees into 1 row per company separated by a comma.
combining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.
I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.
I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)
I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and ( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )
I've forgotten all the formulas that I learned during college. And that was for Excel 2003; now I'm using Excel 2007. So, hopefully you experts can help me out.
So here's the problem. I need to set up an invoice in Excel but I don't know what formula to use. I'll first describe my invoice and then I'll tell you the problem.
It's a basic invoice where you have customer's info and product's details and prices. So, when I choose a customer's name from a list in a cell, the address and phone of that customer will automatically appear. I don't have a problem at all with this part. And then there's the product details section where you enter a product type, size, color, price per unit, and total price. The price per unit also depends on the customer entered. Each customer has different pricing and I used a discount system for each customer. We basically have a universal price list for our products, but we have different discount percentage for each customer.
I have two documents, and I need to search data in document 1 based on information given in document 2. On document 1 I have ID number and date range and on document 2 I have ID number and date. Date needs to be in the date range, and ID numbers must be the same.
Please see tables below for the reference.
Document 1 Document 2 ID Start date End Date Data ID Date Data 11 01/01/2009 31/12/2010 Data 1 11 05/05/2011 22 01/01/2010 31/12/2010 Data 2 22 08/08/2010 22 01/01/2011 31/12/2011 Data 3 33 01/01/2012 33 01/01/2012 31/12/2012 Data 4 11 02/02/2009 33 01/01/2010 31/12/2011 Data 3
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results? i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.
the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned .... kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)
here is the formula:
{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}
"data to retrieve" is an array of one column containing the data you want to retrieve "cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted) "table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
This formula works on 1 sheet but we are unable to make it work looking across multiple worksheets? Looking for a match to A3 in column G and wanting to pull the info from column E in the same row.
I am trying to replicate a payslip from a list of data on a worksheet.
The list of data contains the employee name, location they worked, and number of hours.
Each employee will work at multiple locations throughout the month, perhaps 10 or so.
The payslip must contain each location worked along with the relevant data, in a list so to speak.
What function can I use to pull this in? Of course if it were one location I would use vlookups to pull in data. As this only returns the top match I would then need a different formula to pull in the second location in the cell underneath?
I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...
My column headings are as follows... Type Region Contact Phone Email ADD1 ADD2 ADD3
In column A (type) would be the type of contact eg:business/personal/family etc.
So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense! There are over 1000 records i need excel to search through & return matches.
I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.
I have attached a sample spreadsheet which describes my problem.
In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.
I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest.
My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))).
If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula.
I have a worksheet which Sorted in ColA contains Product Id#, ColB contains Vendor, ColC contains Grade# (1 thru 6). Now there may be multiple Vendors (ColB) for same Product # (ColA) but with different Grade# ColC).
I would hope if you can please help me formulate for ColD to pull (list) ALL Vendors for the same Product# (there maybe as many as 6) PROVIDING that the Grade# (ColC) is either 3 or 4.
have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
What I am trying to achieve is using the above can it check
C4:C7 Grouped and named "first" C8:C10 Grouped and named "second" C11:C13 Grouped and named "third"
If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?
I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.
I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.
Last Name First Name Position Building Items
So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.
I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want..
