Insert Rows Based On Criteria Match From Sheet2
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:
Sheet1 looks like this:...................
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet. I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
View Replies!
View Related
Insert Rows By Criteria
i need a macro for inserting a row in sheet by criteria. the Criteria: 1-checking if Collumn G ( letters ) are equal if not then inserting the row 2-cheking if collumn H ( numbers ) are equal if not then inserting the row one more thing i need the row to be yellow from collumn B .. to .. I
View Replies!
View Related
Copy All Rows With Criteria Match
I am trying to write a code to copy rows from sheet 1 to sheet 3 if cell of column A "account #" on sheet 1 are matching cell of column A (account #) on Sheet 2. If multiple entries find on sheet 1, (e.g. acct # 101c shows twice) copy all matched rows to sheet 3 as well.
View Replies!
View Related
Insert Row Macro Based On Cell Criteria
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do. Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest. Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN. There could be 100 or 1000 transactions generated based on the day.
View Replies!
View Related
Deleting Rows That Dosent Match The Criteria
i have a speadsheet with 2 columns 1 good work 2 good 3 excellent 4 needs improvment i need a macro that goes through each row and does a search in column B for a partial match of a word. if that word isnt present in the row, the row is deleted. if i typed in a criteria of "good" rows 3 and 4 are deleted. so my speadsheet will then look like this 1 good work 2 good
View Replies!
View Related
Moving Rows Into A New Sheet That Match Criteria
My knowledge of Excel is quite basic, so i had little luck searching for this one. We have a main data sheet in which everything is inputted, but i would like that data to be split up and reported (copied into) in 2 or 3 other sheets, depending on a single criteria. Is this able to be done so these 'report' sheet are constantly updated, and the columns in the reports dont include irrelavant columns which are in the main data sheet.
View Replies!
View Related
Delete Rows That Dont Match Criteria
I have a file which has some data I want to keep and some data I want to strip out. The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove. Example file shown below 23-Apr-0812341234123412341234 30-Apr-0812341234123412341234 19-May-0812341234123412341234 27-May-0812341234123412341234 > 1 year12341234123412341234 Overdue12341234123412341234
View Replies!
View Related
Find Closest Match Based On Two Criteria
I have a sheet of pump test results, placed in rows, where the test rig is set at a specific speeds and flow and it records pressure. At each stage of the test a number of snapshots will be taken (Usually between 3-5 at each stage), so I have 3-5 rows of very similar data. I only need to use the best row from the selection available, so I am hoping to find a formula or code (Don't care which) that can look at the results and select the row of readings where the speed AND the flow are closest to the targetted data. For example: In cells G6:G8 I have flow result values 0.129, 0.151, 0.156 and in H6:H8 I have speed result values 72.536, 71.82, 72.13. These are actuals. In cell N6 I have the target speed (75) and in O6 I have flow target (0.15), but these could be moved anywhere to suit really. What I was hoping to do, but am struggling big time, was to just flag up the row of readings where these two values are closest to the targets by placing a check mark in column L:L which I have formatted in monotype sorts and would like to place a "4" (Which gives me a check mark) on the 'best' row. From that I can do more with some simple IF statements. Is this feasible, or am I trying to do something beyond Excels capability, it is certainly beyond mine at the moment. I was looking at the check mark method, but that is not essential, so if anyone has any suggestions on an alternative means of identifying the best row of data, that might make it easier to do, then I am listening.
View Replies!
View Related
Compare Cells Across 2 Sheets & Insert Rows If Criteria Met
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to. (example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.) To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3. I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
View Replies!
View Related
Match Two Columns Across Sheets And Based On Criteria Of Another Column
This is driving me nuts. What I have is: Sheet1: ColumnB - number stored as text (Work ID) Remaining Columns: - mixed data, some columsn numbers, some text Sheet 2 ColumnA - number stored as text (Work ID) ColumnB:ColumnAW - mixed data, some columsn numbers, some text CollumnAX - Dollar Values What I want is in Column F (Sheet1) a formula to: if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True
View Replies!
View Related
Copy Rows Where Cells In Column Match Condition/Criteria
I did a search to find a question similar to mine and I found this: Copying data matching a criteria into another spreadsheet This is exactly the same concept I am trying to accomplish, but don't know how to...at all. I couldn't follow the thread either. I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will: 1) First ask which region to display the information for (I would like to create the same pop up menu to choose from) 2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all -if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information 3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land". Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate
View Replies!
View Related
Pull Every Instance INDEX/MATCH Criteria Into Consecutive Rows
I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student. I've attached a mock report up so that maybe it would make more sense. So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas. Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs. So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach. Find the next instance Name & # matches, and pull that into rows 12-16, etc.
View Replies!
View Related
VBA Macro To Insert Horizontal Page Breaks Based On Criteria Of 1 Column
I want to achieve is a procedure that inserts horizontal page breaks at certain parts of the sheet where there is a cell equal to 2. Here is the code I have so far. Sub insert_pagebreak() Dim printbreak_cell As Range Dim j As Long Dim i As Long ActiveSheet.ResetAllPageBreaks Set printbreak_cell = Range("AD1") j = 1 For i = 1 To 100 If printbreak_cell.Value = 2 Then Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell j = j + 1 End If Set printbreak_cell = printbreak_cell.Offset(1, 0) Next i End Sub Everything works until the cell value reaches a 2, and then once it goes into the If statement I get a 'Application-defined or object-defined error' at the below line. Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell.............
View Replies!
View Related
Insert Rows Based On A Value In A Cell
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
View Replies!
View Related
Insert Rows Based On Cell Value
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
View Replies!
View Related
Delete Rows Based On Criteria & X Rows Below
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341. Sub DeleteRows() Column_To_Check = 1 Start_Row = 1 End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row MsgBox End_Row Search_String = "SA341" For Row_Counter = End_Row To Start_Row Step -1 If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then ActiveSheet.Rows(Row_Counter).Delete End If Next Row_Counter End Sub
View Replies!
View Related
Insert Rows Based On Cell Time Value
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time. For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
View Replies!
View Related
Insert Rows Based On Number Of Columns Used
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why. Sub split_For_Database() Dim No_Of_Cells As Integer Dim Start_Cell As Range Dim Cell As Range Dim LastRow As Range Dim i As Integer Dim lRows As Long Set LastRow = Range("C65536").End(xlUp) i = 0 Set Start_Cell = Range("C2") Set Cell = Start_Cell Do While Start_Cell.Address <= LastRow.Address Do While Cell.Offset(0, 1) > 0 i = i + 1 Set Cell = Cell.Offset(0, 1) Loop...................................
View Replies!
View Related
Insert Rows Based On Numbers In Column
I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................ With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!
View Replies!
View Related
Copy And Insert Rows From List To Ws Based On A Variable No
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method: 1) Select and Copy entire row 2 from sheet(1) 2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable. 3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3. 4) loop through all records until complete I have tried writing the code in VBA but am not even getting close!
View Replies!
View Related
Insert Number Of Rows Based On Cell Values?
Column B contains geographical Areas. Column C contains a list of business departments. North Env North Env North Ops North Sales North Sales North Sales South Env South Maint South Ops South Sales South Sales South Sales Etc. The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
View Replies!
View Related
Insert Rows & Headings Based On Cell Value
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
View Replies!
View Related
Insert Rows & Repeat Values Based On Corresponding Cell Value
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above. For example - before macro: Joe Bloggs 2 Adam Wilson 10 Peter Andrews 0 Claire Burrows 6 After macro: Joe Bloggs Joe Bloggs Adam Wilson Adam Wilson Adam Wilson Adam Wilson....................................
View Replies!
View Related
Deleting Rows Based On ListIndex Match
I need some assistance with the code below. I have a userform that has a listbox with 2 columns of information. When there is a match between the value in the listbox and the value in the cell for a given row, that row is deleted. I thought this was very straightforward, but i'm not getting this to work right... Private Sub cmdDelete_Click() Dim Val2 As String Dim Val3 As String Dim doit As Long Dim roww As Long Dim LastRow As Long Val2 = ListBox1.List(ListBox1.ListIndex, 0) Val3 = ListBox1.List(ListBox1.ListIndex, 1) doit = MsgBox("Confirm that you want to delete the entry", vbOKCancel) If doit = vbOK Then
View Replies!
View Related
Delete Rows Based On Partial Match
I currently have a speadsheet that looks similar to the following: REF DATA1 DATA2 SVRW_67 784598 475395u SVRW_34 fdsjsjdf 734978 CAT_56 ghdrhad gaghadh CAT_67 48578 8943539 I'm trying to create a macro that will delete all rows with "CAT" in the REF column. I've searched this site and struggled trying to adapt other methods listed and got nowhere.
View Replies!
View Related
Copy Rows Between 2 Sheets Based On Cross Reference Match
I 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.
View Replies!
View Related
Using Macros For Custom Filter Giving 'Or" Criteria And Paste The Data In Sheet2
When I go one column and Click custom filter and give the command one number and or another numbers ( I Have attached an excel sheet with screen shot) This filters the data, and I need to copy the same and paste in the next sheet. I have to do like this for about 20 times for 20 sets of data). I have already done this and pasted the data in sheet2. I did everything manually. ( sample sheet is attached) I need a macro to do this work for me. When I run the macro If get 2 text boxes I can enter the numbers. and click ok,the data has to filtered in sheet1, and result has to be pasted in the next sheet.with the header. Again I will run the macro i will give 2 numbers and the result should be pasted in sheet 2 after the 1st set of data leaveing one row as blank. ( exactly like the sample data in sheet 2). If I run the macro for 10 times giving 10 different numbers, the result should be pasted one after the other in sheet 2.
View Replies!
View Related
Delete Rows Based On Criteria ..
I am a pharmacist that runs reports and I need to sort and exclude data It is in Excel 2003 format I run a report that includes a patients name in one column, the medications name in another column and if the medication was withdrawn, wasted, returned or restocked in another column. Withdrawn, wasted, returned or restocked are treated as different functions and each have their own row entry. Patient and medication name are the same in all situations I need a macro that will look at the patients name and medication name, see if it is the same and delete all rows that have a withdrawal with an associated wasted, returned or restocked.
View Replies!
View Related
Hide Rows Based On A Criteria
What am I doing wrong here? I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.
View Replies!
View Related
Delete Rows Based On Criteria?
I had a raw data sheet in which i need to prepare a statement just like the attached worksheet. My requirement is to delete all those rows in the department column except the department which starts with "C" Just like "CNN" & "CNN-IN". Rows with data containing the words starting with "CNN" should not be deleted .The rows can contain words with "CNN" or "CNN*"(here * denotes anything after the word CNN) I had just formatted the whole worksheet for easy reference.Actually the raw data is extracted from other program which is very clumsy & irregular. The department column might be in any column.
View Replies!
View Related
Delete Rows Based On 2 Criteria
Is there a vba code that can delete the entire row based on two criterias? If a row has a location of Canada and is a female, then delete the entire row. Name Location Gender Alan Mexico Male Dick USA Male Sharon Canada Female <-- This will be deleted if based on criteria Mike Canada Male
View Replies!
View Related
Move Rows Based On A Criteria
Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2" I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B
View Replies!
View Related
Formula To Calculate Price Based On Sheet2
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged. Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution. Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View Replies!
View Related
Re-arrange 1 Column To Rows Based On Criteria
I'm trying to rearrange my data so that a column of info for each unique value in another column becomes a row next to that value. Example: A 1 A 2 A 3 A 4 B 66 B 77 B 88 C 10 C 15 C 20 C 25 C 30 should become... (6 columns) A 1 2 3 4 B 66 77 88 C 10 15 20 25 30 I had a really hard time figuring out what to search for (and in writing a description) for this one.
View Replies!
View Related
Copy Rows In Range Based On Criteria
which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5) But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere? Private Sub CommandButton1_Click() Dim rngFind As Range Dim strFirstAddress As String With Sheet4.UsedRange Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues) If Not rngFind Is Nothing Then strFirstAddress = rngFind.Address Do rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0) Set rngFind = .FindNext(rngFind) Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress End If ..................
View Replies!
View Related
Delete Rows Based On Two Criteria Within Each Row
I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool. The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error). The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...). What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as: "T" "[" "T" "#" "T" "@" "T" "]" The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ). I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).
View Replies!
View Related
Copy Rows Based On Multiple Criteria ....
I am trying to loop through a column and cut and copy cells containing one of ten or more criteria (text strings) to the next worksheet, which already exists. The basic loop is set up, and I understand that an array for the multiple criteria is needed but am falling at this hurdle, as well as struggling with the routine to copy the row to the next available row on the next worksheet. I have omitted to post my effort so far, as it's embarrassingly basic, but can anyone help with the code?
View Replies!
View Related
Copy Rows From One Workbook To Another Based On Criteria
I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook. This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.
View Replies!
View Related
Find A Value Based On Criteria In Columns And Rows
I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20. In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?
View Replies!
View Related
|
TRACKER
