Lookup Across Multiple Sheets
May 3, 2013
I have two workbooks of clinical data: one main of 1,400,000 rows and one small of 30,000 rows. What I want to do is compare the entries in small to find out whether they exist in main. All I need is a TRUE/FALSE result.
Eventually I want to run a comparison of :
Unique number, surname, firstname, DoB and gender. (amusing, excelforum will not allow me to type ***). For now I am just trying to figure out how to do it with the unique number.
I have got the hang of using the vlookup function, but have now found that it cannot search across multiple worksheets. Main is spread across 16 worksheets.
View 2 Replies
ADVERTISEMENT
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
View Related
Mar 12, 2008
I have a workbook that has separate tabs for each month. In cell D1 in each sheet is the first day of each month (1/1/08) then in E1 (1/2/08) until the end of the month.
In each sheets - the total for each day is in D30 then E30, etc...depending on which column the last day of the month ended up in.
Then I have a "Report" on using its own worksheet in the same workbook. I would like to be able to type in a date in cell F4 "Report" tab and then for cell A9 "Report" tab to be able to search through JAN D1-?, then FEB D1-?, then MAR D1-?, etc. until it finds the date. Then I would like for it to return the corresponding value found in D30 or E30, etc.
View 9 Replies
View Related
Nov 23, 2006
I'm having trouble looking up text from several worksheets. I've tried hlookup;
=HLOOKUP("H1", '1'!B6:O32,27,FALSE)
but get nowhere when text is in different sheets. I've attached simplified sample... lookup "H1" - holiday1 on sheet1 and return date to "Holiday" sheet. I've tried vlookup across worksheets code, but can't figure out how to customize.
View 2 Replies
View Related
Apr 11, 2007
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.
=IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0)))
It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.
I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.
This is how I have modified the formula to suit me..
=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))
<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
View 9 Replies
View Related
Feb 4, 2014
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.
Result:
ID
AREA
ROOM
INFO
INFO
INFO
[Code].....
I explained this well enough
View 7 Replies
View Related
Mar 18, 2009
I have a file with multiple sheets. Most of the sheets are named for states. I will import a worksheet monthly that has sales data that needs to be copied one line at a time and pasted to the correct company on the sheet that corresponds to the appropriate state. I want to do this by macro.
Here is what my sheets look like...
Arkansas
A B
Comp1 $1000
Comp2 $2000
Louisiana
A B
Comp3 $500
Comp4 $1000
Sheet3 - Import
A B C
Comp1 AR $500
Comp3 LA $500
Comp2 AR $500
What I want is a macro that will read each line in Sheet3 and depending on the state in column B, copy/paste/special/add the value in column C to the amount in column B for the corresponding company on the sheet for that state. In other words I would like to see..
View 9 Replies
View Related
Jan 13, 2008
I came across a code on your website that looks across numerous sheets and stops at the first match. I have a similar need but only i do not want the code to stop when it has found the first match. I want the code to lookup from 1 column in 1 sheet to another workbook with 20 odd sheets, then want it to return a findings report or show all matches.
View 3 Replies
View Related
May 5, 2014
New to using Excel formula's and am trying to create an IF/LOOKUP formula to look at multiple sheets within a workbook and display the information within the 'compare' sheet.The yellow cells are where data will be entered.
What I am trying to achieve:
Type the store numbers on the compare sheet (B2 and D2). For the sheet attached I have put 190 (in B2) and 2012 (in D2)
B2 store shows the predicted and actual values of 190 in columns B & C
D2 store shows the predicted and actual values of 2012 in columns E & F
(No need to worry about variance and difference columns)
So, if I change the store numbers in B2 and D2 to any of the sheet numbers, I want it to display the correct info for that particular store within the compare sheet.
I have attempted a formula, which you can see... I have basically looked at some previous sheets that had IF and LOOKUP on it and tried to replicate that for my sheet, but with no luck.
View 3 Replies
View Related
Feb 17, 2009
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.
the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.
View 4 Replies
View Related
Jul 31, 2014
I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:
1st sheet:
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
I have 180 rows of data like this one in the first sheet
Second sheet(named sheet 1) is not used for this
Third sheet(named sheet 2):
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.
This formula should be in the column following "majoration".
I am using excel 2007 and windows 7.
View 3 Replies
View Related
Apr 7, 2014
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.
View 3 Replies
View Related
Feb 26, 2012
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
View 2 Replies
View Related
Aug 9, 2007
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
View 9 Replies
View Related
Jul 9, 2014
I am trying to use the formula in Sheet3 cell T4 for the summary. If I added an OR to IF statement, the answer is incorrect.
The formula should state like this:
if the result is not in Sheet1 then it should lookup the value in Sheet2. But, there are instance that the value is not only VL. It may be EL or SL or AWOL or VL 0.5 ... If this is not the value, then the result must be blank instead of the plotted time in Sheet2.
View 2 Replies
View Related
Nov 9, 2008
I have a Master Price list on one tab (oddly enough, named Master Price.!) and 4 suppliers on seperate tabs, Supplier A, B C & D. See attached..
Each supplier is capable of supplying various items and they cross each other in what they can offer. On the top of the Master Price is a list of the Suppliers.
Each week I get a price list from each of the Suppliers. What I want to do is dump their list on to their respective sheets in my file and run a macro of sorts that compares their list with the Master list. If it finds a match, place the respective suppleirs list onto the Master list under their respective heading ???
View 2 Replies
View Related
Jul 6, 2007
I'm a first-time caller as they say. I need to extract data from the same row in a sequence of sheets and copy on to a separate sheet using the sheet name as a ' lookup value', and the row across sheets as 'table array'. I want to avoid using macros if possible. This has to be applied to a number of workbooks with different sheet names.
View 3 Replies
View Related
Feb 18, 2014
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
View 3 Replies
View Related
Dec 26, 2009
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.
View 9 Replies
View Related
Nov 10, 2008
I have two worksheets with different data in them. In worksheet "Sheet1"
A B C (?)
1 01 03
2 01 03
3 01 03
4 03 03
5 02 04
...
In worksheeet "Sheet2"
A B C
1 01 03 Mechanical Engineering
2 01 04 Aerospace Engineering
3 02 03 Electrical Engineering
4 02 04 Electronic Engineering
5 03 03 Hospitality
What i would like to do is to fill in the C column in Sheet1 by checking if the value for A and B columns match up with those in Sheet2, then copy the value in C column and put into C column in sheet1. for example if A1 = 01 and B1 = 03, then it is Mechanical Engineering. I was thinking of using VLOOKUP but don't think it'd take "if else" criteria.
View 2 Replies
View Related
Jan 4, 2007
I have a workbook which has a number of activity sheets which calculate a cost against an individual on a number of tasks they may do. There are a number of worksheets and individuals might appear on a number of these sheets depending on tasks. I have a summary sheet which lists the individuals by code (which appears on the task sheets with their name) and I would like to have a function which looks at the individuals code, finds it on each activity sheet and adds all costs up to provide a total. I know I can do a series of Vlookups and SUM Formula these together but wondered if there is a why of combining functions (or another function) which would do this.
View 6 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Feb 19, 2012
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
View 5 Replies
View Related
Jun 3, 2014
I am in the process of creating a Phone Roster for my unit. On Sheet1 is their administrative data (Name, Position, Phone Number, etc.). What I am attempting to do is use a formula to find the persons name for that position and place it in the appropriate position and then their phone number below.
Admin!H:H contains the positions
Admin!B:B contains their name
Admin!N:N contains their phone number
I believe the following is what I need =LOOKUP("Operations NCO",Admin!$H:$H,Admin!$B:$B) and it works, but only for the first person on Sheet 2. After that it is random. If there a different formula I need to use to make this work?
When I get the phone number I used =LOOKUP("Operations CO",Admin!$H:$H,Admin!$N:$N) and the same thing happens. It works for the first person and everything else is incorrect.
A problem is the Admin sheet is listed alphabetically, not by position, so I cant use a specific cell to copy the data. I need the formula to find the position, then find the name and phone number of the person in said position.
View 5 Replies
View Related
Jul 8, 2014
I am looking for a formula that will add some numbers for me on different sheets based on a sorted option that I choose on my Summary tab. sample survey (1).xlsx Here is a sample workbook with what I am looking for.
View 3 Replies
View Related
Feb 4, 2014
What I want to do is to vlookup site IDs in column A of sheet 2, into Sheet 1. The problem is there are two criteria
1- The code finds the roads names of column B. Sheet1 in column.sheet2
2- Considering that the road names are not unique, then within the same roads checks for column C.sheet1 in column D.sheet2
3- When it find the correct value return column A.sheet2 into column A.sheet1
In the case of the picture assuming we are in cell A4.sheet 1, it would go and look for Abbotts road within range (a2:e9) in sheet2 and find several, then go and look for the next criteria which is value 890 (C4.sheet1) and when in finds it in in D4.sheet2 returns A4.sheet2 into A4.sheet1 and it needs to continue doing that until row 2700.
Untitled.jpg
View 6 Replies
View Related
Apr 29, 2013
I have 2 sheets.
Sheet one column A has random dates from 1 jan 2013 to present in order base on fuel purchase dates for our company.
sheet one column F has the purchase price i.e 3.98 gallon
Sheet one column A can have a date entry of lets say 2/4/13 with no data in F because no fuel was purchased just the meter was being read.
Then Sheet one column A can have 2/5/13 where column F has 3.78 where fuel was purchased. (these are 500 gallons tanks that are being refilled as needed)
Sheet 2 row B has just the month formatted as Jan 13, Feb 13, Mar 13, .... you can use B1, B2, B3...ECT for the remaining months
What I am looking for is on sheet 2 in cell C1 a way to look at B1 for the month (i.e Jan 13) look at a range of cells on sheet 1 column A for any dates that fall in the month of Jan, then look for data in corresponding F range and if no data is found then use the last months data (Dec 12)
I can make sure I place a dollor figure in the first row of Jan on the sheet, so by default we can use that if no data exist for Feb.
View 9 Replies
View Related
Feb 20, 2008
I am working on a worksheet. I have the first tab at the bottom and that sheet contains all my data, and the additional tabs moving on the bottom are portions of that data for clarity. What i have on the primary sheet is 5 columns ( Name(A), Total(B), Tax(C), Amount(D), Date(E)) and being the master lists everything. I can add a filter to choose the name i want and naturally that only shows the relevant data for that name. What i want to do on the second tab along the bottom which is only relevant for the name i have chosen, their own sheet if you like is to be able to extract the data from the master sheet on a periodical basis. For example I have the same data that i need to show (including some additional stuff that manually include) and i will need to include Total, Tax, Amount and Date columns. What i want is to be able to have a drop down list in the cells below the Date column so that when i select the date it automatically completes the Total, Tax, Amount information that is shown on the primary sheet. So Total will be A column through to Date in D column and changing the date in column D will populate A to C with the data automatically.
View 9 Replies
View Related
Oct 9, 2008
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
View 3 Replies
View Related
Dec 8, 2013
I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".
=INDEX($A$2:$C$9999,SMALL(IF($A$2:$A$9999=$E2,ROW($A$2:$A$9999)-1,"hh"),COLUMNS($G2:G2)),2)
A
B
C
D
E
F
G
H
I
J
K
L
1
name
score
color
[Code] ..........
View 9 Replies
View Related
