Compare Two Very Large Files - Vlookup Is Not Working?

Jul 24, 2014

I'm trying to compare two very large files to find some that are in one AND also in the other. I'm using =VLOOKUP(C2,Parts2!C:D,1,FALSE) and my results are #N/A for all of them. I have formatted both columns as 'General' because there can be letters and numbers in the serial number, I have also tried formatting as text to no avail. I have confirmed no preceding or trailing spaces or other special characters.

I am however, able to locate values in both documents using Ctrl F. I'm totally puzzled because I've always been able to figure out what's causing my Vlookup issues when I have them but this time I'm not able to.

View 14 Replies


Getting Information From A Large Set Of Files

Jul 21, 2006

I'm trying to do is take a 3 by 40 area from each file, add it together into one report. I've been browsing through some other examples, but i can't seem to get any of them to work. Heres the
Sub clamdata()

Dim sht As Worksheet
Dim sFolder As String

Dim count As Long
Dim data(3, 50) As Long
Dim sum1(3, 50) As Long
Dim count2 As Long
Dim vaFileName As Variant

How do I have it select the sheet i want (Eyelet Faults) and then get the data from the ranges I want (B7 to D47), add them, and then output that data to one file?

With this code, it gives me a general error message (and doesn't highlight anything):

Runtime error '91'
object variable or with block variable not set

I originally started with this idea:

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:DataReportsWORsWidget"
Set wbBook = ThisWorkbook
Set wsData = wbBook.Worksheets("Data")
lngRows = wsData.Range("A65536").End(xlUp).Row

View 9 Replies View Related

Formula Not Working On Large Number Of Records (500,000) - 2007

Nov 2, 2009

I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)

I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.

View 9 Replies View Related

Importing Very Large Text Files

Oct 28, 2008

So I am trying to use this VBA:

It´s basically so I can important a very large log file, about 800,000 lines, many of them repeating, I want to import into Excel to better control the data.

I keep trying to import the data, and I cant get it to make me new sheets.

View 3 Replies View Related

Importing Large Text Files

Feb 8, 2006

With reference to Importing large text file. i need to get large text files to automatically split across Excel sheets sheets while trying to Import. Can it be done only through Macros or is there any function in Excel itself.

View 7 Replies View Related

Excel 2013 :: Opening Large (1-3gb) XML Files

Sep 9, 2013

I received a few 1-3gb xml files from the government and I would like to open them in an Excel format. I have tried opening them using Excel 2013 (both as an xml table and as a read only workbook) with a machine running 8gb RAM and a 3.4 ghz i5 processor. I left it running overnight but Excel continues to "not respond". The file is intended to display thousands of line items with around 40-50 columns of data. For previous (and much smaller) similar files, the saved workbook is a manageable file. The only hurdle here is getting these open.

View 5 Replies View Related

Split Large File Into Multiple Smaller Files

Dec 12, 2009

On this forum, a script is provided to break a large Excel file into smaller 500-line files:

Split Worksheet Into Multiple Files By Every Nth Row

I copied the code and pasted it into the VBA editor in Excel 2007, but when I run the macro, it generates an error message:
Runtime error 91: Object variable or With block variable not set.

The line the debugger identifies as the one with the error is this one:
For lLoop = 1 To rLastCell.Row Step 500

Here is the full code from the previous thread: ...

View 9 Replies View Related


Sep 4, 2009

I don't know if this is possible but thought i'd check. Could I put together a look up that pulls through the 5 best performing categories' so say i have 200 categories, just to pull on to a report page the top 5 and their result, and produce a report weekly so if the top 5 change it updates this?

View 2 Replies View Related

Importing Text Files - Large Number Of Rows Blank After Each Import

Apr 5, 2012

I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.

Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?

View 3 Replies View Related

LARGE, VLOOKUP- Set Of Data Covering Cells A8:C71

Jan 25, 2008

I have a set of data covering cells A8:C71

Column A lists locations - there maybe upto 6 entries with the same location.

Column C lists a unique serial number.

The list is sorted by column A (A-Z).

For this example say location LONDON has 3 entries, with serial numbers 120, 125, 128.

In cell F1, i want to display the lowest serial number for LONDON

In cell F3, i want to display the 2nd lowest serial number for LONDON

In cell F5, i want to display the 3rd lowest serial number for LONDON

I have tried the following formula, with no success..


View 9 Replies View Related

Using Vlookup & Indirect To Ref List And Vlookup Files

Sep 17, 2008

I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.

This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.

I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:

View 14 Replies View Related

Compare The 2 Files

May 2, 2007

I have 2 xls file what I want to do to compare the 2 files see attachment

On sheet 2 is the main xls ... what I want to compare sheet 2 to sheet 1 and if it's on sheet 2 and sheet 1 if will post the result to sheet 3 pulling all the data to those colum ie
Employee IdInstrument IdQuantity Br Account
95086 Dec 300 511 2046

what has to match is sheet 2 Emp# and Qty to sheet 1 Employee Id
and Quantity

View 9 Replies View Related

Compare Two Files With The Same ID

Jul 9, 2009

