creating a macro I have two sheets named customer list (I have only put in 30 rows as an example but some sheets have 400 rows)
in the sheet named list column "F" are the names it should be cross checked with column "F" in the sheet named customer if it is there entire row to be deleted
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies View RelatedI have two separate spreadsheets with lists of email addresses. Each spreadsheet has email addresses in column A, followed by different properties in subsequent columns to Column D. I'm looking for a way in excel to search one spreadsheet to see if the same email is present in both spreadsheets. Then, if it finds a match, I'm hoping to transfer the cell contents from one of the properties to the other spreadsheet.
In excel terms its something like this.
If an email address in Spreadsheet#1 cell A2 is found in the range A1:A10000 of Spreadsheet#2, then grab the information relevant to that email address from column B in Spreadsheet #2, and add it to E2 in Spreadsheet #1.
I have a huge lists of names that I need to work with. The first is a list of all employees. The second is a list of those employees who have submitted their monthly report. In theory every record should be a match. I need to compare the lists and mark the people who are missing their monthly report in the main list of all employees.
Not sure how to set this up in Excel
Sheet 1: Column A - All employees names, Columns B-M: used to show whether a report has been submitted.
Sheet 2-13 (July, Aug., Sept., through June): Column A - employees who have submitted their report.
I need Sheet 1 to show either all of the missing records across all the months or it can show the ones that have been received. Either will work.
I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid.
The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....
which formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
So I have two columns, in column a, i have a list of zip codes and in column b i have a larger list of zip codes. is it possible to find what zip codes in column a appear in column b, and then output those zips?
View 1 Replies View RelatedIn my application, I am reading a barcode label whose first three digits are a code for the model number (stored in Sheet1, Column C). On sheet 2, this 3 digit code appears in Column C also, and the exact model number for each 3 digit code is stored in Column A. I would like to display the information from Sheet 2 Column A in Sheet 1 Column H, depending on the value of Sheet 1 Column C. (for example, Sheet 1 Column C contains the code 011. On Sheet 2, Code 011 is the code for Model # HTE14WABWW. I would like to copy HTE14WABWW to column H on every row where the row's column C equals 011.
I see several examples of sorting data across sheets but nothing that really answers my specific question.
I am trying to use a macro to delete 2 columns from a spreadsheet using a macro.
This sounds easy, but due to the fact the columns needing to be deleted ‘cross’ merged rows the macro then deletes all the columns based on the merged cells.
In the attached spreadsheet (ColDel) if you manually highlight columns D and E and then use Edit/Delete this will delete columns D and E correctly.
I'm working with several sheets, each containing databases of customers that bought different products. I'm trying to do a cross reference so I know which customers has already bought multiple products.
English is not my first language and I might not be able to clearly express what my problem is, so I included a worksheet example.
In the example, you can see that Lea bought an apartment coded with APRT-001 from the first sheet. Lea also bought a condo coded with COND-002. What I'm trying to do is to put a note beside the her entry in the apartment that she has bought COND-002. And vice versa on the COND-002 entry on the second sheet.
What I usually do is combine all the data in one sheet, use Conditional Formatting to point out the duplicate name entries, and work through the whole thing manually. The thing is, there are thousands of names and I need to update the list regularly
IF A1 on sheet one matches the value in A1:A500 on sheet two, to display "Yes" in the cell on sheet one column B.
View 2 Replies View RelatedI am trying to drop (paste) in new data in a range in sheet 2 and cross reference a column with a table in sheet three and display all rows of matching instances in sheet 1. Example:
Sheet 3 has
a1 b1
amcdap amber connor
apsdap ashley simpson
sheet 2 has
a1 b1 c1 d1 e1
amcdap 300 400 2:00 9:00
apsdap 500 300 4:00 8:00
capdap 200 300 5:00 9:00
dlsdap 400 300 2:00 8:00
I need to return only rows 1 and 2 to sheet 1. I guess ideally I'd like to drop data in sheet 2, click button.
Sheet 1 holds aprox 30,000 lines of data, each line of data has values populated in columns A:P
Each row in column A holds a unique number (displayed as TEXT) made up of ddmmyy plus number in two digits, for exampls 18011209
how using code I can check each number through column A for duplicates and delete that particular row of data when found
In the attached sheet, i have data in 2 sheets in the same fromat. Both the sheets are seperate reports.
Coloumn A is the list of Team Ids, Coloumn B is the Ids of people who are assigned in the team. Sheet 2 also have the same details with changes in the people ID.
i am trying to find if the people IDs in report 1 is the same in report 2. i need a formula that will check if the people ID is matching to the same team ID as in report 1.. If its not the same, the formula should be able to show that.
eg :
In report 1, "197595" in coloumn B is against "4011-11341" in Coloumn A.
I need to check if "197595" is against "4011-11341" or some other number in report 2. If "197595" is not against "4011-11341" in report 2, it should show me " Miss match". if "197595" is is against "4011-11341" in report 2 as well, it should show me "match"Project match.xlsx
I have excel file, consists of 4 sheets Data, PF, Gratuity, Discrepancy. Here I have Emp no. field as key field, now here first I have to check Data Sheet data with PF.
For example,
Suppose, we are considering Emp no.: 4 from data sheet. It will check in PF sheet for same Emp no. if it find then it will check the relationship and first name and last name from data sheet. If it is ok then it will return output in discrepancy sheet with OK. Discrepancy sheet format:
Emp No. Spouse Child1 Child2 Father
(All the relationship present in Data Sheet in Column)
4 OK
9 OK OK OK NA
And so on.
What I am trying to do is, check if the value of c2 in update is the different to the value of c2 in original then run a code (for the moment just says msgbox so I know it works).
Code:
Sub what_needs_update()
'UPS = Sheets("Update").Range("c2:76")
'ORS = Sheets("Original").Range("c2:76")
For Each cell In Sheets("Update").Range("c2:S76")
[Code] ..........
I have a macro that will not complete, and I can not figure out why. The macro is CHECK_for_Sheets_THEN_Copy_DATA:
View 3 Replies View RelatedUsing VBA, I am running code that filters the data in my column "Account" on my main sheet, creates a new sheet for each account in that column then copies the filtered data to the new sheet. There will always be the same list of Accounts with the exception that there might not be data for one or more.
In that case, I still need it to create a new sheet with the account name but just have some text stating "no data available for his account".
I can find code to search if a specific sheet name exists but, in my case, I don't know which one won't have data at run time.
I was hoping I could put a static list of the expected sheet names into an array and loop through that list and create the sheet for any that it can't find.
My list: FTL, DTB, CAR, BLD, RSG, STS
I have two workbooks open, the first is a summary, the second has new data that needs transferring into the summary. Before transferring the data I need to check that 10 specific sheets exist in the file with the new data.
I have found some code that checks for one sheet name, but really need to check for 10 specific names, if any are missing then I need a message to appear, listing the missing ones and stopping the macro from proceeding,
Dim mySheetName As String, mySheetNameTest As String
ActiveWindow.ActivateNext
mySheetName = "Data"
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).Name
If Err.Number = 0 Then
[code].....
How to check if a range of cells matches specific text.
Example:
A1 - dog
B1 - cat
C1 - mouse
I want the formula to give me "yes" if dog is present.
=IF(A1="dog", "yes", "no") is for one cell. However if I do it this way:
=IF(A1:C1="dog", "yes", "no") I get an error.
I have an issue where I want to delete a row on Sheet1 if the cell in column a of that row is a match in a column A of Sheet2. If there is no Match, I want it to move to the next row until it has checked all rows with information. The size of the data varies daily. I do not want to check Row 1 either. Below is an example of what would happen.
BEFORE MACRO:
Sheet 1 Column A
ID#
A
B
C
D
Sheet 2 Column A
ID#
A
C
D
AFTER MACRO:
Sheet 1 Column A
ID#
B
Sheet 2 Column A
ID#
A
C
D
I'm looking for a macro to basically produce me a list of shop names on one sheet with their appropriate sales information. Bascially, i have 4 sheets one for each week of the month, with all the sales info for various shops. Each sheet will list the same shop about 10 times as they make numerous transactions so each one is listed. The list on each of the 4 sheets can be any length and will change week to week depending on how many sales there are.
What i want is to find shops that appear in more than one week and have then appear on a list on a 5th sheet, i.e. my good performers sheet!
VBA code that would delete ALL other sheets in the workbook except for Sheet "Data".
View 2 Replies View RelatedI have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried:
For i = 1 To 200
range("B" & i).activate
If range("B" & i).value = " Total:" Then
rows(activecell.row).select
selection.delete shift:=xlUp
End If
Next i
When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"
I have a Excel file with alot of data in it. I need a macro that will create a report for me and relive me from alot of manual work. The 1st sheet is named "Projects". This contains the search arguments. The 2nd sheet is named "Database". This is the sheet where I want to search in. The 3rd sheet is named "Report". This will contain the results of the search. So the going will be something like this:
Copy row 7 from "Projects" into row 7 in "Report". Then use the value in that row, column E (named Search code). Search for rows that has this value in column E in the "Database" sheet. Copy all those rows to "Report" sheet. Copy row 8 from "Projects", leave two rows of space and paste into "Report". Repeat the procedure mentioned above. Repeat this until reaching a row in "Projects" that has no value in Column A.
I have two sheets with pretty much the same content but not exactely. I need to compare the data from the first sheet to the data in the second sheet in this way:
data from column b (numbers) in the first sheet needs to be compared to data in column b in the second sheet. if this dosen't match it needs to go to the next line.
if the match is positive it needs to compare the data from column d on the first sheet to the data on the second sheet same column and copy the data from the first sheet's column d to the second sheet's column d. But only if the data in column d on the first sheet isn't blank. If it is it should leave the data on column d second sheet intact.
Most likely another very basic on...
2 worksheets.
Worksheet 1, holding all data in column C.
Worksheet 2, the value in column B.
In need for a function that list all exact matches of the values in worksheet 2 column B that are found in Column C of worksheet 1.
I got this VBA code that checks for the string in worksheet 2, column B, but it is a Instr, and I need a exact match and only the exact match. Is this difficult to modify and how?
Or would this be a regular array function?
(I would not need a macro, since the data change constantly and I prefer function as they update easily)
Function matchArray(ByVal testString As String, ByVal dataRange As Range, Optional IndexNum As Long) As Variant
Dim outRRay() As String
Dim rangeArray As Variant
Dim xColl As New Collection
Dim xVal As Variant
Dim rIndex As Long
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
rCell.EntireRow.Delete
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
rCell.EntireRow.Delete
End If.........................................
I need a macro to start at cell "A1" on sheet1 and then find that same value on sheet 2 in column B. Once it finds that value in sheet 2, the code would copy the row related to "A1" (A1:H1) into the row on sheet 2 with the value matching "A1" from sheet 1. Once it has done this I need it to do the same from A2:A598. I thought this code below was working but it seems to erase a row from sheet 2 if it is not present in sheet 1. I need the macro to only update the row if the information in column A on both sheets is the same. Here is the code I am using
Code:
Sub FindStr()
Dim rFndCell As Range
Dim strData As String
[Code].....