I have a sheet which have 20000 lines of row of data populated with data from column a to column n.
I need a formula or macro to search under column F for repetition of same data and to be copy the information of the row to a new sheet.
i want to use for searching a name in a colum. And copy the row of this name to another row.
I want to use this because i want to change an format to one i use all the time
person Astreet awork a
person Astreet bwork b
person bstreet cwork c
This is the situation: i want to search for person A and copy the data of the row , so copy street a. and work a. to another row
And i want to do the same for person b and so on until person z
I have a excel spread sheet that has 30 rows and single column(like A1,A2,A3....A20).I have to loop thru all these row values one by one and search for matching values in another spread sheet.If it mathches take the second column and third column values in the same row and paste it my spread sheet in the fourth column and fifth column.and put yes in the sixth column.Go to next row and do the same.Repeat this for all 20 rows .How to do that?
View 9 Replies View RelatedI can just use the macro recorder on. The screen shot below is an example of one of many sheets I have in a workbook. Each line would represent a customer order. I like to be able to search all but the first and last sheet for orders due on a user entered date. Then when it does find a match to the due date copy the order details( range b3:n3 in example below) over to a new sheet and continue searching and copy over other matches below the previous to creaye a list of orders due that day...
View 21 Replies View RelatedWhat I'm trying to do is lookup a description matching the date on worksheet 1 cell (a2) and go to worksheet 2 and copy all descriptions associated with that date into worksheet 1 in cells b3:b6 (array may vary) without duplicates.
The date and descriptions relate to safety inspections and I am trying to include them in a report. The answer is important to me as well as the benefits to many others.
On Work Sheet 1 Looks Like
[Code] .....
I have a workbook with 26 multiple worksheets that each represent 1 of 26 payperiods per year (bi-weekly payperiods). These worksheets consists of rows of employees names and hours worked each day. The columns consist of dates representing each day in that payperiod.
I need to be able to search all the worksheets (ie. search all 26 payperiods) to find specific 30 day intervals and then copy each employees hours worked to a master spreadsheet. I have tried variations of Index & Match formulae as well as vlookup/hlookup/ISNA without luck. I am not able to use VBA.
I have a dataset that is large. I want to write a macro program that checks the number in cell A1, copies the number and then the rows contents to another worksheet/spreadsheet. Then jumps to cell A2 and if the number is different to that of A1 copy the data of that entire row over from that row to the next worksheet/spreadsheet. If the number is the same as A1 then jump over to A3
Then continue this process but rather than comparing it to A1, compare it to the new number that has been found in coloumn A.
For example
7 A
7 A
6 AB
7 A
So starting at the first line then jumping the next one then to 6 and finally 7.
From a large set of process data, I would like to copy those instances where there is indeed a measurement available to a different sheet automatically.
The original data looks like this:
Time Temperature
02-Jan-2010 11:41:50 156.1
02-Jan-2010 11:42:50
02-Jan-2010 11:43:50
02-Jan-2010 11:44:50 157.3
02-Jan-2010 11:45:50
02-Jan-2010 11:46:50 156.8
02-Jan-2010 11:47:50 156.4
02-Jan-2010 11:49:50
02-Jan-2010 11:50:50 157.2
The resulting data shoot look like this (in a different sheet):
Time Temperature
02-Jan-2010 11:41:50 156.1
02-Jan-2010 11:44:50 157.3
02-Jan-2010 11:46:50 156.8
02-Jan-2010 11:47:50 156.4
02-Jan-2010 11:50:50 157.2
I have actually been learning quite a few tricks on my own since I started this data analysis project but I believe this requires some experience with macros, which I really don't have.
From mySQL database, i am getting a ton of data that is all being inputted into a single excel cell. Within this cell, there are certain data points that i wish to obtain (Bank #, SSN #, Routing #). There are multiple difficulties in obtaining this information.
1. these cells contain the same fields, but different number of total characters (differing in names, addresses, etc.)
2. some of the data points (like SSN) are inputted incorrectly, so even though a SSN is only 9 digits/characters, I may have to output all 11 that the SQL database placed within the cell.
Can I get some help? I'm thinking of a search function/macro within excel, because all of the fields show up correctly.
something like (i know this doesn't work)
= or("cellA1" contains/finds/function SSN,return the 9 characters after "SSN ")
um...yeah. difficult to explain, i'm sorry!
edit: not sure what is wrong the file. I have attached a new one.
I would Need a macro which would Search a Keyword in the excel sheet and copies and pates the data in Cell "A2". for Example "Market" and then after the search it copies 12 rows upwards and 10 columns from the Cell that the word "Market" is placed. And then it copies 12 rows downwards and 10 columns from the Cell that the word "Market" is placed.
I have tried recording the same but it does not work if the Word "Market" is placed in different cell value.
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
Attached is the spreadsheet example. Issue: I have a Column B with with entries in the cells i.e. - GroupA, GroupB, GroupC. Some cells can have multiple items and/or nothing in them.
Goal: I would like a formula for Column C that If B2 Contains "GroupA" Then C2 = "GroupA"... Then ofcourse if B3 Contains "GroupA" Then C3 = "GroupA" and so on.
Problem: I've tried the options from the other posts and I run into an issue that if B2 contains "GroupABC" then it puts "GroupA" in C2 and that is what I do not want to happen.
the macro needs to look in sheet2 column B, the data that it needs to look for is in sheet1 column a, once it finds the data in sheet2 column B, the original search criteria needs to be paste starting in sheet3 A1 while all the found data needs to be paste in column B.
It will then proceed to look for the next data in sheet1 column once it's done searching on the first item. I can't even think how to start with this one, it's too complicated for me. I attached a sample data. The search data is in sheet2 and the lookup data is in sheet1.
sheet 2 column 1 has a name in it. I need to search sheet 1 column 1 for that name
when that name is found copy "b" thru "F" of that row
and procede until end of column (using that same name) and then go to next name in list (sheet2) and repeat
Is there a (relatively) easy way to use vba to search a whole workbook (5 sheets) for a value entered via a user in an inputbox and then if any instances are found the entire row of the sheet it's in is copied to a new sheet, then any further instances found also would repeat that building a single sheet view?
View 4 Replies View RelatedI'm dealing with 4 columns. Column A is a list of names. Column C is also a list of names, each with a number beside it in Column D (i.e "John Smith" in cell C1 with "10" beside it in cell D1). I need a formula that can search the list of names in Column A for the matching "John Smith", then put the corresponding number ("10") beside his name in Column B. Meaning...if the search finds the matching "John Smith" in cell A6, I need the formula to copy "10" to cell B6.
View 2 Replies View RelatedThe attached wb will be used to schedule 10 machines. I have a dispatch ws that will have the machine # in column A, job/lot/ assy in column B, and load hrs in column C. there is a ws for each machine number. I want the worksheets for each machine to look in the dispatch ws, and return the info from B and C that has it's number in A for every occurence, currently it will return the 1st occurrence only.
Schedule.xls
I would like to search and copy values from one Worksheet to another.
In my case, The project number and account are listed in one worksheet called 'Source'.
In the Target sheet, The Account Column should be filled according to the Project number as per the data in source sheet.
Sample sheet : Sample.xls
Copying cells. It is across 2 sheets.
The cells I want to fill are in column V in sheet 2. ie v10, v11, v12, etc.
On sheet 2 column d contains an item count and V is the count of that item.
Then on sheet 1 column ranges (B4 To B49) (G4 To G49) (L4 To L49) (Q4 To Q49) contains the relevant item code to column D on sheet 2. Also on sheet one the count section that I want copied into V on sheet 2 which is columns D,I, N,S 4-49 etc..
My issue is that sheet 1 is constantly rearranging and sheet 2 is in numerical order of column D.
So how do I get sheet 2 V10 to populate by searching for the item code of D10 on sheet 2 in B,G,L,Q of sheet 1 then once located to then copy the contents of the number from D,I,N,S that works with the item list?
Then once done this that I can copy the formula all the way down column V in sheet 2?
I have a main worksheet called 'uren'. In this main worksheet I load data every week for around 40 persons. Every person has their own sheet, named equal to their name in the main worksheet 'uren'.
What I try to figure out is how to copy alle the data below their name in the main worksheet to the sheet which is named equal under the right weeknumber.
My main worksheet is build up like this ( used --- to keep the numbers in place when posting )
Weeknumber 1 ---Andre----Wiebe----Kayleigh----Sandra----etc
Complaints --------23-------45--------87---------34---------
Help-------------23.56------45------12,89--------67-------
Call---------------44--------4---------56---------12-------
hours-------------2,4-------56---------6--------34,67-----
etc
When the data is loaded ( the numbers you see) I want to copy all the data below a persons name ( number behind complaint, call etc) to the sheet which has the same name as the person. the data has to be copied to the correct weeknumber I use in the main worksheet.
The 40 additional sheets are build up like:
Weeknumber-------wk1-------wk2------wk3------wk4------etc
Complaints
Help
Call
hours
etc
So the data has to be copied under the correct weeknumber. The data in the main worksheet is loaded with a function and I quess the data also has to be copied without the fucntion (?)
the steps are below
===============
0. Row 1 to 9 in Sheet mv2f will copy into Sheet2 row 1 to 9.
1.Base on data in Sheet1, firstly macro will delete the same datain column A then it will start to search for 04022RP in Sheet mv2f.
2. macro will then copy 14 rows and paste into Sheet2 starting in row10.
3. then looping to 2nd data which is 04024RP T0.55 in Sheet mv2f and repeat the step above.
I have to extract all the entries that have certain words.
The worksheet has 600 rows, setup like
Column A=unique document number (never repeats)
Column B=Document Titles (some repeats, though the documents that are the same are not titled the same, though they have certain keywords in like documents)
Column C=Document Types
I want to search Column B for all titles containing the word/text string WOR, and then copy the row of A, B, and C when WOR appears in Column B, and put it into another worksheet.
Now obviously, I can do a "Find All" for WOR on Column B, but I can't cut and paste the results into another worksheet.
I prefer a simple formula, cause my VB and Macro skills are pretty non exsistant, though I will learn if that is what it takes.
It's finals week and I need to figure out this last VBA problem for my excel class. (Yes, we are allowed any outside help.)
The code below is something I tried to develope to do the following:
1. Search "N" column for blank cell
2. If cell not blank then select
3. Copy select cell information 3 cells to the left
4. Continue function to next row
I cannot figure out two things with this code. I need the code to copy/paste the information in the cell versus equalling values. Sometimes there is a small .jpg or .gif file inserted into the cell, so copy/paste is desired. Second is I need the function to not copy formulas if possible. I cannot figure either of things commands out. Hoping message boards can help me as they always have in the past.
Code: .....
i have been using a code that "Joe Was" wrote on this forum the code search all cells for a word i type and then copy the rows where the word was found into a new sheet. my problem is that the code overwrites the rows, so only one line is shown even when the result box say it finds 10 results.
Public Sub FindText()
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
Worksheets("Søkeside").Cells.ClearContents
For Each ws In ThisWorkbook.Worksheets......
Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
Found.EntireRow.Copy _
Destination:=Worksheets("Søkeside").Range("A2").End(xlUp).Offset(1, 0)
Sheets("Søkeside").......
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub
Basically a VBA code for a Command Button which when being pushed looks for the last cell being used in column A and copy/pastes rows 1 to 19 right in the next cell where this X (just an example..X could be anything basically) would be found. My columns go up to BU.
I have 2 workbooks:
- 1 contains tabs for each day of the month and each tab contains a couple of big tables.
- the other is where I want to gather certain data from the first workbook, selection should be made via a condition. I want to copy not the entire row that meets the criteria but lets say the first 4 cells of the row that meets the criteria (code below still copy's entire row, if you also have the code for my goal I would apreciate it very much).
The next part of code is what i've found on the internet and i'm trying to rebuild it to my situation, the biggest problem is the extarnal link to the 1st workbook sheet it seems to hang there everytime. If I copy a part of the table to a empty sheet in the 2nd workbook just linking to that sheet works so the problem is with the external linking what am I doing wrong?
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error Goto Err_Execute
LSearchRow = 4
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
Sheets("[D:Sudden Death BestandenCHC NetherlandsFuelFuel2007januari.xls]1!").Select
While Len(Range("A" & CStr(LSearchRow)).Value) > 0.........................
I have a workbook that is an export from an inventory database. Basically I need to search Sheet1 column B for any item with "DDC" in its value, select the entire row, copy/cut it, and paste it into Sheet3. It would be nice to have it delete the row from Sheet1 after successful paste. I want to stress the fact that I'm a no0b with excel macros and have a very small understanding of programming in general. I've tried taking a few different macros with similar goals and slapping them together, this is what I've come up with so far with still zero luck.
At the moment the error is "Application-defined or object-defined error" with "With Worksheets(1). Range("B")
Sub DDC()
With Worksheets(1).Range("B")
Set c = .Find("*DDC*", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & Worksheets("Sheet2").Range("A65536").End(xlUp).Row + 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
I need to search a sheet for a specific phrase "phrase". When the phrase occurs I need to copy adjacent cells into another sheet. Then, I need to continue searching through the end of the sheet for all other occurrences, copying them into the other sheet. For example: "Phrase" occurs in cell B2, B20, B42, and B82 of Sheet1.
When it occurs in B2 I need to copy C2 and D3 into Sheet 2, cells B2 and C2.
Then when it occurs again in B20 I need to copy C20 and D21 into Sheet 2, cells B3 and C3.
I have a workbook with 6 worksheets in, 5 worksheets contain data whilst the 6th I will use as a search worksheet. The 5 data worksheets contain columns A to J which have text entries in.
What I would like to click a button on the search worksheet, lets call it worksheet 1, and for an inputbox to allow the user to enter a text search. The macro would then search for this text in columns A to J and all rows (or rows with data in) on the 5 data worksheets, and if found, copy the entire contents of those rows where the text is found and paste them into worksheet 1, the search sheet.
I have done a forum search and found a few examples of this type of search but not across multiple worksheets, also I found this code that does search across multiple sheets but does not copy and paste:
VB:
Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
[Code]....
