Add Missing Data By Comparing With Another Workbook
Sep 18, 2007
I have been searching the forum for weeks now for an solution to my need, but was unsuccessful. I have two Workbooks. One is for tracking new enrollments in the school for whom I work named WVT.xls and the other is for tracking the school materials assigned to each student named 2007.xls (for inventory accounting). I need to streamline adding new enrollments names to both Workbooks by adding to only one manually and then using VBA code to update the names in the other workbook. The sequence would be to type the names into WVT.XLS and run a macro in 2007. XLS to automatically update the names. Below is the locations of the same named columns in each workbook.
First Name Last Name
WVT.XLS Column B Column A
2007.XLS Column B Column C
In 2007.XLS, there is a sheet for each school location that is represented by a unique school code. In WVT.XLS, there is one sheet for all enrollments from the start of distributing materials. Each row is a student. There is a column that contains the unique school code to identify in which school the student has enrolled.
View 9 Replies
ADVERTISEMENT
Feb 8, 2012
I am in the process of comparing the data between 2 different sheets and the data on each sheet contains 2 columns of information. I am trying to find a way to compare one sheet to that same item on the second sheet and see if anything is missing, however the second sheet contains more information between the two columns and the rows are never the same during my comparison. I've tried Match, Lookup, Indirect, and if/then with an And functions and because the rows do not stay the same the data is not accurate. Here is a brief example of what I am working on:
SHEET 1
A B
1 Red Delicious Apple 125 Count Apple
2 Red Delicious Apple 125 Count Fresh Fruit
[Code]....
So in these comparisons, the names in Column A match identically, so if I try to say take the name from sheet 1 and match to that in sheet 2 and if the wording in Column B matches then put a "Yes" value. But you can see on Sheet 2 by the time it gets to rows 14, 15, 16, the Baby Food Chicken has a 3rd item in Column B in comaprison to Sheet 1, so any type of match doesn't work.
I am using Excel 2003.
View 9 Replies
View Related
Jan 23, 2014
I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.
I attach an example : NameCheck.xlsx
View 3 Replies
View Related
Mar 28, 2014
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
View 3 Replies
View Related
Sep 18, 2008
new data goes into the report, the pivot table looks at a dynamic range and confirmed that the range doesn't select any empty cells.
Just wrote a macro to fill missing cells with a data on workbook open.
all the data is date form, yet still when i refresh the pivot table i look my montly grouping, when i try to group again it says "unable to group"
View 9 Replies
View Related
Jul 8, 2008
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
View 9 Replies
View Related
Apr 17, 2008
I have problem about adding workbook event.
when I go to ThisWorkbook and click the left side dropdown at the top of the code window, I can't see the word "Workbook" appear. What I can see is only (General) and there is no other choices.
Other worksheet and application event work fine.
attached is a screen shot when i try to add a workbook event.
View 7 Replies
View Related
Aug 26, 2009
In Excel I've always had a Personal file come up when Excel was opened. It may have accidently got closed??? The personal file does not open with Excel and I am unable to find it. It is required to run macros correct?
View 13 Replies
View Related
Dec 21, 2009
However it will only add data to the active sheet when i am asking it to loop through the workbook missing out specified sheets. Would anyone be able to look over the code to see where the error is as to why it will not loop through the remaining sheets in the work book.
View 5 Replies
View Related
Sep 8, 2007
SETUP: I have a shared workbook that is accessed by many people. It is designed to control the flow of work during the monthly close process. These processes are controlled by macros. There are 40 sheets in this workbook.
PROBLEM: On of my location's workbooks frequently loses half of it's sheets in the workbook. Counted in the neighborhood of 14 to 19 sheets go missing.
EFFORTS: I have attempted to Delete, Cut, Move, etc... these sheets with no success.
Only when I put the file in an exclusive state can I manipulate the sheets.
View 7 Replies
View Related
Aug 19, 2013
I would like to have a macro designed. I have 2 workbooks, one having latest information and the other having the information in database already. both workbooks have 26 columns each. I would like to check if a value that exists in Column A of the "latest-information" workbook also exists in the "already-in-database" workbook. If so, I would like to update all 26 columns of that row in the "already-in-database" workbook with information from the "latest-information" workbook. If the value does not exist, I would like to add all 26 fields in that row into a new sheet on the "already-in-database" workbook.
View 3 Replies
View Related
Apr 18, 2013
I am trying to use a macro to move a range of excel tabs to the front of the workbook however some of the tabs could be missing due to no information.
If I simply record the macro by moving them manually if the procedure arrives at one that is missing it would report an error.
So in essence I need it to look for the tab and if its there move and if its not move onto the next until it arrives at the last one.
View 9 Replies
View Related
Feb 8, 2014
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
View 2 Replies
View Related
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
Oct 2, 2013
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies
View Related
Jun 1, 2014
I have a table with 21 numbers two rows with x and y e.g.
nr.xy
205,5
195,4
185,3
so on ........
Now I wish to make a table with 100 numbers in wish y is interpolated based on y in the given table. Using many IF statements (in Dutch "ALS") isn't useful since the number of statements is becoming too large and besides is a number is missing e.g. nr7 x=0,7 the y is 0.
This is not working properly:
=
ALS(P12<$H$28;"Bodem";
ALS(P12<$H$27;$i$28;
ALS(P12<$H$26;$i$27;
ALS(P12<$H$25;$i$26;
ALS(P12<$H$24;$i$25;
ALS(P12<$H$23;$i$24;
[Code] .....
View 11 Replies
View Related
Mar 13, 2007
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies
View Related
May 15, 2014
I need to take in third sheet all data which are missing in second sheet from first sheet, for example:
IDName LastName
1John Doe
2Jany Tine
3Paull Marphy
4East Doe
5West Marlyn
6ThirstanWist
7SecondEast
8ExampleMiddle
9Doe Tryer
10MarlynEarly
Sheet 1
IDName LastName
1John Doe
4East Doe
9Doe Tryer
10MarlynEarly
Sheet 2
And in Sheet 3 to get result
2Jany Tine
3Paull Marphy
5West Marlyn
6ThirstanWist
7SecondEast
8ExampleMiddle
Sheet 3
View 14 Replies
View Related
Dec 6, 2012
I am trying to import data from web to excel. I posted the link below to the website I am attempting to import from. The only part I need is the statistics. However, I cannot get it come thru by getting external data from web.
The goal is to great an auto updating spreadsheet with multiple sheets so copy and paste is not feasible.
[URL]
View 1 Replies
View Related
Mar 2, 2009
I have ben given a excel file from another department at work as they had been told i know a bit about excel. Excel... Yes, Visual Basic... Nil, zilch, nada! It really might as well be written in chinese!!
What has happened is the guy who set this up has since been made redundant from the company and they now want the file to do one more thing.
Basically what they want the file to do is, when the user clicks the 'generate file' button, if E2 has no date entered then a message box pops up stopping the user from going any further until a date is entered.
View 4 Replies
View Related
Dec 11, 2013
I need to be able to create an average total that skips any missing information. For example, if I have four values I need the average for all four (e.g. 5+6+7+3/4), but if one is missing I need the average of three (e.g. 5+7+3/3).
I found the array formula: =AVERAGE(IF($A$1:$A$50,$A$1:$A$5)) [Note: I pressed Ctrl+Shift+Enter to make it an array formula]. Which works perfectly for cells that are next to each other (e.g. A4:W4). The trouble I am having though is that the cells I need to pull the data from are not next to each other e.g. (K4, N4, S4, W4).
I did try to ammend the array formula with the following: =AVERAGE(IF($K$4,$N$4,$S$4,$W$40,$K$4,$N$4,$S$4,$W$4)). But of course that came back with a "too many arguments" error.
How can I ammend this information to make this formula work.
Extra information if needed: The cells that have missing data are not blank or have a 0 value, they show a "#N/A" (which doesn't need fixing).
View 4 Replies
View Related
Jan 8, 2008
Is there a way to flag data that hasn't been used in another worksheet. For example, a second worksheet consolidates the data into two different groups, but someone spelled the name of one of the groups wrong in the first worksheet and the data was not added via a sumif function.
View 2 Replies
View Related
Jul 9, 2013
I am importing information from 20 stores by item. If a store doesn't carry an item it is not creating any data for that store but in turn it throws my whole template out of whack. The graph would be a repetitive 1-20. Can I create this pattern and import the information to match while creating blank cells when the data isn't available? I manually entered the left column and the items columns "B" on are imported.
excel store column test.xls
StoreSKUDescriptionMfg.#UPC
115170016GUN,BB BUCK 00000000210539256221051
225170016GUN,BB BUCK 00000000210539256221051
335170016GUN,BB BUCK 00000000210539256221051
445170016GUN,BB BUCK 00000000210539256221051
5
6
775170016GUN,BB BUCK 00000000210539256221051
885170016GUN,BB BUCK 00000000210539256221051
995170016GUN,BB BUCK 00000000210539256221051
10105170016GUN,BB BUCK 00000000210539256221051
11115170016GUN,BB BUCK 00000000210539256221051
12125170016GUN,BB BUCK 00000000210539256221051
14145170016GUN,BB BUCK 00000000210539256221051
15
16165170016GUN,BB BUCK 00000000210539256221051
17175170016GUN,BB BUCK 00000000210539256221051
18185170016GUN,BB BUCK 00000000210539256221051
19195170016GUN,BB BUCK 00000000210539256221051
20205170016GUN,BB BUCK 00000000210539256221051
21215170016GUN,BB BUCK 00000000210539256221051
5170016
View 5 Replies
View Related
May 17, 2014
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
View 1 Replies
View Related
Apr 18, 2013
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.
View 2 Replies
View Related
Sep 13, 2008
I have created a user form for some data submission. I am looking for a macro to check the text boxes, that I have named appropriately i.e. Name, Address, City, State etc. , to make sure there is something entered.
If nothing is entered it needs to return a msgbox that says what field is blank.
View 9 Replies
View Related
Mar 13, 2007
I have a pivot table based on a sales sheet called "Datasheet". I have created the pivot table using a dynamic name as below =OFFSET(DataSheet!$A$1,0,0,COUNTA(DataSheet!$A:$A),12). My problem is that I have 11100 lines of sales data but the when i refresh my pivot table it only seems to be going down to row 10979. Therefore missing out some of the data. Is there a max number of lines for a dynamic named range or is my formula wrong in any way
View 3 Replies
View Related
Aug 25, 2014
Any easy way to retrieve data from another table using an array formula.
I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table.
I have the spreadsheet attached. I attempted to use an if/and statement :
=IF(AND(D2=C8I:I,E2=J:J),H2,0)
but found out that it does not work with arrays and only found the first address by default.
How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name?
View 5 Replies
View Related
Oct 23, 2008
I'm using a worksheet that has a time value in column A.
The time is manually updated every five minutes.
Is there a formula that I can use to catch any missing times?
View 6 Replies
View Related
Feb 10, 2010
I have 2 sheets in the same workbook (sheet 1 & 2) with one matching column (A) of info and need them to combine and fill the missing data. Each sheet has identical column heading and the amount of data is not the same; sheet 1 has ~2000 rows and the other ~5000. Sheet 1 is consolidated so I would like to fill in the missing cells from sheet 2.
Ex. Sheet 1.......
Ex. Sheet 2.....
As you can see, the missing cells in sheet 1 could be filled from the data in sheet 2; also note that the above info is on different rows (and will be random for each case). I only want the columns to fill in that have a matching email, the other 3000 rows in sheet 2 do not match with sheet 1 and I don't want the rest of it to transfer over.
View 4 Replies
View Related
