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


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 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?

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 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

Open & Cross Match Files And Show Differences In Another Worksheet

Jun 24, 2009

I am running some VBA code that works fine, until I specify a filename that contains embedded blanks. Hopefully, the following code and screen captures will demonstrate what is happening. First the code that opens the files ...

View 8 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

Reference Changing Files In VBA

Dec 14, 2007

I have recorded a basic macro that sources data from a number of workbooks.

If I change the filename in the workbook with the source data or move this file to a different folder path the macro does not run without errors.

Does anyone know what vba code I can use to address this problem?

View 6 Replies View Related

How To Recognize Unnecessary Reference Files In The Vbe

Jun 18, 2009

I made an excell document with many many vba codes/procedures. While creating it, I was doing some tests/playing with the references in the visual Basic Editor.

My problem is now I dont know which ones are necessary and which dont. I want to remove any unnecesarry reference. How can I know which ones are necessary for my project to run properly and which dont?

View 8 Replies View Related

Read Strings From 2 Reference Text Files?

May 28, 2014

we have 2 text file one is masters and other one child. we want to read the city from child text and wants to read the country coresposding to the city from master file.

the final output should look like

country city

england Portsmouth

View 2 Replies View Related

Cell Reference - Excel Files Linked To One Master

Aug 1, 2013

I'm working on a project that has a master workbook and multiple files that link to the master. The master workbook calculates values based off of a ton of information: account info, pricing info, quantity info, etc. It is a pretty massive excel file, but that is not where the problem arises.

The files that I am trying to link contain relevant information for specific accounts, including prices. The cells that contain product prices are linked to the master workbook. Example: Destination file, lets call this "Company A", Source file, lets call this "Master". In Company A's spreadsheet there is a column that contains pricing specific to that company. This pricing comes from the Master. Most of the pricing is in column C. What I have been doing is copying the relevant cell in the master and "Past Special, Paste Link" in Company A. I do this for every product in Company A's worksheet.

The goal of this is to automate pricing, so that when there are price changes or account changes, one would only have to update the master and all the separate account worksheets would populate with the correct prices. The method I've been using was working perfectly, until I had to edit the master file. I had to insert and delete a few rows from the master file. I work in excel quite often and link cells alot, so I figured that the linked cells would update to the new format, but they aren't. Ex. If a cell in Company A's worksheet is referencing F46 in the master and I delete row 44, I would like the cell to now reference F45, because that is the cell with the correct information. But instead Company A's worksheet isn't adjusting for the deleted row and is still referencing F46.

The only solutions I have found for this issue are to either have all the linked files open when I am editing the master. But seeing as there are almost 25 files, that doesn't seem very practical. The other solution is to never insert or delete rows, just to add on to the end of the master spreadsheet.

View 5 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 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







Copyrights 2005-15 www.BigResource.com, All rights reserved