I have one xls sheet with employee’s information, at column A I have ID number -called Mater. I have second xls sheet with update employee’s information, at column A i have the same ID number - called update file. I need help with VBA code that update the Master file according to the update file (The ID at column A is the key between the files)and mark with color the update. The file contain more the 20,000 records and with 10 columns.

View 5 Replies View Related

Compare Formula (For Vertical Range) Not Working

Feb 21, 2014

I am trying to compare vertical range "A" in sheet1 to vertical range "K" in sheet2 if they match to copy both rows and place in sheet3. I have tried the following but it did not work.

Sub Searching()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
' create short references to sheets
' inside the Sheets() use either the tab number or name
Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)

[Code] ......

View 1 Replies View Related

VLOOKUP In Large Data Sets Of More Than 16384 Rows

Mar 27, 2006

I have a problem with VLOOKUP looking in sorted lists of more than 16384 items.
Basically it fails at 16385th item

I have developed a VLOOKUP function that looks up in multiple tabs in
multiple files and returns a value. However I have discovered that it fails
at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
anyone else encounteed this problem? is it just my machine, is there a
solution / bug fix?

If I use FALSE when the item to be found is beyond 16384 the function
returns the 16384th value this is a warning to other users who may not have
noticed as I did not initially.

View 12 Replies View Related

Compare Two Files And Match Id

Nov 12, 2009

I want to compare the colour column in book1 to the colour column in book 2. If I find a match I want to take the id from book1 and port them over to book2. How do I go about doing this?

View 10 Replies View Related

Compare Data In Two Different Files?

Oct 1, 2013

i need to compare the data in two different excel files and ask if the data in column A that contains a registration number is the same in both and if not to flag up any that are missing

Is there a macro or command I could use that will do this

View 2 Replies View Related

Macro To Compare Between The Two Given Files

Dec 17, 2008

i need a macro to do a comparison b/w the two given files ...(book1 n book2) ...
n want the result to be like result file .. (result.xls)

View 6 Replies View Related

Compare 2 Files And Do A Net Send

Sep 18, 2007

Here's my first file, a txt file. It's just 1 line:
5 10

Here's my second file. It's an excel file:
Bob 4 20
Joe 5 10
Bill 6 8

I need something that compare's the first number (5) in File 1 with the first number for each person in File 2. The first line in File 2 is just a heading. If Bob's CIQ is greater than 4, I need to issue a net send that says "Net Send bob Alert! Check the montor" Or if Bob's ASA limit of 20 is higher than the second number in File 1, I need the same message to go out to Bob. Same for Joe and Bill.

This seems like it should be simple but I've struggled with this for over a week.

View 9 Replies View Related

Compare 2 Files & Append

Aug 15, 2008

I am trying to compare 2 excel files (main and monthly) and append the new records in to the main file that will have several more columns than the monthly file. So the Main file will have all the records of previous months, and will be compared against a new monthly file to make sure only new records are added from the monthly file in all the six sheets. The columns are exactly the same in all the sheets. Except for the main file which as mentioned will have several more columns for other details. How can I do this? I have been searching a lot but couldnt find what I exactly was looking for. As an example I have attached 2 files. obviously the real files have much more data. The second part is how to modify the already existing records in the Main file when same record in Monthly file changes. this is later.

View 3 Replies View Related

Working With Muliple Workbooks/xls Files

Jun 20, 2007

in simple terms im trying to cross reference a name list with registers from another wrokbook. so basically the desired result is. i press my button and it loads the second workbook up, it then uses the a name list in the new book and searches for that names maches in the registers (a group of sheets) on the previous workbook then if a match is found it colours the background in green. I am however getting errors e.g. "run time error: '424': Object required"

with this line then highlighted in yellow:
u = ShtNm.Sheets("Sheet1").Cells(1, "C").Value

Sub Macro1()
Dim u As Integer
Workbooks.Open FileName:= _
"S:HR TrainingO2 Operational TrainingRetentions and CollectionsUpgrades
ames 18th june onwards.xls"
ShtNm = ActiveWorkbook.Name...............

View 3 Replies View Related

How To Compare Data From Excel Files

Jun 3, 2013

I am trying to do a comparison of numbers from two excel files (one with data from Google Analytics and one with data from Ad-words).

I would like to compare data in the two files, and when there is a match (of keywords), the data from google analytics excel file should be copied into the correct row in the spreadsheet with data from google ad-words.

Does it make sense? and is it possible?

View 2 Replies View Related

Unknown Characters In Compare Files

Aug 18, 2008

I have two access databases which export results to two excel files. I am trying to compair the two excel files (generated by access databases) to find the common data in the two files. I am using Vlookup function.

The data looks like this
File 1
Serial No Fault
40293 A
40294 B

File 2
Serial No Solution
40293 Procedure 1
40294 Procedure 2

The result should be
Serial No Fault Solution
40293 A Procedure 1

But the column serial no in file 1 has some unknown characters like 40293followed by a small square or a vertical line. The datasource for the access file that generates file 1 is actually a lotus notes database. I think thats what causing this. Is there a solution to clear these spurious characters?

The VLookup function works fine if I manually delete those characters.

