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.
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
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.
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.
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.
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.
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: ...
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?
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?
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:
I have 2 xls file what I want to do to compare the 2 files see attachment vlookup.zip
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
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.
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)
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. --
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?
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
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: Name CIQ ASA 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.
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.
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...............
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.
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 etc...
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.
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.
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.
Example 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 Sequence Agreement Billing source Description Total Billable Qty
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:
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?
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.
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.
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
