Macro That Duplicates Lines Based On Value Of Column?
Aug 22, 2014
I need a macro that duplicates lines based on the value of a Column. So in Attached excel D1 is 10 - I need it to create 9 entries for that line, so there are 10 total duplicate lines.
I had a Macro that worked that is in the document, I even had everything set up the same, which it is not now, but could not get it to work.
A Macro or a Finished document is fine, Just need this to produce raffle tickets for the church
Raffle Tickets.xls
View 2 Replies
ADVERTISEMENT
Jan 8, 2008
in column A i have account name but it can be up to 4 lines (rows)
in column B i have the fund name which goes with the first account name row
so...
View 9 Replies
View Related
Jan 21, 2010
For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.
View 4 Replies
View Related
Jun 9, 2014
Basically I want to copy and paste all data in a row if the value in a cell is equal to something
I am currently using the below formula
The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).
But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.
So I am looking for a way i can add multiple values each going to separate sheets.
View 14 Replies
View Related
Apr 3, 2013
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I would need an excel formula not macro
View 9 Replies
View Related
Jun 5, 2014
Excel 2003 - delete duplicates based on value in one coloumn.
very simple but how!i want the whole row deleted where there are duplicates in one column but ONLY the duplicates, so, if they're 3 duplicates in the column i want 2 deleted leaving 1 unique record.
View 4 Replies
View Related
Dec 6, 2013
I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:
A
B
C
D
[Code]....
[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]
I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:
A
B
C
D
E
[Code]....
If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be
View 6 Replies
View Related
Jan 30, 2014
I have a data set of property sales that represent properties sold twice over a period of time (each address is represented twice in one column but they are 2 different transactions and the remainder of the row data varies). I am looking for a formula or method to remove the duplicates, but I want to remove the duplicate that sold for less.
The entire row is not a duplicate, only the address. There is another column with sale price and I would want it to keep each address of the 2 (or sometimes 3) that is highest. I intend to use a pivot table to analyze and realized that the data is skewed because everything is showing up at least twice.
View 1 Replies
View Related
Jul 25, 2011
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
View 2 Replies
View Related
Mar 4, 2014
So I have the macro written, but I don't get the complete data that I am looking for. This is what I am trying to achieve
Here is an example of what I am trying to achieve. This is in the first sheet:
Column A Column B
Car Saw
Dew Jacket
Pen Key
Saw Screen
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
Now I want this in the next sheet:
Column A Column B Column C Column D
Car Saw Screen Sock
Car Saw Screen Mouse
Dew Jacket Window
Pen Key
Saw Screen Sock
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
In my current macro, It displays most of what I am looking for, but not a duplicate ID if there is one. For example Screen, there are 2 IDs. My macro is only getting the first one and then skipping over the second ID. I would like it to where if there is a double or if it is there more than once that it gets all of the data.
Here is my macro.
[Code] ....
I will also attach the document of what I am looking for : Data 1.xlsx
View 4 Replies
View Related
Aug 13, 2008
I have an amount of data split into various fields. I need to do something similar to the following:
In each row I have three fields separated by columns: name, sector and specialty. There is only one name and one sector for each record, but possibly several specialties (of which there are fifteen types in total, so they do not appear in a consistent column. specialty 'i' could appear in columns 3, 4, 5 through to 17. ie, sample entries below:
sheet 1
a1 name a2 sector a3 specialty i a4 specialty iv
b2 name b2 sector b3 specialty ii b3 specialty iv b4 specialty x
c3 name c4 sector c5 specialty i c6 specialty ii
The thing is, for every specialty in a row, I need new records created, so the above would become:
sheet 2
a1 name a2 sector a3 specialty i
a1 name a2 sector a3 a4 specialty iv
b2 name b2 sector b3 specialty ii
b2 name b2 sector b3 specialty iv
b2 name b2 sector b4 specialty x
c3 name c4 sector c5 specialty i
c3 name c4 sector c6 specialty ii
View 9 Replies
View Related
Mar 25, 2014
result of command is like below:
Code:
Entity,DATE,A_C,A_N,A_T,A_C,N_C,PER
YYY,24/03/2014,222 , 0002,ALARM TEXT DIFFER IN TIMEFORMAT , 1, 1,3.03030303
YYY,24/03/2014,333 , 0001,SCRIPT FAILED , 31, 6,93.9393939
YYY,24/03/2014,111 , 0003,ALARMS NOT RECEIVED , 1, 1,3.03030303
Entity,Sub-E,DATE,A_C,A_N,A_T,A_C,N_C,PER
YYY,9999992867290,24/03/2014 ,333 , 0001,SCRIPT FAILED , 7, 1, 100
YYY,9999992813525,24/03/2014 ,333 , 0001,SCRIPT FAILED , 3, 1, 100
[code].....
Now I'm looking is when opened in Excel, it should create a two sheets excel file 1) with the First Section and the 2nd with second section . based on headers included in file.which then will be used for PivotCharts..in 2nd stage of automating..
View 3 Replies
View Related
Oct 22, 2008
I have to export some very large stock files into excel, which produces a stock list. However, the list is massively clogged by lines where there is zero stock. What I want to do is tell excel to remove all lines where a zero is found in column g (bearing in mind, there are breaks in the exported report where there is no data at all in some rows).
View 9 Replies
View Related
Feb 13, 2014
I am calling sub B from sub A and want to skip some lines in sub B (only when it is called from sub A).
View 3 Replies
View Related
Apr 4, 2009
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
View 27 Replies
View Related
Apr 7, 2009
I'm trying to make a task tracking spreadsheet. Each part (or project) that I'm working on has it's own sheet. On those sheets, in a specified column (let's say column A for now) I would like to add a tag or date that I can use to create a summary sheet. The summary sheet would have a macro that looks through each sheet in the file and if the value of the tag matches a specified value, the summary sheet would be populated with everything in each row that has that value for the tag.
View 4 Replies
View Related
Mar 17, 2014
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
View 1 Replies
View Related
Jan 25, 2013
I'm looking to populate my Helper Column with all duplicates from my Data Column. Please note that my helper column needs all instances of a duplicate.
Data Column
Company ABC
Business DEF
Corporation GHI
Business DEF
Company ABC
Business DEF
Helper Column
Business DEF
Company ABC
Business DEF
The formula needs to be dynamic and it will need to be able to be copied down.
View 9 Replies
View Related
Jan 8, 2014
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek
into this-
_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.
View 8 Replies
View Related
May 26, 2009
I'm having an excel sheet with addresses. I need to copy some lines of the first sheet to the other sheet. Between the lines I want to copy there are 7 lines which shouldn't be copied.
I created the following macro which does the job: ...
View 11 Replies
View Related
Jun 4, 2007
I am having trouble writing a macro that will draw a line relative to what cell is currently selected. I can only get the macro to draw a line in one specific spot.
View 9 Replies
View Related
Mar 16, 2008
I have 3 different sheets named:
USA
UK
DK
and 1 sheet named:
I would like to have a macro written that when runned will delete all data in line 2 to 1000 in the first three sheets and line 2 through 1500 in the compilation sheets
View 9 Replies
View Related
Jun 14, 2009
Is there a possibility to automate a grouping process i have to do every month? The rows that have to be grouped change every month.
Grouping is done based on the contents in column C. In here i have the following values: Category, Segment, Type, Brand.
For example, column C looks like this:
Category
Segment
Type
Brand
Brand
Type
Brand
Brand
The grouping needs to start from row 100 down. Usually the maximum number of lines to group is 500...
I always do the grouping for Brands first, then Type, then Segment and then Category.
View 9 Replies
View Related
Aug 28, 2009
I want to draw a (time) line from the top left corner of a cell (say C3) to top left corner of a cell (say i10) in a Macro. Both start and end cells (column & rows) will vary depending on certain input values which I can convert into a cell reference. Line must overlap on both ends by two column widths.
View 9 Replies
View Related
Oct 2, 2006
I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search
Sub loopy()
Dim rngTemp As Range ' the range variable declaration
Dim intCounter As Integer 'counter as integer
' the following loop goes through all the cells in column a up until the last used cell in the column
For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp))
'increment the counter if the cell has value fridge
If rngTemp = "Fridge" Then intCounter = intCounter + 1
Next rngTemp
MsgBox "There were " & intCounter & " cells equal to Fridge"
End Sub
View 2 Replies
View Related
Apr 3, 2012
Is it possible to put in Trend Lines when creating a stacked column chart?
I've tried in Excel 2003 and 2010 but it doesn't appear that it is possible.
View 3 Replies
View Related
Dec 18, 2013
I am trying to generate a column chart with a high-low range overlay so that it looks like error bars. I've precalculated all values but cannot figure out how to add the high-low range lines. The ranges are in the min and max columns.
I tried using custom error bars but it did not work.
View 4 Replies
View Related
Aug 7, 2014
I have another report which needs to be sorted using a macro. However, before I can sort the data, I need to delete rows at the bottom of each report that have "0" values in column A and B. Again, each report I run is of different length so I need to write a code that will take that into consideration. Here is an example of what I need to do:
Sub second_step()
'
' second_step Macro
'
'
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("7876:7876").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
End Sub
Now, the thing is, not every report will start having "0" values in row 7876. How do I write a code to reflect this?
View 9 Replies
View Related
Nov 4, 2008
I have a problem with multiple, similar formulas in a macro. Each of these formulas, depending on the active cell, selects a certain range of cells and prints it.
The problem is, no matter which active cell I'm on, the macro activates all lines of the formula and separately prints each range. Is there a way to "separate" these lines in the formula so each range will print separately, depending on the active cell?
Here's the
View 8 Replies
View Related
Apr 16, 2008
I have a question for you that can be much helpful for me. I prepared a macro which is collecting many datas from different pages and entering them on a sheet. Some lines of this sheet are with data and some are empty. I would like to write a code, which will delete the lines in which there is no data. But I would like the code to do this between line 5 and line 225.
View 9 Replies
View Related
