Basically I would like to copy rows where the name of the person (in Column A) matches a separate this.
E.g.
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Paul
01/2/1966
Lookup list:
Peter
Jim
In Sheet2:
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Note that there are many names (this is just an abstract) so I think it requires a macro.
I had a quick look around and couldn't find exactly what I was after my apologies if it already exists. I have attached a sample worksheet and I would like it the entire row A from sheet 1 to sheet 3 if the cells A2 OR D2 contain what is in cell Sheet2!A1.
View 3 Replies View RelatedOn my main worksheet, I have a list of stocks in the "A column", so for example reading down the list would be:
Vodafone
Vodafone
Ericsson
Nokia etc...
I have a seperate worksheet for each different name (about 100), and I wanted to know if there was a way to select all the information relating to the name "vodafone" for example, and to copy and paste that into the worksheet called vodafone. The problem is copying and pasting 100 times would take ages and wanted to know whether there is an easier way using VBA.
Copy rows from one Sheet to another based on a separate cell value But specifically, I am trying to copy row values from Columns C through column Z in Worksheet 1 of file POHeader.xlsx to row values Columns N through AK in file POReceiv.xlsx when the (Purchase Order #) values in Column A of each file match.
The reason is behind this is - one file has the unique Purchase Order number as the key without associated parts and the other file has the associated part number as the key with purchase order number attached.
I don't know whether I need to use VBA or if I can just use an index and match function.
i'm trying to create a macro to find any value <> "" from COLUMN B then copy Bx;Cx;Dx and paste into another worksheet with the name of COLUMN A.
And when the cells Cx or Dx is empty should appear 0 into the other worksheet
for example.
sheet1 ..... cell("dx") is empty
a b c d
w 5 3
the macro would copy 5,3,0 and paste into sheet named "w".
i found this code on internet but it's not quite good for me because it copies the hole row. i tried everything to change that but i couldn't.
[url]
(ORIGINAL CODE)
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.
I am trying to do this in VBA and I was wondering if there is a way to do this.
I have some specific procucts in coloum A in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 1
LSearchRow = 1
[Code] ....
Err_Execute:
MsgBox "An error occurred."
End Sub
I have some specific procucts in coloum A(a2, a3, a4, till a50) in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
[Code] .....
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'm trying to use a vlookup or some formulae in sheet 3 that looks at AGDP and matchs column A with a number in statement file worksheet column b. If they match not all of them will then the row is cut and paste into Sheet 3.
View 9 Replies View RelatedI have 2 work books A and B. I want to be able to copy the entire rows out of WB A, which contain the same data as one of colums in WB B
e.g
WB A
Bob Smith | 3 Smith Lane | 0204587512 | bob.smith@emailaddress.com |
Tom Brown | 3 Brown Lane | 020241445 | tom.brown@emailaddress.com |
WB B
| bob.smith@emailaddress.com |
Would give me WB C
Bob Smith | 3 Smith Lane | 0204587512 | bob.smith@emailaddress.com |
Any way of doing this?? I know that i could use ' find' then copy the data, except i have around 300 records...
setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.
Heres the ....
I have used marcos to create two worksheets and need to compare each row of the first three columns in each worksheet to find any rows which non-matching data. I am comparing retrieved data (Worksheet1) against a master list (Worksheet2) to ensure the retrieved data was entered correctly. The three columns of data include Manufacturer, Lot number and Date.
The row in which the matches will occur is random. The three values from the retrieved data only have to find a match somewhere in the master list. I can place both the retrieved data and the master list side-by-side on the same worksheet if that would make things more efficient for running the comparison.
The non-matching rows, if found, will be copied over to another worksheet along with its corresponding row in columns "D" and "E" found in Worksheet1. The additional data alerts me to the location where the data was entered incorrectly. The master list does not have any location data in it.
I'm trying to eliminate any .select or .activate from my code to speed things up and reduce screen flicker. At the moment, I have a sheet created at the click of a button. Basically for every date exclusive of weekends, there are 4 entries, for each date there are the following entries AM Metro, PM Metro, AM Regional, PM Regional. The user wants to be able to change the date range in the "overview" sheet and when there is data already in the other columns for these matching rows, have the input sheet update with old (copied??) info, if no previous data then add all 4 rows and sort... if previous date not in date range then delete entire row.
View 4 Replies View RelatedI've been struggling with dependencies, so I have two sheets.. the first sheet contains a simple table:
EmployeeUsernameStatusStatus Note
Alex
Josh
Sarah
David
This is the second table:
Employee Username ActivePassword Change Days Since Change
Alex al2 Y Y 20
Alex alex Y Y 11
Alex aa2 N N 0
Josh josh2 Y Y 10
Sarah ssarah N N 0
Sarah sarah Y Y 1
David David Y Y 4
The problem is in the first table as I don't know how to populate the "Status Note" column which depends on these two columns The column Username which is a dependent list from table 2 that contains the username associated with each employee selection The column Status which is a dropdown list that contains (Active, Password Change, and Days Since Change)
For example if the following is selected for user Alex: al2 and Active how can I make Excel display Y in the status note column
EmployeeEmployee UsernameStatusStatus Note
Alex al2 Active Y
Josh
Sarah
David
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
I have a large sheet of data I'm trying to sort through. Each row of data has a column that contains a long string description. Each of these strings contains a three letter codes in all caps that I want to be able to pull out. I have a seperate sheet that has all of the possible three letter codes in one column. Is there a formula that can compare each string of text to this list of three letter codes and if it find a match put that code in a column on the original sheet?
Doc Number
String
Code
33333
This is an example string of text. An example three letter code would be HCB. The location of the code within the string varies.
HCB
33332
This is an example string of text. An example three letter code would be CDQ. The location of the code within the string varies.
CDQ
33331
This is an example string of text. An example three letter code would be RCA. The location of the code within the string varies.
RCA
Codes
DCP
HCB
RCA
CDQ
LCP
DCA
SCR
Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
ExampleCell1
ExampleCell2
ExampleCell3
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".
Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.
Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.
I need to track only certain tasks id's out of entire task id database. I have 1 column of data on sheet "A" that represents task id's I want to track. On sheet "B" I have multiple columns of data, the first column being the task id's. I need to compare the column on sheet A with the first column on sheet B. If there is a matching task id, I need to copy the entire row of data from sheet "B" on to a new sheet "C". If there is no matching task id on sheet "B", I would like to copy just the task id. Each week I would get a new set of data for sheet "B" (which can vary in length) and I need the ability add or subtract task id's on sheet "A" that need tracking. See the attached xls file for a piece of sample data.
View 4 Replies View RelatedI am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI am trying to select all cells in a range whos contents match the contents of a specific cell so I can group the respective rows of these cells. I did a forum search but I am not sure if I have found what I am looking to do. I am a very basic user of VB so keep answers as simple as possible.
View 8 Replies View RelatedI have a sheet with some survey data. the data covers about 4 months. There are about 2200 rows and 8 columns.
The "code" could be in there more than once as the person took the survey multipule times, but all other data is different. How can i pull out the whole row when the code is there more than once.
I want to know all the "codes" with multipule entries that took the survey more than once then trend there scores.
CentercodeRecommendReasonEnvironmentTraining ManagerOverall LHQTR27909415Learning effect4444LHQTR28844652
Center environment2222LHQTR45614375Service5555LHQTR96944292Service2222LHQTR144769543
Center environment4433LHQTR144769543Learning effect3433LHQTR155258791Service3213LHQTR168772563
Center environment2232LHQTR168772563Center environment3332LHQTR168772565
Learning effect4414LHQTR173991905Learning effect4445LHQTR192966385Service5555LHQTR193282534
Qualified teachers3344
in writing a macro that does the following.
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
I have a spread sheet with Column A = Document number, Column B = revision.
I am trying to get a macro that, when run, increases the revision letter in column B. The problem is that we do not use a few of the letters such as I, O, P and Q.
I thought it would be best to have a table of the used revision letters (Say in Z1 to Z40)
Look at the text in column B of the row currently selected and find it in the Z1:Z40 table
Move one cell down the Z1:Z40 table and copy that text back into column B on the row initially selected.
I have a excel workbook, which sheet2 (vehicle arrangement) is master data sheet. which column headings are match with other worksheets. whenever I put the cell value of any multiple rows or column, the cell value (number) and rows (text) are copy or show in the particular sheet which name match with column head.
Sample:
Customer Name
ORDER
MMT
TIL
VBG
JKL
SSL
RDF
ERG
RFG
ERRT
WC
ARS
YUH
TOTAL
M/S XYZ
50
[Code] .......
Worksheets are names as column names is here.. "MMT","TIL", "VBG"........"YUH". So whenever I give the cell value under the column head the same value and corresponding row (customer name) should be show in the particular column head worksheet.
I have an array of data with multiple codes stored in text...
I want to set a formula in a cell to look for all instances of specific text in an array. Then compare the value of all of the column headings, which are numbered across the top of the array, which contain a match in that column and return the highest value.
Example: 10x10 array, columns 1,3, and 5 contain "text" somewhere in those columns. Formula would return 5.
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
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 two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
If you open the attached file you'll notice that once clients are entered into the name field of the master worksheet that a new worksheet is created with their name based off a master template. What I need now is a way to create a "on-demand" "click" macro that will allow the user to select a month (Jan-Dec) based on my master tab and autocreate a summary worksheet named that month with each client listed on the Team Roster worksheet in the next available column of the worksheet that was just created.
macro generate a prompt to list the months to allow updates frequently.. so if the same month was run twice it would overwrite the previous.. the woman we're doing this for isn't very excel literate...
NOTE: As you enter names on the Team Roaster sheet it will autocreate tabs.. test file has MrExcel as the worksheet, if deleted the sheet will delete automatically.