Our school system is trying to clean up student records. They have a demographic worksheet of hundreds of records. Each student has a student ID number. We are trying to flag students whose ID numbers in 9th grade do not match an ID number for 10th grade. so we are left with only students who have matching ID numbers for both 9th and 10th grade. We are trying to find the right function(s) to make this work without VBA. I am attaching a small sample file
I am trying to write some VBA code for Excel that looks at a several different cells for a specific value and if that value is there returns a prompt message outlining to the user specific cell info. For instance, if some Excel cells in a column show that someone has not paid their bill yet, I would like a message to appear as soon as one opens the spreadsheet showing the bills for what customers are still unpaid.
i have cell N1 witch is a number example "8832" cell O1 is text example "state street" now i have A1 thur A10 as an address "number" now E1 thur E10 is street address "text" i need to take n1 match it to A1-A10 then take that row number and see if E1 of that row numbers matches O1 if it does that that row numer and get g of that number if not keep checking to i find a match
I have 2 worksheets. Sheet 1 has in column B a single ID number in every cell, Sheet 2 in column A can have multiple numbers in a cell (e.g. "55517 / 55518 /55519" written in one cell.)
For every row in Sheet 1, I need to find the ID in Sheet 2 and add in column C in Sheet 2 the corresponding cell value found in column E on Sheet 1.
By doing this formula, when I am in Sheet 1, I can already find the row number in Sheet 2, if it exists.
I suppose I can then move to the destination cell by using OFFSET.
This is all I have been able to figure out conceptually.
I think it makes more sense for this to be in VBA. Especially the 'writing part' I don't know how to do. After the OFFSET part, how do you tell Excel to write something to that cell?
Because several IDs from Sheet 1 can occur in Sheet 2, it would have to add like REPLACE at the end (ie. len(cell)) "+result", so they get summed if there are multiple finds.
I am also not sure the MATCH sentence above can be translated as a Macro, or whether it is even an efficient way.
And finally, it has to loop (repeat for every row in Sheet 1), which must require VBA.
I want to search or filter the list and find the only non matched record in the list and put this in another sheet ie max as this is the only unique name in the list, all the others are matched. Everything I have tried eg filter for unique records returns ian andy john david max, which is not what I want.
I have two tables, both contain a column of ID numbers and a column of addresses.
I would like a method to match the IDs from table1 to those of table2 - if no matches are found, search for matching addresses, if a match is found then output the ID in table1, the address, and the corresponding ID in table2.
If still no matches are found, output the ID and address from table1 and indicate that it didn't match.
Then I want to check table2 records - i.e. search for matching address between the records and output the table 1 ID, address and table2 ID were partial or no matches are found, as above.
I have this sheet, which is just an example (the actual one has many more records). subset.xlsx
I received this sheet (again, example)... new.xlsx
I need to add the new sheet to the old sheet, so that they are merged into one sheet. Also, if the same "LOC" appears in both sheets, I need to overwrite all the records for that "LOC" in the old sheet with the records from the new sheet.
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.
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
I need to search through a worksheet for a value located in the first A column eg. and then put down a flag ( or bettar the searched text ) to che previous row in B column
Es:
I search the text 'xxx' in A column
if I found the searched text 'xxx' in A5 i need to have a flag or better 'xxx' in B4
Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
I am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
sorting duplicate e-mails across three columns in an Excel spreadsheet.
Precisely, I have three mailing lists (Column A, B, and C) that I would like to sort.
I would like to know what e-mail addresses appear in more than one Column (Mailing List), and I would like to highlight/flag them somehow.
I have attached an example spreadhseet that contains fake e-mail addresses for test purposes. As you can see, some e-mail addresses are duplicated or in triplicate across the 3 Columns. In other cases, an e-mail address may be unique to a specific Column.
In my real spreadsheet, I have approximately 3,500 rows and 3 columns.
I have many small spreadsheets of data organised into 4 columns and anything upto 250 rows. I want to be able to test if all the cells along each row have the same data in each of the 4 cells, so 4 occurences of the same thing. Each row of data will be different. (I'm not intertested in matching the data going down the column).
However, I won't know with each spreadsheet what is the exact data I will be looking for - if all 4 cells don't have the same data, that is fine as this will then require my manual attention which is the purpose of my project. I just want to avoid having to check through lots of rows of data where all the data does already match.
The data will usually be text but it can be IP addresses and numbers too. I need a formula or set of operators to use for is exercise.
to set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
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.
1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?
2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.
For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision. e.g.
Grand Peninsula=B5/N3 Meadow Glen(Mansfield)=B6/N3 ...etc
Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
I have an Excel spreadsheet with 3 columns (A, B, and C) and 600 rows. Each cell in A and B contains a 6-diget number. Column C is empty.
I want to determine whether the number in A1, A2, ….A600 appears anywhere in column B. If A(n) does appear in column B then I want to enter a symbol (say a Y) in C(n). If A(n) does not appear in column B then I want to enter a different symbol (say N) in C(n).
What is the formula I enter (and where) that will do that?
I am trying to set up a spreadsheet to track certification expiration dates and things of that nature at my volunteer fire department. At my career job, we use specialty software like Firehouse, but that is way too expensive so I must settle for Excel..or maybe Access.
Basically, I need a formula that will look at todays date and compare it to the date in a given cell and then somehow differentiate thise which are expiring at a given intercal.
For example, if someones EMT was expiring in 6 months it would turn yellow, then at 3 months turn blue, then red at 1 month then black if it expires.
It don't necessarily have to be just like that, just some way to differentiate depending on the how close to expiration it is.
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA: 1.1 If NOT present, tell the user "Error". 1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue) 2.1 If quantity <= 0 then tell the user "Nothing left" 2.2 else REDUCE that quantity by 1 unit.
