I have three different sheets, each with a column of names, titled "list1, list2, list3." I also have a fourth list of names on a sheet titled "masterlist." I am wondering if there is any way I could cross check the three lists against the master list to see if any names appear on the master list but do not appear on any of the three lists. Is there any way I can cross reference the master list with each of these lists?
View 2 RepliesI need to set up a system for recording data into a certain way.
I a currently using this formula =IF(sheet1!A1:A20="H","Y","") and it kind of works.
Basically i want it to fill a cell in sheet2 with a "Y" if ony one cell between A1 and A20 from sheet1 contain a H. At the moment it only works if a few of the cell between A1 and A20 contain "H".
Basically I order thrice a week from a certain supplier to fill the shelves of my shop.
They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-
order ref
date
unique product code
description
qty
9262
03/07/2013
622642
Fabric Dye
4
And an invoices spreadsheet with the following columns:-
invoice ref
date
unique product code
description
qty
122551
04/07/2013
622642
Fabric Dye
4
Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.
I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-
1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times.
2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.
I am trying to match a person's name in the A column (A3) with particular course requirements in Columns B through to BB (ie. B2). Not every person will have taken every course. I have the master list of data on another worksheet with 4 columns of data: A-Student Name, B-Course Name, C-Final Mark, D-Additional Notes.
What I would like to do is have Excel find the student name on sheet one as well as each of the courses listed from B-BB and check sheet two (master data) to find if the student name and course name appear on the same line. If it does I want it to return the value in column C (the final Mark).
I thought that I could do this with VLOOKUP but it only works with the left most column correct? I saw something about a VLOOKUP2 but couldn't find that option in Excel 2007.
Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?
If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.
In my application, I am reading a barcode label whose first three digits are a code for the model number (stored in Sheet1, Column C). On sheet 2, this 3 digit code appears in Column C also, and the exact model number for each 3 digit code is stored in Column A. I would like to display the information from Sheet 2 Column A in Sheet 1 Column H, depending on the value of Sheet 1 Column C. (for example, Sheet 1 Column C contains the code 011. On Sheet 2, Code 011 is the code for Model # HTE14WABWW. I would like to copy HTE14WABWW to column H on every row where the row's column C equals 011.
I see several examples of sorting data across sheets but nothing that really answers my specific question.
I have a workbook with two spreadsheets. One contains customer information. The other line items and shipping information. I would like to pull the customer number over to the line items spreadsheet. Both spreadsheets have the order number, but note there is a new line for every item in an order on the line items sheet. How do I accomplish this?
View 2 Replies View RelatedI am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.
View 3 Replies View RelatedI'm working with several sheets, each containing databases of customers that bought different products. I'm trying to do a cross reference so I know which customers has already bought multiple products.
English is not my first language and I might not be able to clearly express what my problem is, so I included a worksheet example.
In the example, you can see that Lea bought an apartment coded with APRT-001 from the first sheet. Lea also bought a condo coded with COND-002. What I'm trying to do is to put a note beside the her entry in the apartment that she has bought COND-002. And vice versa on the COND-002 entry on the second sheet.
What I usually do is combine all the data in one sheet, use Conditional Formatting to point out the duplicate name entries, and work through the whole thing manually. The thing is, there are thousands of names and I need to update the list regularly
I have a list of products that have individual item numbers that share some common sizes and some common materials and are ordered in different quantities. When it comes to re-ordering the raw material to remake these items, I need to know how many of each item in the same type of material and size, need to be made. The attached spreadsheet shows more clearly what I am probably failing to explain! I have tried various combinations of VLOOKUP combined with MATCH but I can’t get anything to work – I guess I’m getting a little punch drunk here having spent the whole morning trying to find a solution!
View 5 Replies View Relatedwhich formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
I am trying to match/cross reference (check by ZIP CODE) two extremely large databases/spreadsheets in MS EXCEL. The vast majority of matches will turn up negative (no match) and only like <5% of all the rows/fields in my 31000+ excel sheet will actually match by zip to the records contained in another sheet (actually this second sheet is a large Seibel Sales database that I exported to Excel for easier access/manipulation) So it would be very tedious and a time waster to manually match the records by hand. My question would be, is there an automated way in Excel (or using any number of Excel addons such as ASAP Utilities etc) that would let the computer do the work for me?
Basically I have two large records, one that I exported from Siebel online to Excel, and the second one is the original work Excel database that I am to work on (ie find matches by zip code and mark them as matched)
Is there a quick way to do this? The power of the computer should really be put to use, but I just don't know /cant figure out how to go about this.
here is the screenshots and explainations if you don't know what I am talking about:
[url]
Just to clarify (I'm not sure I explained it very good)
Imagine two sheets
Sheet #1 <-(Seibel converted to xls) Sheet #2 (xls sheet to be worked on)
12345 --------------------------------54986 (mark as NO MATCH)
84596 --------------------------------25746 (mark as NO MATCH)
24578 --------------------------------12345 (mark as MATCH!)
96328 --------------------------------15789 (mark as NO MATCH)
45897 --------------------------------96328 (mark as MATCH!)
The problem is Sheet #2 is not only five rows, it is like 30000+ rows, and I
will have to do another 30000+ rows soon, so 60000+ to do by hand is very slow indeed.
Also, >95% of all the rows in Sheet #2 will be a NO MATCH with no matching zip codes to ANY of the zip codes of Sheet #1. So less than 5% will actually be a match, but the thing is I have to check ALL of them to actually know which ones match and which don't.
IS there a utility / macro or function in Excel that can quickly let me do this the automated way?
I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...
How would I go about dynamically picking up the sheet name. and referring to it?
For example if I have a column with all the different sheet names in it:
Col AA:
31-10-13
01-11-13
04-11-13
etc!
I have a large dataset that I need to push into a dictionary, into that dictionary I also have to create other dictionaries and this can go on until I fill the entire dataset into the dictionary. The problem is that I do not know beforehand what the key names will be so I need to find a way to get the key names.
I have the following test code:
Code:
Dim player As New Dictionary
player.Add "val1", "hello123"
player.Add "val2", "piet123"
[Code]....
how to get reference the key in a nested dictionary not using the keyname?
I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.
Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.
This is what I have so far
For J = 31 To 49
ResultSht = SheetJ.Name
Sheet1.Activate
ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select
It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?
I am writing a macro to consolidate data from different worksheets in more than one Summary Sheets.
My workbook has quite a number of worksheets, from different department e.g. OPS001, OPS002,OPS003, ADMIN001, ADMIN002, ADMIN003 and so on.
I want data from OPS001, OPS002 and OPS003 to go on one sheet e.g. "Summary-OPS" and data from ADMIN001, ADMIN002 and ADMIN003 to go on the other sheet name "Summary-Admin"
When I am working on Summary-Ops sheet I want to copy data from sheets starting with name "OPS" and so on.
here's a simple discription of what I would like to do:
I have two worksheets open, SHEET1 and SHEET2
In SHEET1 I have numbers 1,2,3...10 running in cells A1.....A10
What I would like to do is add up a certain range of rows in
SHEET1 from target values I place in SHEET2
example:
in SHEET 2:
A1 = 3 ( I insert begining row )
A2 = 5 ( I insert last row )
A3 = equation to add up rows 3 to 5 in SHEET1, ie: A3+A4+A5
What is the equation I should put in A3? This driving me nuts!!
in sheet1 i have a list of names in column B. in the next columns(C to H) i have some numerical data about them. in sheet2 i want to keep only the names and the data in column H. all works fine except... if i insert another line in sheet1 the data in that line is not inserted in sheet2. and if i remove a line in sheet1 i get a ref error in sheet2. what can i do so the new data is inserted correctly?
View 4 Replies View RelatedHere's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
Trying to write a macro that will reference one cell in about sixteen different worksheets and return the value of each of those cells. Is there an easy way to do this?
ie.
For n = 1 To n = 15
Worksheet(n + 1).Cell("A1")
Return A1
I know this isn't even close to the right code but this should give you an idea of what I'm trying to do.
I have 3 worksheets:
Lets call them RED, BLUE AND GREEN
Essentially GREEN pulls Values from BLUE and BLUE pulls Values from RED. Red being the base worksheet from which everything is calculated.
In my GREEN workbook I have the following =MIN('BLUE'!H14,'BLUE'!L14)
And this will work fine.....Until I press a clear Worksheet button that has been implemented on the RED worksheet (button was not implemented by me and I do not wish to edit anything with respect to that button)
When RED is reset the #REF! Error will appear in GREEN. This is fine because it cant find any values From BLUE. However when I do input new values into RED, which in turn updates BLUE which is where GREEN in theory should then be able to pull the results from.... But it just stays with a REF! error.
Is there anyway i can permanently set a CELL to have the following code =MIN('BLUE'!H14,'BLUE'!L14), no matter what happens to other cells?
I'm trying to optimize code by avoiding activating other worksheets, but I'm running into a problem with a match function.
I'm using a workbook called "Template" and then opening another workbook called "DCP" and trying to use the match function to figure out what row data is on. I can get this first line of code to work:
HTML Code:
MatchedRowNumber = WorksheetFunction.Match(Combo, Sheets("Sheet1").Range("A:A"), 0)
However for that to work, I have to activate the other workbook. I want to avoid that and stay within the "Template" workbook. I think I need something like this:
HTML Code:
MatchedRowNumber = WorksheetFunction.Match(Combo, DCP.Sheets(DCPSheet).Range("A:A"), 0)
That one however doesn't work... looks like I have the wrong syntax.
Below is an excerpt for the code in case something is wrong with how I set the variables.
HTML Code:
Sub StockOrderByDCP()
Dim Template As Workbook
Dim DCP As Workbook
Dim MSS As Worksheet
Dim DCPSheet As Worksheet
[Code] ..........
I'm getting an "Invalid or Unqualified Reference" error at the 'division=.cells' line when I try to go through each worksheet and paste some info from there onto a summary sheet. I know there is probably a simple solution that I am oblivious to.
Code:
For Each sheet In ThisWorkbook.Worksheets
i = 1
division = .Cells(2, 1)
[Code].....
I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month...
Kind of lost on how to possibly make this work. I basically need something like:
=location/[workbook.xls]Month(Today())!cell
I'm having trouble calling a defined range within a VLOOKUP function in VBA. If the named range is located on the same sheet within which you are running the macro, everything runs fine and all is well in the world.
However, after I relocated the range to a separate sheet (a 'SourceData' sheet to tidy up the user interface sheet), I was getting the following error message:
Method 'Range' of object '_Worksheet' failed
The name is correctly defined - Range("DaysInYear").Select still picks up the correct selection - it's just the VLOOKUP will no longer function correctly.
Here is part of the macro's
For I = 1 To NumberOfDays
Range("A1").Value = DateAdd("d", -(I - 1), EndDate)
If Application.VLookup(Range("A1"), Range("DaysInYear"), 3, False) = 1 Then
If Application.VLookup(Range("A1"), Range("DaysInYear"), 4, False) = 0 Then
ActualNumber = ActualNumber + 1
End If
End If
Next I
I have two worksheets...let's call them worksheet A and worksheet B. How can I get a list of people who are on worksheet A AND also on worksheet B? I would actually like those names on a separate worksheet.
View 6 Replies View RelatedMy Thread title should have read covert code TO run on all worksheets with same word in Name. I have had a look at some other pieces of code in this forum but they are quite the same as I want to do....I have tried a few things but each time I get an error ...so for sure I am not modifying the code quite right, I ahve been missing something for what I now want it to do. So I wonder if someone could Please review these two pieces of code...they of course are both for running on just one worksheet in a wookbook.
I would like them both be able to run, still as tow separete pieces of code as they are, on all and only worksheets that have in their worksheet name a key word in this case the word "Region" and that those worksheets all in a single workbook but the workbook does have other worksheets in it I dont want the code to run one on - but those worksheets do not have in their worksheet name the word "Region".
Sub test()
Dim r As Range, txt As String, ws1 As Worksheet, i As Long
Set ws1 = Sheets(1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 16 To 21
Again:
For Each r In ws1.Range(ws1.Cells(6, i), ws1.Cells(Rows.Count, i).End(xlUp))
If r.Value <> "" Then
If Not .exists(r.Value) Then
.Add r.Value, Nothing
Else
txt = txt & "," & r.Address(0, 0)
If Len(txt) > 245 Then
ws1.Range(Mid$(txt, 2)).EntireRow.Delete
txt = "": .RemoveAll: Goto Again
End If .............
I have a piece of code from a form command button. its supposed to colect data and put it to appropriate cells.
VB:
FoundColumn = 0
For Each c In Sheet19.Range("A5:A33").Cells
If c.Value = student1.Value Then
'check if date allready exists
For Each cc In Sheet19.Range("c4:nc4").Cells
If Calendar1.Value = cc.Value Then
[Code] .....
I need this for Sheet19 and to 10 more worksheets like Sheet20, Sheet21 etc. I can manually copy paste the code and change the Sheet19 to whatever but it just does not seem the right thing to do. I tried:
VB:
For i = 1 To 6
naming = "Sheet" & i
MsgBox naming.Cells(1, 2).Value
Next i
but that gives an object required error.
I need to create a macro that will create a mailing list from about 300 spread sheets in the same workbook. (I am proud of myself, I found a macro that helped me bring all 300 worksheets in to one workbook).
All worksheets are template style if B3 is company name in one worksheet it it the same on all worksheets. I have 15 cells I need to bring from each spread sheet into a master mailing list.
The big problem is, I only have the next 18 hours to do this and it is taking way too long to do it one at a time.
my formula is as follows: =(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE)). I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.
ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name. So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets. I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup.