I had a VBA excel question. I have 2 excel files. Each file has a column on product SKUs. What I need is a VBA function that compares the 2 key columns and extracts the matching SKU and saves them in a 3rd excel file. That is the 3 file should contain only the SKUs that are in both of the 2 files.
View 9 RepliesI have on excel file like this :
SKU PRICE QUANTITY
1........25...........4
2.........41..........9
etc......etc........etc
The second one looks like this :
SKU UPC
0...786876
1...44777
etc...etc
While the first file contains about 300 active products the second one has a list of about 2000 active and deactivated products. What I need is to extract the UPC from the second file matching the active SKU from the first file so I have a final file that looks like this:
SKU UPC PRICE QUANTITY
1..44777...25.........4
and so on.
Heres what I need:
On the 'values' worksheet (first one), a formula in cell M2 that will search the worksheet 'oc_users' (second one) for any row containing "Active" in column G, these rows will return the corresponding email address in column A of the same worksheet. Skipping "Inactive" rows.
End result: anyone with an active status in oc_users will have their email populated in the 'values' worksheet.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I have a flat file (see attched). What I want is to be able to choose a "Unit no" from a form combo box and a list of names linked to that unit to appear below
View 4 Replies View RelatedI am trying to make a simple formula to populate a cell with the first matching substring found in a data cell (from a fixed list of substring keys).
Example of desired results:
Substring data: {X, YY, ZZ} (typically 6 to 15 values in real world use)
Data..........Calculated result..........Comment
AAX.................X........................Substring X was found in Data.
ABC.............[empty]...................None of substrings were (X, YY, ZZ were found)
XYYZZ.............X......................(only first match need be returned)
ABCZZ.............ZZ....................Substring ZZ found etc….
I am looking for something cleaner than nested if statements since there are likely to be a lot of values and different variations. The work would then be to copy the formula and populate the list of substrings to search for in the data. I tried some array formulas using search() but no luck so far.
I have two excel sheets. Lets say one is for May and another for June month. I have some data like name of account, account number their ratings . I wish to find if the account present in June month was present in May month sheet or not and if yes, then i need to extract a rating entry( column) from it and paste it in another sheet where i have copied all the data of June month so that i am able to see if the rating has changed over the months or remain same for that particular account...
i am trying vlookup but it is not able to search the name of the account even when it is present in the sheet( like WBIDFC) ... it seems the problem may be because of the format but i am not sure.... moreover i tried to vlookup via account no. but problem is that sometimes the account number is given and sometimes not.. Moreover , the format also does not match sometimes..
I need to use a macro to import data from an unknown # of order files to my master spreadsheet. My master spreadsheet & my order files contain a unique po number that can be used to find matching records. When the macro is ran & a match is found it needs to import the all data that to the master spreadsheet & updated the "processed" column for the record found. Also, all the lines in the unprocessed order files should be matched up. If a record is not matched, a warning needs to be displayed. If the record has already been processed, it just needs to be skipped. Attached is an example master spreadsheet & an example unprocessed orders spreadsheet. If at all possible, please split the unprocessed orders into separate files when testing the final product. The part that I will struggle with the most is looping thru separate files.
View 5 Replies View Relatedi've just started a new job and i'm working shifts and i'm on a 26 week rota that just repeats it self. is their away to put a week rota on to a row that is colured
if i clicked on a comand button i would like it to go into the yellow cells
I have around 20 Excelfiles each of them has around 15 Worksheets. Now I would like to extract certain values in order to make calculations and finally to build graphs and diagrams. the value is written next to the descripiton which is common. With copy and paste I get crazy.
How can I automatize it that I can scan Excel files and worksheets for certain values (e.g. total production volume).
I have about 50 - 60 text files and I need to find certain values, namely Name, Account No, total amount under Portfolio Value in the text file and copy these values to a worksheet under column: Name, Account and Portfolio Value respectively. All new entries are paste one row below the existing data.
Some of the text files may not have any data in it or may not have the Portfolio Value as the monthly transactions may occupied 2 or 3 text files. Some text files may have 2 Name in it and there will be 2 Portfolio Values in it which I will need both to be copy to the worksheet.
I have attached both the Excel file and the dummy txt file.
I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.
I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.
I have hyperlink on sheet1 from A4:Z500, I want to extract the hyperlink address of .pdf files from them to new sheet in column 'A'.
VB:
Sub ExtractHL_AdjacentCell()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
The below article explains exactly what I am trying to do but the formula in step 5 doesn't seem to work. I keep getting an #REF! Error.
[URL].....
I am not sure if maybe I'm typing in the quotations wrong or if the formula doesn't actually work.
I have to compare data in first sheet of two excel files and have to create a log file to display the logs of comparisons.
First workbook is placed in folder C:/Input/ with name as Input.xlsx and second workbook is placed in folder C:/Output/ with name as Output.xlsx.
I have to compare below cells between input.xlsx and output.xlsx.
Cell "B1" in input.xlsx , has to match with Cell "C4" in output.xlsxCell "B2" in input.xlsx , has to match with Cell "C5" in output.xlsx.
Cell "B3" in input.xlsx , has to match with Cell "C6" in output.xlsxCell "B4" in input.xlsx , has to match with Cell "C7" in output.xlsx.
Cell "B5" in input.xlsx , has to match with Cell "C10" in output.xlsxCell "B6" in input.xlsx , has to match with Cell "C9" in output.xlsx.
[Code] .......
After Comparing, I would like to log all the comparision in a log.csv, if cell "B1" in input file is matching "C4" in output file , say matching and color it green. If not matching, provide mismatch values from both cells and color it in red.
How we can compare 100's of excel files placed in Input and output folder and create one log file as stated above.
I have .Raw data in 3 different configurations, which can be opened in Excel.
I want to automatically extract relevant data (Columns) from these 3 different types of .Raw data before running analysis using chart using Macros.
Now here is the problem, Data from the .Rawdata needs to be accumulated. That is, I have multiple sheets of excel, which I have to open manually and extract specific data from individual columns onto another sheet to accumulate every data before running analysis.
How to tackle this situation?
1. Able to detect the right type .Raw data for use in Excel.
2. Extract data from many excel onto one main sheet, using Macros.
Firstly, I have attached a file called test2 which has two sheets within it, one called list and one called 12345. This is for demonstration purposes only as in real life these are both separate files.
What I am trying to do is create a history in the file called list that extracts certain data from the file called 12345 and places it in a more user friendly format. No problems with the formulas etc, just a case of a few ifs and buts.
My real problem lies in the fact that file 12345 is only one of hundreds of files and in order to create the history, I have to repeat the process on all the other files, so that the list grows as I copy the data.
Again for demo purposes, the file "list" sits in the C root ie C:list and the job files sit in C:files*.*.
Is there a way to automate the process so that formulas or VBA residing in the file "list" searches through the C:files*.* and populates the list as it runs through them.
I have a few hundred data/text files to analyze, some a few thousand lines deep. I would like to grab 5, just 5 numbers describing voltage levels. I know the preceding text for each of these in the cell and the following text as well. I just need to delete everything else and keep these 5 values in a column using a script or a macro, and do so for all these files in a specified folder.
This would be 3 weeks of work and the analysis needs to be done ASAP for my research.
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
"11/09/07 14:42:20 J13-50 and J6-9, PCU Resistance (XXXXXXX. Ohms) is within 1.000 to 3741."
Thousands of lines of ancillary data...........................
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
I have 2 workbooks.
The first one (Top Ten Auto Generator.xls) ( Sheet is Summary) has 1 row (13) with 4 cells that have data. A13 with date (today formula), B13, D13, and E13 are numbers. The cell range will be the same each time the data is copied (the workbook has a marco to generate the numbers each day already).
The date doesn't need to be copied, just the other 3 cells data (in the same order) using paste special.
The workbook that needs the cell data is a report ( Dashboard.xls) that has lots of different departments each using 3 columns for their specific data. The left most column A is the date listing to match.
The column Range for my department on that "Raw Data" sheet is "H", "I", & "J" where the "H" would get the data in "B13", "I" would get "D13", "J" would get "E13" for the date that matches the other workbook.
So the way it would work, is that once the vba is run the 3 cells from the auto generator are copied, then the vba opens the dashboard.xls and looks for the date in column A which matches the other workbooks A13 date value, and then the cells are pasted into that row, but in column H, I, & J.
Here's sort of some code that I put together to see if that would be easier to understand. I'm new at doing the vba so I don't have better code.
Private Sub CommandButtonpaste2dash_Click()
'get our data from generator
Windows("Top Ten Auto Generator.xls").Activate
Sheets("Summary").Select
Range("B13,D13,E13").Select
Range("E13").Activate
Selection.Copy
Workbooks.Open "Dashboard.xls"
Sheets("Raw Data").Activate
'look for the date in column A which matches A13 in the Auto generator
Some Code For finding the right date
'once found, paste special to the same row, but in column H (the 3 cells should paste together ok)
some code For pasting into H In the same row As the found date
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
ActiveWorkbook.Save
MsgBox "Done!!!"
End Sub
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
I'd like to find an excel solution to match what out what sales a customer knows versus what we have in our system. I've searched this board but can't find anything similar. In the below example, sheet 1 would be what came out of our system, and sheet 2 would be what the customer has emailed....
View 11 Replies View RelatedI have 2 sheets with lots of data in them. On the first sheet in the A column i have data that looks like this: 01234567 On the second sheet in the D column i have data that looks like this: 1234567 It's basically the same data except without the 0 in front. The first sheet has like 60k rows and the second sheet has around 18k rows. I need to find out what data from the second sheet is in the first one. I'm attaching an example.
View 2 Replies View RelatedI have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".
I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.
I have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".
I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.
Situation: I would like to compare the information between two worksheets and delete the rows that contain the same data in multiple columns, on a row by row comparison.
IE: I have two worksheets, each have identical row headers, with 5 columns each.
Company Load Date Load Time Load Description Amount ?Report?
Store#44 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
Store#44 5/14/2009 02:34:21 AM SLATOUR $20.00 WS1
Store#45 5/14/2009 01:55:41 AM GCHANDLER $100.00 WS1
Store#46 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
If column A(Company), B(Load Date), and E(Amount) for record 35 in worksheet one, match the same columns for a record in worksheet two, both records are deleted/highlighted/marked with an x in an additional column/anything.
Alternately, I can combine the data in both worksheets into one large worksheet, if that would make the solution easier. And or adding a column that idenifies which record came from which report.
Basically I have two similar reports; each contain a few rows of transactions that the other does not, I need to separate the matching transactions from the unique transactions, in order to balance the two.
I have tried using the Remove Duplicates function but it saves one of the matching records (they should add an opiton to delete matching records aswell keeping only truly unique records), I dont understand how to work Conditional Formatting to get it to do what I want, I dont know macros, or vlookups.
I have 4 Columns. Column A has a master list of inventory numbers(about 3000 of them). Column B has dollar figures associated with them. Column C has some inventory numbers in it(800 of them), all of which are also in the master list in Column A. Column D has dollar figures that are associated with the inventory numbers in Column C.
My question is how can I make it so that if an inventory number in Column C equals one in Column A that the dollar figure associated with the inventory number in Column C gets put into its own column on the same row as the inventory number it was equal to in Column A.
I've tried basic IF functions and I can't get it to work because the inventory numbers aren't lined up on the same row. So one may be on row 300 but the other maybe located in row 12. Maybe if a double sort was possible or if someone knows how to make the row in the IF statement a variable.