View 9 Replies View Related

Compare Data From 3 Workbooks Against 2 Files

Feb 15, 2010

For the past several days I have been attempting to write a macro, which in theory would check the values within a one- sheet workbook (we’ll call it run_list.xls) against the values in two different workbooks (we’ll call these production.xls, development.xls). However after several days I am nowhere closer to solving this problem then I was when I started. I’ve tried different scripts and variations of vlookup, but I have been unsuccessful in tailoring what I have found to meet my needs. So I feel it is time to lie down and scream for a medic.

Here’s some background on the workbooks. The sheets in all three workbooks are set up in the same manner. Cell “A1” contains time/ date, cell “A2” contains a lot#, cells “A3:A99” contain positioning data, and cells “B3:B99” contain serial numbers associated with tubes in the specific positions. The only differences between the workbooks are the sheets in production.xls and development.xls are labeled according to their specific lot number.

What I have been trying to do is to take a value from cell "B3"in run_list.xls and find where that value occurs in column B either of workbooks. When that value was found I wanted to copy cell "A2" from its sheet and paste that value in cell "D3" of run_list.xls. I had also wanted to repeat that those steps for every cell in column B containing a serial #. If a serial number was not found I wanted it to report “Not Found”. There are also times when instead of a serial number a phrase “No Trakmate” is listed. In these cases I had intended to skip these lines.

Also, since production.xls, development.xls are updated frequently their names are also updated with new version numbers ie. Production_v10.xls I had intended to allow the user to choose what files to search in but was unable to figure out how to add a second location.

View 9 Replies View Related

Reconciling Large Amount Of Data When VLookup And Match / Index Not Ideal

Nov 12, 2013

I need a little coaching on big data. I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.

I have 1 Item Code.
I have a around a hundred invoices. (Sample 100)
I have a few hundred different customers. (Sample 100)
I have several hundred lines of billing data. (Sample 650)
I have several thousand lines of production data. (Sample 40,000)
In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.
In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.

Prod System:
30 lines of data has a run amount and a total run amount for each line.

I am looking for tools to reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.

Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.

Invoice number
Billing item
Est. Quantity
Actual quantity
Billable Qty
Sales order
Position number
Billing source
Total Billable Qty

[Code] ..........

View 4 Replies View Related

Links To CSV Files, Find And Replace Not Working

Apr 9, 2009

I have a large spreadsheet that has links to CSV files. Every month I need to create a new folder and put the CSV files into that folder, all the CSV files from month to month have the same name for each worksheet.

I'm trying to do Find and replace on the Folder name within the links to change say, February to March so I can just add the CSV files for each month and the spreadsheet will link to the new CSV file in the folder.

I have even tried adding all the February CSV's to the March folder so that when I overwrite the CSV's it will update the master spreadsheet, but it keeps asking me where to look for the folder.
Here is an example of a link:

='\FileservermsbilBRANCH REPORTSBM ReportsFebruary[TItemToDispense@40186 Export Dispensed Item Data-00.CSV]TItemToDispense@40186 Export Di'!BM2

As you can see the folder is named February within the link and I need to replace it with March.

View 9 Replies View Related

Doing Find / Replace Not Working If Replace Text Is Too Large

Feb 5, 2014

[Code] .....

Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.

I need it to post a message exactly as long as what I have in there. How do I get it to work?

View 6 Replies View Related

Macro To Compare The Data Between 2 Files And Generate The Count?

Apr 25, 2014

Creating a Macro which compare the Customer ID's present in Column H of Sample1 file with Column B of both Sample1 and Sample2 files.

The Count of Sample1 file should come in Column I and the count of Sample2 file should come in Column J of Sample1 file.

After above steps macro should automatically pick the lowest value (value should be greater than 0, if value is 0 than macro should consider it as blank) from Column I and J and paste it to Column K.

In Sample1 file I had also shown that how the data should look after running the Macro.

View 2 Replies View Related

Compare Data Between 2 Files And Copy Unique Rows?

Jun 24, 2014

I need creating a macro which compare the values of "Column B" of attached both "Sample1" and "Sample2" excel files and if any unique value found in Column B of "Sample2" file then the entire row should be get copied in "Sample1" file after row count.

For ex. the rows colored as yellow in "Sample2" file are unique and should be get copied in "Sample1" file.

View 3 Replies View Related

Get Vlookup In Vba Working

Apr 11, 2007

I have 3 sheets ("zip", "report", "ches") in a workbook

I want "ches" column A to be filled with data from "report" column D if there is a match of "zip" cell A12 from "report" column S

I keep getting the error
"unable to get the Vlookup property of the WorksheetFunction class"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range
Dim myRange1 As Range

Set myRange = ActiveWorkbook.Worksheets("report").Range("A1:S65536")
Set myRange1 = ActiveWorkbook.Worksheets("ches").Range("a65536").End(xlUp).Offset(1, 0)

myRange1.Value = Application.WorksheetFunction.VLookup(ActiveWorkbook.Worksheets("zip").Range("a12"), myRange, 4)
End Sub

View 9 Replies View Related

Copyrights 2005-15, All rights reserved