I would like a formula that will take the info from 2 cells (not adjacent to each other or in the same row) and then look these up in a table giving the answer in the 3rd column somewhere else on the worksheet.
View 5 RepliesIm using a table so I can sort diffrent catagories and I'm trying to put other functions to the right of the table I noticed as I change the sort on the table is moves the information outside the table as well is there a way to seperate that information or do I just need to move it above my table?
View 2 Replies View RelatedI need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
I have to prepare sheet 2-sales for my job and I cannot find the way to fill the price and sales results depending from the three other inputs required of the table.I belive we will need the functions addif, match, & others but still can't make it. Another tag when there is no result it needs to show "No existe producto".
View 3 Replies View RelatedI'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedI have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
ie:
asset no
description
received by
day
[Code].....
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
What I need is to lookup the matching 2 key IDs (i.e., ISIN and Firm_Quarters) in Sheet2 and then return the variable (i.e., market cap) in Sheet1. I have done one manually highlighted in yellow.
View 4 Replies View RelatedI am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
In the attached sheet, i have data in 2 sheets in the same fromat. Both the sheets are seperate reports.
Coloumn A is the list of Team Ids, Coloumn B is the Ids of people who are assigned in the team. Sheet 2 also have the same details with changes in the people ID.
i am trying to find if the people IDs in report 1 is the same in report 2. i need a formula that will check if the people ID is matching to the same team ID as in report 1.. If its not the same, the formula should be able to show that.
eg :
In report 1, "197595" in coloumn B is against "4011-11341" in Coloumn A.
I need to check if "197595" is against "4011-11341" or some other number in report 2. If "197595" is not against "4011-11341" in report 2, it should show me " Miss match". if "197595" is is against "4011-11341" in report 2 as well, it should show me "match"Project match.xlsx
I have been working on this 2 weeks and have finally come to the conclusion. I am trying to use index/match to find an article number in any 1 of 3 attribute files workbooks. I have been am using Vlookup, but the Master attribute file workbooks DO NOT list the article number in column A (but list them in F) and therefore I have been creating an altered copy every month to place the article numbers in column A so VLOOKUP would work. Index/Match would be a more efficient look up formula as I could link directly to the Master Attribute files without altering the workbooks.
Below is the formula I have been using that looks in 2 attribute files workbooks
=IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)),
IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE)),"",
(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE))),
(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)))
Below are the three (the new formula would incorporate a 3rd attributes workbook "Other" to the search criteria) Index/Match formulas I would like to "Tie" together to find the article number in any of the 3 attribute files workbooks and return column A if the article number is present or nothing if the article number is not present in any of the 3.
=INDEX('C:JDAAttributeFiles[OtherAttributes.xls]OtherAttributes'!A:A,MATCH(A8,
'C:JDAAttributeFiles[OtherAttributes.xls]OtherAttributes'!F:F,0))
=INDEX('C:JDAAttributeFiles[PartAttributes.xls]PartAttributes
[Code] .....
what i am trying to do is look at an entire column, and see if that same date exists in the other Activity tab, if the dates match, then show the activity
here is the formula i am using, =IF($A9=Activity!$A2,Activity!$B2,"No Activity")
my problem is that i am only able to isolate one of the cells,, how do i check the entire column?
in other words, if i select two weeks, those two weeks of activity will show, if i select all weeks, all of the weeks activity will show, right now i can only get one of the weeks
my data looks like the following
1st tab
1/8/2013 - 1/14/2013
1/29/2013 - 2/4/2013
1/22/2013 - 1/28/2013
[Code].....
I have a column on sheet 1 which contains 200 lines with a unique number in each row (account codes). I have another sheet (sheet 2) which contains the same column. sometimes an extra row for an extra account code is added to the col in sheet 1. I need to make sure that the col I have in sheet 2 always has exactly the same as the col in sheet one, where the same account code should never be repeated. I am currently trying to setup a sheet where I keep all the checks and controls.
View 7 Replies View RelatedI'm trying to automate a search and match function between two separate workbooks using VBA and am having some problems. I'm using the following line:
search_results = Application.Match(temp, Range("E1:E900"), 0)
which works fine. now the problem is that when i'm searching for lets say "ABCD" in one of the workbooks, if there is an entry "ABCD " (with a space at the end) it says that the entry doesn't exist. is there any way of correcting it such that it would always neglect the space at the end if it exists? i tried using -1 instead of 0 and it messes up, so i'm not sure what to do.
I have tried a slew of different formulas but I can't seem to get the outcome I am looking for. I need to make a station comparison for an individual project build based off a master list. Sheet 1 has the individual build with column A as a part number, column b as a description and column c as a quantity required and column D is blank. Sheet 2 has a master list of every part we carry formatted as: column a as a part number, column b as a description, column c as a quantity required and column d as a unique station identifier for that part (which is always a number).
What I am trying to do is match the part number on Sheet 1 Column A to Sheet 2 Column A, and when a match is found, take the unique identifer under column D associated with that part number and have it displayed on Sheet 1 Column D.
Sheet 1 will change with each build, but it will always be the same format in columns a,b,c,d. Basically I have a format on sheet 1 for what is needed to build each custom kit (787 kits) and sheet 2 carries every part we offer along with the identifer in column d (always a number value). I want to create a formula I can easily copy to match the value in sheet 1 column a to sheet 2 column a and add the identifier from sheet 2 column D to sheet 1 column D. The average kit is roughly 120+ parts so doing that for each part 787 times will be a nightmare. Also in case this matters, sheet 1 column A will have the parts arranged in a random order based on what is desired for that kit. Sheet 2 column A has the part numbers in order from our lowest part number to the highest number (basically an entire inventory list sorted from lowest to highest).
Example
(For sheet 1, since it doesnt carry formating 11111111 is in column a, nut, flange is in column b, 1.00 is in column c and the output data i want would go into column D)
Sheet 1
A B C D
11111111Nut, Flange 1.00 *Starts blank* -looking for - Output from Sheet 2 Column D (1)
33333333Bolt, Squared 4.00 *Starts blank* -looking for - Output from Sheet 2 Column D (4)
55555555Bolt, Coated 3.00 *Starts blank* -looking for - Output from Sheet 2 Column D (2)
22222222Bolt, Hex 4.00 *Starts blank* -looking for - Output from Sheet 2 Column D (11)
44444444Bolt, Screw Type 2.00 *Starts blank* -looking for - Output from Sheet 2 Column D (3)
Sheet 2
A B C D
11111111Nut, Flange 1.00 1
22222222Bolt, Hex 4.00 11
33333333Bolt, Squared 4.00 4
44444444Bolt, Screw Type 2.00 3
55555555Bolt, Coated 3.00 2
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
View 3 Replies View RelatedI've got an excel worksheet that contains a list of buildings and their associated control valves for water supply (subset of table attached).
I'd like the user to be able to search by building name to display its associated valves and control information. I realize I could use the list function on the worksheet, but would prefer to set it up as front page to the workbook where data is displayed in a nicer format.
I guess my question is if there any way I can set up another worksheet with a drop down box that allows the user to select a building or type in the building name and have its corresponding data display?
Table1 (TeamA)
Table 2 (TeamB)
Name
Date
Name
Date
Table 3 (TeamC)
Table 4 (TeamD)
Name
Date
Name
Date
Given above is a single Sheet in excel with Four Different Tables for each team. I would like to scroll and enter data separately in each Table.
Say for Example Table 1 Team A
Scroll through the rows (1-100)
Enter Data (in any row)
Freeze panes at header
Likewise for all the tables separately. I had tried using split panes, but that is not what i am looking for.
How can i count "how many "A" for period date in table and date out table in this case"
Date inDate outresult Count
05-Mar08-MarA ?
05-Mar09-MarB ?
06-Mar07-MarA ?
07-Mar08-MarB ?
07-Mar08-MarB ?
08-Mar09-MarC ?
05-Mar08-MarA ?
05-Mar08-MarA ?
I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:
1) the personal data should be copied repeatedly.
2) Each Group data should be copied next to personal data.
3) The group's name is mentioned at the top of the datasheet.
I have attached a sample workbook for your kind reference.
I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example
If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
I'm having an issue and I've tried thinking of all the work arounds and haven't come up with a usable method. I built a form that pulls info from a Pipeline table. I wanted to keep track of all the people that make edits on a separate table. Is there a way to take the original info and paste it on to another table by way of a click() and allow the others to make edits on the fly afterwards? This has been bugging me all week.
View 3 Replies View RelatedI'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies View RelatedI am using a pivot table to summarise information with a list of tonnes and sources from civic amenity sites. All was fine until I got to this month to do monthly reports. The pivot table appears to be treating what appears to be exactly the same categories of "CIVIC" and "TRANSFER" differently. By this I mean that although the values in the column appears to be "CIVIC" or "TRANSFER", some get treated differently in the pivot table making it pretty useless. I have made sure that all spellings of the words are the same. there are no spaces before or after the word e.g " CIVIC" but to no avail!
View 4 Replies View Relatedi have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
View 4 Replies View RelatedI have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
100% Bran Nabisco 70 130 10 5 6
AlI-Bran Kellogg 70 260 9 7 5
All-Bran w/Extra Fiber Kellogg 50 140 14 8 0
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?
Code:
Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long
LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row
[Code] ......