Macro To Duplicate Row Based On Cell Value
Feb 17, 2011
I need a quick macro to duplicate based on specific cell value in column D. If the value is 3, then I need the line to be copied three times and then change the original value line to an incrementing value.
Starting Data:
A_______|B_____|C_____|D_
CompanyA FacilityB Group1 qty3
CompanyA FacilityB Group2 qty2
CompanyA FacilityB Group3 qty4
CompanyA FacilityB Group4 qty1
CompanyA FacilityB Group5 qty5
End Result:
A_______|B_____|C_____|D_
CompanyA FacilityB Group1 001
CompanyA FacilityB Group1 002
CompanyA FacilityB Group1 003
CompanyA FacilityB Group2 001
CompanyA FacilityB Group2 002
CompanyA FacilityB Group3 001
CompanyA FacilityB Group3 002
CompanyA FacilityB Group3 003
CompanyA FacilityB Group3 004
CompanyA FacilityB Group4 001
CompanyA FacilityB Group5 001
CompanyA FacilityB Group5 002
CompanyA FacilityB Group5 003
CompanyA FacilityB Group5 004
CompanyA FacilityB Group5 005
View 5 Replies
ADVERTISEMENT
Nov 25, 2009
I currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)
View 7 Replies
View Related
Jan 27, 2014
I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
View 2 Replies
View Related
Jun 25, 2009
making a macro that will scan for duplicates, length & empty cells.
I have the sample file below that does conditional formatting but it doesnt help as much because I want to show the reason for the highlight's on a comment instead.
sample file : [url]
file that might help out: [url]
checks would be:
column A - duplicates and/or length should not be over 100 characters
column B - duplicates
column F - should only contain 2 comma's(or 3 keywords)
all columns - check if no entries are found(empty cells)
View 9 Replies
View Related
Nov 3, 2011
a quick macro/VBA project that would allow my to duplicate existing rows based on the count in one of the cells. For example, my data currently looks like this:
DCITEMITEMDESCVENDORCOST ABCSTATUSRCATOHExtended Cost 1st Discount Level 2nd Discount Level CGO6-IAVHORN12/24VDC MINI HORN07697$ 12.69 UA24901$ 12.69 $ 9.99 $ 5.99 CGBK-PR2AEPOXY-ENCAPSULATED RELAY W/ACT20775$ 7.43 SA25203$ 22.29 $ 5.99 $ 3.99 CGSF-SUB901CLEAR 2"SPACER F/STI 910507450$ 7.88 SA25904$ 31.51 $ 5.99 $ 3.99 CG0Q-BLTBWIR21OUT B/W IR BULLET 420TV 3.6M01047$ 66.59 XD31211$ 66.59 $ 49.99 $ 32.99
And I want to duplicate the rows based on the quanity in the OH column (highlighted in yellow) - so that it looks like this:
DCITEMITEMDESCVENDORCOST ABCSTATUSRCATOHExtended Cost 1st Discount Level 2nd Discount Level CGO6-IAVHORN12/24VDC MINI HORN07697$ 12.69 UA24901$ 12.69 $ 9.99 $ 5.99 CGBK-PR2AEPOXY-ENCAPSULATED RELAY W/ACT20775$ 7.43 SA25203$ 22.29 $ 5.99 $ 3.99
[Code] ........
View 3 Replies
View Related
Mar 12, 2013
Every week I get a report that contains 1 or more (sub) part numbers in each row down column E, separated by a space. I need each part on a separate line, with the rest of the data on that row intact. Because these are the sub-components of an overall part, the data in column E is stored grouped together like that.
I currently use text to columns to break those up, separating them by the space, add a count of those cells so I know how many sub part numbers there are, run a 'duplicate rows' macro on that count, then manually delete out numbers so I'm left with one part number per row. The report is already over 100 rows long, so after I run the 'duplicate rows' macro I have a lot of rows to work through.
View 7 Replies
View Related
Mar 1, 2008
I have a number of rows that I want to have duplicated X number of times (and altered) where X is found by looking at certain cells within each row.
There are four numbers in each row, and I want to split them up into multiple rows each with three zeros and one one.
I would like to convert data from this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 3 0 1 0
Mike 0 1 1 2
etc.
...to this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 0 0 1 0
Mike 0 1 0 0
Mike 0 0 1 0
Mike 0 0 0 1
Mike 0 0 0 1
etc.
You can see that the W, X, Y, and Z columns from the four new Mike rows sum to equal the values in the original Mike row (0, 1, 1, 2), but everything has been split so that each row just has a single one in it and three zeros.
Does anyone have an idea of how to do this? Thanks Auto Merged Post Until 24 Hrs Passes;I thought of another way of putting it that may be easier to understand.
Given an input row of "George, 4, 7, 3, 2", I would like the output to contain 4 rows of "George, 1, 0, 0, 0", 7 rows of "George, 0, 1, 0, 0", 3 rows of "George, 0, 0, 1, 0", and 2 rows of "George, 0, 0, 0, 1".
View 3 Replies
View Related
Nov 28, 2013
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
View 1 Replies
View Related
Feb 26, 2014
What im needing is something that will duplicate each row based on Column F as shown below... However I also need the duplicate row numbers added to Column G and then the original row deleted... I will try and show you an example of the input/output i would like to achieve: (also the first row can be ignored it will all be headers)
INPUT
ROUTE
NAME1
NAME2
ADD1
DESC
3
WOOD000001
PW
ID
[code].....
View 1 Replies
View Related
Oct 28, 2009
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:
If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
View 5 Replies
View Related
Apr 21, 2007
I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).
How do I modify the code below to evaluate duplicate data in Columns A through D? .....
View 7 Replies
View Related
Jan 22, 2014
I am currently doing an excel database of students application. I want a macro that creates a duplicate of a template sheet and renaming it based on the name of the student. Meaning once a name appears in the name cell, a sheet of that particular name is automatically created. At the same time, i want all the data regarding the applying student to appear the newly created sheet as shown in the attached file.
View 4 Replies
View Related
Nov 1, 2007
I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.
View 9 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
Mar 15, 2014
I want to sort row based on duplicate value in Column 2 & 3. And copy the content if Column 3 is Self-duplicate.
Hard to explain, something like this:
Column 1
131.74
1.63
12.31
Column 2
375-9323
384-803
267-6491
Column 3
375-9323
NO
384-803
267-6491
375-9323
Result should be like this:
Column 1
131.74
1.63
12.31
131.74
Column 2
375-9323
384-803
267-6491
375-9323
Column 3
375-9323
NO
384-803
267-6491
375-9323
I've been playing with different Macro and search quite a few places but haven't got any luck so far.
View 2 Replies
View Related
Aug 8, 2007
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes.
The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
View 9 Replies
View Related
Jul 30, 2008
I got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)
the second next to it this =COUNTIF($D:$D;$D5)
and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
Example
4905524490831DPFV900B.CEU0649055244908312DUPLICATE
4905524490831DPFV900B.CEU502DUPLICATE
View 9 Replies
View Related
Feb 18, 2009
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies
View Related
Oct 23, 2013
I am attempting to create a ROI calculator for my place of work. I want a blank cell to fill in green or red based a cell below it being equal or greater than 5.0%. What is the Macro or equation to do that?
View 5 Replies
View Related
Aug 6, 2014
I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
View 10 Replies
View Related
Jan 12, 2009
I did a search on this site and found some code I was looking for (see link:
http://www.excelforum.com/excel-prog...e-in-cell.html - Leith Ross's response code).
The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed.
I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()".
I assume I need to modify the code but am unsure as to how.
View 11 Replies
View Related
Feb 18, 2010
I need to filter the following file.
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
View 2 Replies
View Related
May 22, 2014
I have a large spreadsheet that has some duplicates in Row Q. However, in column L there is a date. I want to be able to find the duplicates in column Q then delete the duplicate with the earliest date.
[Code] .....
View 4 Replies
View Related
Feb 6, 2010
I have a worksheet with two columns and a few hundred lines. One has titles and the other integers indicating how many times the title needs to be copied to another worksheet or text file.
Existing Worksheet (Input)
TitleA1
TitleB6
TitleC4
TitleD3
Desired Output (Worksheet or Text File) [The entire row, including the integer could be copied as well, if it is easier.]
TitleA
TitleB
TitleB
TitleB
TitleB
TitleB
TitleB
TitleC
TitleC
TitleC
TitleC
TitleD
TitleD
TitleD
View 7 Replies
View Related
Feb 6, 2010
I need a VBA/macro very similar to several I have seen posted here. I have a worksheet with two columns and a few hundred lines. One has titles and the other integers indicating how many times the title needs to be copied to another worksheet or text file.
Existing Worksheet (Input)
TitleA1
TitleB6
TitleC4
TitleD3
Desired Output (Worksheet or Text File) [The entire row, including the integer could be copied as well, if it is easier.]
TitleA
TitleB
TitleB
TitleB
TitleB
TitleB
TitleB..............
View 2 Replies
View Related
Oct 10, 2011
Currently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
View 5 Replies
View Related
Feb 23, 2012
I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.
View 9 Replies
View Related
May 1, 2012
I have a sheet which has the following Columns:
Name..........Sales..........Returns..........Net
These are columns B,C,D,E,
Because the data is imported, it shows one row for the person's sales and another for their returns like this:
Dave...........100...............0...............(Blank)
Dave..............0.............-20..............(Blank)
Fred............200...............0...............(Blank)
Andy..............0............-500..............(Blank)
What I am trying to do is get the net sales of 80 in either of Dave's rows in the Net column, then I can hide the duplicate row. However, this is complicated by the fact that names without returns, (like Fred in this example,) do not have a second row. (This also applies to those names without sales, but with returns, like Andy).
View 6 Replies
View Related
Nov 8, 2012
I just want to colour duplicate values but want to do it with this Dictionary method
Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary
[Code]....
View 9 Replies
View Related
Aug 29, 2008
I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all.
Example:
DataABC DTom9730-003Company B100 Joe10242-001Company A200 Tom9730-003Company B300 Joe10242-001Company A400 Sam9730-003Company B100 Desired ResultTom9730-003Company B400 Joe10242-001Company A600 Sam9730-003Company B100
View 9 Replies
View Related
