Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.
View 3 RepliesI've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
View 1 Replies View RelatedI am displaying cells in textboxes depending on the results of a listbox selection. I was wondering if there is code for making the textbox names variable (so I could just loop through the offsets and textbox names)
the code below works but I would like to make it more versatile and shorten it.
There are other textboxes on the page.
Sub detailfill() ....
I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.
View 6 Replies View RelatedOn a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.
I have attached the following code to a button on a different sheet.
The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.
Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String
[Code]...
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I m trying to match the values based on the Coulumn B
[IMG][URL]....
;base64,iVBORw0KGgoAAAANSUhEUgAABVYAAALYCAIAAAAYRj5jAAAgAElEQVR4nMy9Z1RU+Z73Oy/uWs9aM
/eZmTtzn5nT3doGoNLOu3KmCEXOQUURUcCAomIgmXOgDYCK5JyhcoQiQ5HNWcE2dNt9uo8dTve
ZM3PCfbF3RYJo95m5rs+qVb0bpMoO8P3s7+/3/zvrwYOL5cCB4dnk5bmTm2tnKCeHYDAnZzA7m2AgK2tg
/347/fv29e/b1793bx
[Code]...
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies View RelatedI am trying to move info from an unformatted sheet to a sheet ready to import into a program. I need to look at the source sheet and if a column heading matches the heading on the destination sheet I need it to move the entire column to the destination sheet.
View 3 Replies View RelatedI'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.
eg: sheet 1
Name
Cell
Region
John
111-2222
[Code] .......
The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.
Copy rows from one Sheet to another based on a separate cell value But specifically, I am trying to copy row values from Columns C through column Z in Worksheet 1 of file POHeader.xlsx to row values Columns N through AK in file POReceiv.xlsx when the (Purchase Order #) values in Column A of each file match.
The reason is behind this is - one file has the unique Purchase Order number as the key without associated parts and the other file has the associated part number as the key with purchase order number attached.
I don't know whether I need to use VBA or if I can just use an index and match function.
I am trying to copy a row based on the value of a cell.
I have two sheets in my workbook and on sheet 1, I have a part number and a description. On sheet 2, I have part numbers again, but this time I the description is broken up into the format I need.
What I am trying to do is have excel search on sheet 2 for the part numbers, then copy the information that corresponds to the part number into the correct column.
I have tried using Vlookup. But if the part number in row 2 on sheet 1 match the one in row 8 on sheet 2, this will copy over the data from row 2 whereas I need row 8.
If this would be more doable using VBA, that is fine by me. I haven't been able to figure out anything in VBA or in excel formulas up to this point.
I'm currently having a hard time creating a formula to verify if the contents of 2 cells match.
Example from Spreadsheet -
Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG
When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".
I am trying to populate Column BB based on data in BA. Explanation: If BA2 is less than 1, BB2 should equal "00-01". This should run until BA is blank. Sample value in BA: 6.68.
I'll paste in my code which is causing a run time error 13 type mismatch error starting with the line in red below. I wonder if I am even close?
Dim m As Long
For m = 2 To Range("BA65536").End(xlUp).Row
Select Case Cells(m, 53).Value
Case Is < 1 'line where error starts
Cells(m, 54) = "00 - 01"
Case 1.1 To 2
Cells(m, 54) = "01 - 02"
Case 2.1 To 3
Cells(m, 54) = "02 - 03"
Case 3.1 To 4
Cells(m, 54) = "03 - 04"
Case 4.1 To 5
Cells(m, 54) = "04 - 05"
Case Else
Cells(m, 54) = ">5"
End Select
Next m
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
I am trying to created a spreadsheet for work where I have created to validation drop down boxes, one each box has been selected i want it to return back with the correct answer in the 3rd column.
below are the 3 colums. i have created a validation for column 1 and 2 but when selected i want the final box to = column 3 ie. >=9, =2
120%
12
>=2
130%
13
>=2
140%
[code].....
I have a list of names in Column B (Starting at B5) with assignments to them in Column A. I want the people who receive the file, to enter their name in B1 exactly as it appears multiple times in sheet. And hope to use conditional formatting to highlight (change the back ground color) of each cell their name appears in.
I've used a number of formulas in the Conditional formatting including "=(ISNUMBER(MATCH($B5:$B100,$B$1,0)))", Countif's and "Not(isnumber)..." but can't find a formula that picks up the whole text.
I am trying to create a spreadsheet for an online gift registry. They require that the spreadsheet have the photo's url's in a column. I already have the spreadsheet filled with my data. In the spreadsheet, Column D is filled with unique numbers, some with parenthesis, (ex. 52011, 52011(2), 34132, etc.)
I also have a folder full of images that are similarly formatted as such
"...imagesrand_name_52011.jpg". (They will be moved eventually to a webserver.) Each number in the column may or may not have a corresponding image. And the images may or may not have a corresponding number in the spreadsheet. Is there a way to generate a url automatically in a column that corresponds to the image with the matching number? And if it doesn't, just leave it blank?
I have a sheet with some survey data. the data covers about 4 months. There are about 2200 rows and 8 columns.
The "code" could be in there more than once as the person took the survey multipule times, but all other data is different. How can i pull out the whole row when the code is there more than once.
I want to know all the "codes" with multipule entries that took the survey more than once then trend there scores.
CentercodeRecommendReasonEnvironmentTraining ManagerOverall LHQTR27909415Learning effect4444LHQTR28844652
Center environment2222LHQTR45614375Service5555LHQTR96944292Service2222LHQTR144769543
Center environment4433LHQTR144769543Learning effect3433LHQTR155258791Service3213LHQTR168772563
Center environment2232LHQTR168772563Center environment3332LHQTR168772565
Learning effect4414LHQTR173991905Learning effect4445LHQTR192966385Service5555LHQTR193282534
Qualified teachers3344
in writing a macro that does the following.
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
I have a table with 4 columns where in column 3 I have part numbers and in column 4 I have the number sold. In column 3, the part sold can be listed several times with a different number sold in the column 3. What I need is code to go through the part numbers and return a total number sold for that part in a new column. I have sorted the table by part number, then by Column 1 which is for the date. The new total should go in the last column with the oldest date for that part.
View 2 Replies View RelatedI have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.
I am trying to do this in VBA and I was wondering if there is a way to do this.
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View Relatedi have stores numbers in column a and the sets and cartons in column b but there is gaps between the data i want in column c a list of stores with the set and car displayed next to it, some information in column b is on 2 rows for 1 store please see attached, how i would like to see it on a row is b345 car011119 etc
View 1 Replies View RelatedI have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
I have a UserForm and what I'm trying too do is change the color property of all the labels on the form simultaneously.
View 9 Replies View Related I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm