Cross Reference Worksheets

Apr 22, 2006

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?

ADVERTISEMENT

How To Cross Reference Two Lists

Apr 4, 2014

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.

View 4 Replies View Related

Formula To Cross Reference

Mar 4, 2009

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.

View 3 Replies View Related

Cross Reference Table

Jan 26, 2010

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.

View 4 Replies View Related

Cross Reference Cells

Jul 29, 2008

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.

View 9 Replies View Related

Cross Reference Maybe Intersection

Feb 4, 2010

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?

View 10 Replies View Related

Cross Reference Numbers

Jan 21, 2009

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.

View 9 Replies View Related

Cross Reference Two Files

Dec 11, 2006

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.

View 9 Replies View Related

How To Create A Cross Reference Database

Sep 18, 2013

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?

View 1 Replies View Related

Cross Reference Values Amongst Columns

May 22, 2009

I have these data arranged in three columns as follows:

ABC
91183
112198
***3181
54201
***5***
***6***
***7***
***8***

(where *** stay for blank cells)

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;

View 9 Replies View Related

Create Parts Cross Reference Table?

Jan 29, 2014

create parts cross reference table?

View 1 Replies View Related

Cross Reference Multiple Columns And Copy

Apr 12, 2009

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.

View 10 Replies View Related

VLOOKUP - To Create A Cross Reference Tool

Mar 24, 2008

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.

View 11 Replies View Related

Cross Reference Matching (or Not) Text Strings

Nov 16, 2008

What formula will check a text string value in a column against a master list of text strings and advises whether or not the text string appears.

View 9 Replies View Related

Cross Referencing Between Two Worksheets

Mar 5, 2014

I 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".

View 1 Replies View Related

Cross Reference Sheet1 And Sheet2 To Find Duplicates?

May 2, 2012

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?

View 5 Replies View Related

Cross Referencing Names On Different Worksheets

Apr 23, 2009

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 Replies View Related

TWo Way Cross Reference Formula Works But Not Across Entire Data Range

May 21, 2009

I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.

View 2 Replies View Related

Excel 2003 :: Cross Reference Multiple Fields For Answer?

Aug 16, 2013

I have a sheet (sheet 1) of employee information for multiple buildings as follows:

Employee Name, Building, Pay Grade, Full Time Equivalent (of full time hours worked)

Anne, Centre A, Anne, Grade 2, 1.0
Bob, Centre E, Bob, Grade 3, 0.50
Carl, Centre H, Carl, Grade 2, 0.60
Dan, Centre B, Dan, Grade 1, 1.0
Emma, Centre A, Emma, Grade 3, 1.0
Fash, Centre A, Fash, Grade 2, 0.40
Gemma, Centre C, Gemma, Grade 2, 1.0
Hanna, Centre B, Hanna, Grade 1, 1.0
etc, etc, etc

From another sheet (sheet 2), I wish to populate the total of "Full Time Equililents" at each grade in each centre, eg:

CENTRE, GRADE 1, GRADE 2, GRADE 3
Centre A, 0 1.4 1.0
Centre B, 2.0 0 0
etc,etc,etc

I'm using Excel 2003 if that makes a difference

View 2 Replies View Related

Copy Rows Between 2 Sheets Based On Cross Reference Match

Apr 22, 2008

I am trying to drop (paste) in new data in a range in sheet 2 and cross reference a column with a table in sheet three and display all rows of matching instances in sheet 1. Example:

Sheet 3 has
a1 b1
amcdap amber connor
apsdap ashley simpson


sheet 2 has
a1 b1 c1 d1 e1
amcdap 300 400 2:00 9:00
apsdap 500 300 4:00 8:00
capdap 200 300 5:00 9:00
dlsdap 400 300 2:00 8:00

I need to return only rows 1 and 2 to sheet 1. I guess ideally I'd like to drop data in sheet 2, click button.

View 5 Replies View Related

Cross Reference (Vlookup) Based On Data Validation List Dropdown?

Jun 19, 2014

Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.

Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.

Example 06.19.14.xlsx‎

View 2 Replies View Related

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.

View 14 Replies View Related

Cell Reference To Other Worksheets

Jan 31, 2009

I basically have the following in my 1st sheet ( [indicates cells] )

[3][10]@[65] - which in laymans terms would mean 3 sets x 10 reps @ 65 percent of your 1 rep maximum.

Moving on, what I would like, is to get cell [3] to reference to 3 cells (without the number 3 showing up) on the 2nd sheet that has the workout program on it.. And adjacently to those cells, I would like [10] to be displayed as the number of reps and [65] to be referenced as the intensity as well.

So in the end it looks like this on the other sheet:

[65%] [10]
[65%] [10]
[65%] [10]

How would I go about doing that? If you don't know the exact answer but could point me in the right direction that would be great!

#2

Is it possible to get a cell in the 2nd sheet to reference 5 merged cells (containing text) in the 1st sheet?

Basically, all I want to do is get the name of an exercise on the 2nd sheet to say the same thing that it does on the 1st sheet. The only problem is that the name of the exercise on the 1st sheet is contained within merged cells.

While I am pretty sure I could break-up the merged cells and just type the name of the exercise in 1 single cell in the 1st sheet to get it to show on the 2nd sheet without a problem, it would make the 1st sheet (which is the overall plan) much harder to read.

View 9 Replies View Related

Can't Reference Cells Across Worksheets

Apr 3, 2007

I've got an error whereby if i open a new workbook and enter the formula "=Sheet2!A1" in cell A1 on sheet1, Excel throws up a circular reference error. If i then try to select sheet2, excel highlights both sheets 1 and sheet 2 (i am not clicking incorrectly here).

Excel then thinks that i've entered the formula into the second worksheet - it dissapears from the 1st and the circular reference makes sense. In other words, it appears that i'm not actually entering a forumla on the hightlighted worksheet.

This was happening in Excel 2003 so i updated to 2007. I'm guessing this is something to do with an incompatibilty with another application but i can't work out what i might have installed. Does anyone have any idea of apps which might cause this type of error?

View 9 Replies View Related

How To Reference Worksheets Inside Workbook

Jul 3, 2013

I do a payroll workbook for my small company. Each sheet inside the workbook is a month. Each sheet totals the monthly labor categories. I would like to keep a "year to date" total on each employee and therefore would have to reference each sheet.

Setting up a formula (or tell me where to look) on how to total worksheets inside a workbook?

Example: the workbook contains a total of 12 sheets, representing the 12 months of the year. Add "cell reference" from sheet 1 to sheet 2 to sheet 3 to reach a total, then when sheet 4 is completed it contains the cumulative totals of all 4 sheets, etc., etc. Where I can look at this "year to date total" and compare to last years "year to date total".

View 2 Replies View Related

Formula To Reference Different Worksheets When Copied/Dragged

Sep 21, 2006

My worksheet "Ledger" has a column of 1-31 days, with row headings of MC/Visa, Amex, Disc & Cash. I also have sheets "1-31" within the same workbook that contain the data I need under each heading. I can get the data for "Amex" in sheet "1" to appear in the first cell underneath "Amex" in the sheet "ledger" by typing the formula ='1'!$B$29.....

View 6 Replies View Related

Dropdowns Used To Reference Information In Various Worksheets To Update Charts?

Apr 23, 2014

I need to create a page (call it a dashboard) in excel whereby drop down fields (doesn't have to be drop downs, just how I imagine it to work at the moment), are used to refer to information in another worksheet, which updates several charts on the dashboard.

So, for example, I have several additonal tabs in the spreadsheet called 'Company 1', 'Company 2' etc, which in each tab contains information (monthly results) in exactly the same layout and format. However, on the front page, I would like to be able to select 'Company 1' (or 'Company 4') and then sub-select 'March 2014' results within that, referring to the data in the 'Company 1' tab. If possible, it would be handy to have this information displaying in a grid on the dashboard, which updates when you change between months and/or company. This could be the source data for the chart.

View 1 Replies View Related

Copying Formulas Between Worksheets While Keeping Reference To Original Worksheet?

Mar 4, 2014

I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.

View 1 Replies View Related

Compare Data On 2 Separate Worksheets And Pull A Reference Cell

Mar 14, 2009

I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions.

The format is: Sheet 2
A/B/
334702/UEMR88QX
334703/UEMR85QX
334704/UEMR81QX
334707/UEMR8JQX

The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesnt exist on the inventory sheet?

Master
A/B/C/D/E/
35/ 465/881676311350/311350/UEMR8ZTU
36/469/881676310722/310722/UEMR8V5V
37/483/881676310924/310924/UEMR8XYU
38/805/881676331096/331096/UEMR8J13

My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month.

I know I can copy the column and past is as a value so I wont lose the numbers as the master changes but getting the data onto that months inventory has been a pain.

View 6 Replies View Related

Formula Copy & Paste: Reference Other Worksheets Within The Workbook, Are Copied Over As Links

Apr 28, 2007

I'm trying to perform a copy & paste operation by hand which to me should be easy in theory. I have a worksheet whose formulas have been corrupted. I have a backup of this sheet- I'd like to copy the backup sheet into the workbook. The problem is that the formulas, which reference other worksheets within the workbook, are copied over as links to the backup workbook instead of the new workbook they are being copied too. This seems simple, but how do I copy the worksheet and the references without having to go through all of the references by hand to only apply to the local book.

View 4 Replies View Related

Cross-referencing Two Spreadsheets

Jun 20, 2014

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.

View 5 Replies View Related

Cross-Validation Rule

Jan 12, 2009

Is there any way to create a cross-validation rule between 2 cells?

For example,

Cell A5 will have one of the following values (AA, BB, or AB) ....

View 11 Replies View Related

Cross-referencing Lookup?

Jul 28, 2008

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.

View 13 Replies View Related

Cross Referencing Cells

Jan 16, 2014

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.

View 4 Replies View Related

Cross Matrix Calculations

Jan 8, 2007

I have 2 tables. One has a listing of items and the work centers that they use and their associated hours. The other has the items and the amount we will produce each month.

What I need is to be able to calculate the number of hours for each item by month and then make a summary so I can see the capacity needed for each item, work center by month.

I have used the VLOOKUP and it works but when I overwrite the second table with new data the calculations get messed up

View 11 Replies View Related

Cross List Of Numbers

Sep 2, 2009

If i have two list of numbers one list in cells A1:a50 and one B1:b50 i want in c1:c50 to see me the same numbers

Also if i have more lists of numbers a1:a50 b1:b50 c1:c50 d1:d50 e1:e50 then in row F show me the numbers that they are in all lists

View 9 Replies View Related

Cross Boxes In 2007

Mar 2, 2010

I am creating checkboxes and linking them to IF statements. I have figured it out all right but I really cannot go through 500 rows and insert checkboxes manually in each row.

View 9 Replies View Related

Cross Referencing Between Sheets

May 2, 2006

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.

View 5 Replies View Related

Cross Platform Formatting

Apr 22, 2007

I'm creating a workbook on a Mac, with Office 2004 for Macintosh. I took the file over to my client's office, who is on a Dell, with Office 2007. When I opened the file on the pc, the formatting (fonts, print area, column width) were all totally different as if they defaulted to a pre-existing setting in Office 2007. How can I get that pc to read my workbook as intended

View 2 Replies View Related

Cross Match Before Calculation

Jul 17, 2007

I have two sets of data and each set consists of two columns as in the example. The first column is the X value and the second set is the Y value. The X values of each set are similar but not the same. I need to multiply the Y values of the two sets with same X value.

View 9 Replies View Related

Lookup & Cross Match

Aug 30, 2007

I am trying to create a project on excel in which there exists four worksheets. I deleted "class 2-4" due to file size but are very similar. My question here is i need a macro to located the number from the "current" column in the Manual Input section in the "Main Page" worksheet. The current number must match column A into the appropriate sheet (depending on class) and then look for the volume in row 5 and then round up to the nearest number. My goal is to input the "proposed" number into the appropriate column and row. This will be done for four different classes. My goal is to develop this table so that i can look for trends.

Eventaully i would like to concatenate multiple proposed values for each cell as the macro will need to add onto what is located in the cell should the value already exist. Code must be a module so it runs when ever data is entered in the current, proposed, and volume cells in the manual input.

View 3 Replies View Related

ADVERTISEMENT