Cross Reference Information From One Sheet With Another Sheet
Jul 14, 2009
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
I've got a bit of a quandary here that my novice programming skills can't seem to solve.
Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc).
all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row.
Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on.
so for example, in my second sheet lets say I have the following A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5 and Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
I am trying to create a summary sheet from the matrix to do further analysis. I want to pick out the welds done everyday with weld inches as you will see in the summary sheet. How can summary sheet be automatically updated when I enter the inspection date rather than copying and pasting? I can use vlookup to get the weld dia once I get the weld numbers on that date. I have attached the file.
I have a master sheet, that needs to collect all the data from multiple sheets and then place them order by number.
What I mean i have 3 folders where folder 1, folder 2 and folder 3. if a person takes work 1, then i want master sheet to tell that this number is received so folders 2 and 3 can only take the next available number.
just wanted to know of how to quickly link cells and columns from one cell to the next without using the equals function because i have about 75 individual sheets to do.
so there is a master sheet which includes operators name and # i would like for one person to key in numbers into that master sheet ("ENTRIES")
everytime that individual enters info into that sheet, i would like it to be entered into sheet "#1"
I have one sheet that contains three columns of data. I want to look in another sheet and find if any of the rows in that sheet have the same data found in the first sheet.
I have attached an example sheet of what I need done:
In reference to the example, I am first looking in sheet 2. The information from Column A and C is what I want to find in sheet three if it is there. So for example, A1 and C1 are Joe and 233. I want to then look into sheet 3 and see if any row in sheet 3 has Joe in column A and 233 in Column B. if so I want the value in column B (233) returned into a new sheet.
I need this process to repeat for all rows in sheet 2. The number of rows may change in either sheet.
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
I would like cells to fill themselves in automatically on one sheet, by getting the figure from the cell to the right of them, and seeing where the same figure lies on a different sheet. which ever row this figure lies in will be used to grab a different figure from colum "C" of the same row. This is the figure i would like to be put back on the first sheet.
Im aware that i havent explained this very well but i hope that my attached spread sheet with a couple of notes and highlighted bit will explain it much better.
Thank you very much in advance to anyone who can help me with this!
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
I have an excel workbook that has 2 sheets in it. Sheet 1 is going to be my parts order form. I want to be able to put a value in Cell A2. From there I want to pull data from Sheet two cells B1 and C1 and have it populate on sheet 1 in cells B2 and C2
I am thinking that is was a Vlookup type formula with some sort of If/Then statement in it.
Here is essentially what I thought it looked like minus excel formating
if Sheet 1 cell A2 = 1006 then Sheet 1 cells B2 and C2 = values from Sheet 2 cells B1 and C11
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code: Option Explicit
'Form level variables - used in more than one event Dim intAdult As Integer Dim intStudentSenior As Integer Dim intBalcony As Integer Dim intChild As Integer Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables Dim intCount As Integer Dim i As Integer Dim intAdult As Integer Dim intStudentSenior As Integer
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
To get the content of cells in other sheets of the workbook you refer to the 'Name' of the sheet and write e.g.:
[Code] ....
However, I need to refer to the '(Name)' of the sheet rather than the 'Name', i.e. 'Sheet1' rather than 'Fruit', and I would like to do it in a formula not using VBA.
I have two lists of reference numbers; I need to see if any of the references appear in the other list. I don't want them to be removed, maybe highlighted?
I have made them into one list and used the duplicate conditional formatting but some references appear twice in one list and there is a lot of data so this is not good enough.
formulas i cannot get my head round at all i need e14 to work out how many times cell e1 appears in column h and in column b. so if yes is in e1. it then needs to look down column b and cross refernce it with column h and return the number of yes that appear in column b AND the same row in column h.
First as said in title i have no idea what these kind of tables is called in English and i didnt know how to formulate me when it came to the title (so hint me with a "right" name for this kind of table)
To the point i have alot of these cross reference tables. What i mean is table that have a horizontal titelbar (row) and a vertical titlebar (column) and in the table you have the diffrent stats or whatever that can be within it. Im having a really hard time expaining this so im just uploading a excel file with a little explanation of what im after.
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
I am having to figure out a particularly painful excel formula. What i need is a formula that will count the number of times an issue label in text shows up, but it has to match a certain time.
we can get the excel sheet to round the time to the nearest thirty minutes, so if a call comes in at 3:12pm it rounds it to 3pm. We label the calls that come in with a issue description also like "dvr issue." So i need a formula that will allow me to count the number of instances where if it says 3pm AND dvr issue it will only count it and give me a total in a specified cell. Is that possible?
I have a spreadsheet with Company A's part numbers, then the number for the same exact part from their rival company or competitor. When searching on our website I need for the customer to be able to find the part even if we are out of stock of Company A's part, we can let them know we have company B's part and it is the exact same part. So I am cross referencing the numbers.
In my spreadsheet I have all of company A's parts in column A and their competitor parts in column B. In another column I have cross reference #'s I did with the concatenate function. Where it gets tricky is one part can have 50 or more numbers associated with it. So far, I have been doing the concatenate for each one. I haven't gotten very far as you can imagine. Is there a way I can have it check a column, then when it finds the same exact numbers have it put the numbers from column B all in the same row seperated by a space(my delimiter)? See image at link. I couldn't figure out how to post the spreadsheet. Maybe I am too new to attach files.
[url]
so in the example above, cell E11 has company A's part number, it's own number, then the number above it and the numbers below it. It has every number associated with 74679142.
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?
Basically I have two files. File A) Consists of a billing spreadsheet of approximately 2500 orders. File B) Consists of a Customer spreadsheet with about 2400 customers. What I want to know is there a fast way to confirm based on customer last name and customer number that for each customer in file b that there is a billing entry in file a. I've gone the V-look up way which is effective but time consuming also. File A & B are going to get progressively larger and larger; thereby making the v- lookup not a very timewise solution.
I am trying to create a cross-reference database in excel. I am not sure if I am using the correct terminology, so here is what I need to do:
I have three columns that contain text cells, each with different numbers of rows. Now imagine that cell A1 is related to cell B3 and also to cell C5. There are numerous different combinations similar to this. Is there a way to show this relationship between cells in excel. If so, how?
I need to "report" somewhere and somehow within the spreasheet hopefully by means of some formulas the following data:
1. for column "C" the maximum value within first group of consecutive numbers that are exceeding 180: in this example equal to 201
2. for column "C" the total number of values within first group of consecutive numbers that are exceeding 180: in this example equal to 4
3. for column "B" the first number corresponding to the first group of consecutive numbers that are exceeding 180: in this example equal to 1
4. for column "A" the corresponding number to the maximum value within first group of consecutive numbers in column "C" that are exceeding 180: in this example equal to 5
and most of all I would like to generalise the problem so that to "report" the same data for all cases that are appearing in the above example;
I am working with two sheets, Sheet one has 1 column of information with 12 columns to the right as categories. I want to be able to put an 'X' in any one of the 12 column rows, (category) and have a function that will copy/pastes the information from the main column (column #1, row=any) to the second sheet. I have tried True-False function, argument function, Vlook etc., I still cannot get it to work.
I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.
Is there a way to Cross-Reference two sheets to find the duplicates and highlight them or another way of making it defined? I do not want them deleted, just need to be able to tell who the duplicates are.
Sheet 1 data has
A1 - Email Address B1 - Contact Name
Sheet 2 data has
A1 - Email Address B1 - Contact Name
I want to be able to find the duplicated email address' in sheet 1. And both spreadsheets have over 10,000 email address'. Is this possible?
