How To Add More Rows From Existing Set Of Values
Feb 26, 2014
I have a table with 2 columns and I want to create another table with the same values in these 2 columns but add a new column with values that are different. So for example, I have col A, Col B with values 256 and 14. I want to create a table that has Col A, Col B and Col C with 256 in Col A appearing 60 times, 14 in column B appearing 60 times and new Col C has values ranging from -4 to 55 for each new row. So new table now has 60 rows with same values for Columns A,B but different C value
I can drag or copy paste but when I have 200 unique records in first table, I want to create a second one with 200x60 rows and drag/copy paste is tedious.
Input
ID
SqnID
256
14
[Code]....
View 2 Replies
ADVERTISEMENT
Jun 26, 2013
Everyone always want to get rid of duplicate rows.
Is it possible to create duplicate rows of all the existing rows?
View 5 Replies
View Related
Mar 26, 2007
I have a button setup with a macro to copy cells in a column and paste them into a row on another sheet. One of the values in the column needs to be checked in the other sheet before being added.
After the button with the macro is clicked
- If the value entered in column A (sheet 1) exists in column A (sheet 2) proceed with the rest of code.
- If the value entered in column A (sheet 1) does not exists in column A (sheet 2), Popup menu to enter details of value (just a menu with an input box and instructions), then the macro should enter the details of value (entered by user into the popup menu) in column B of sheet 3, then proceed with the rest of code.
I posted this with a different explanation before and didn't get a response so I am hoping that this is a thinned down version of my request and is more specific. Please let me know if you need more info.
View 9 Replies
View Related
Nov 30, 2009
need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
Rows("2:2").Select
Do
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select
Loop
View 5 Replies
View Related
Aug 7, 2009
Is there any way to automatically add two new rows after every existing row containing data?
For example
1 A B C
2 D E F
3 G H I
turn into
1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I
View 9 Replies
View Related
Mar 17, 2009
I need to replace the values in the braces with the values in Column A. Is there an easy way to do this or am I stuck with doing each one manually? There are 92 lines in Column A.....
View 9 Replies
View Related
Mar 9, 2009
I have a column with blank rows inbetween rows with data.
Column Heading
1000
Blank
Blank
1001
Blank
Blank
Blank
1002
Is it possible to autofill the blank rows with the row that has a value above them, have it skip the next row with a value, recognize the next blank cells and fill them in with the previously skipped row, and so on down the line? How might I go about doing that?
View 4 Replies
View Related
Dec 26, 2011
I have an excel list of My Existing Customers and have recently purchased an excel list of all possible customers in my market that happens to have my existing customers listed in it as well. How can I remove my existing customers out of this purchased list so that I can import it into my Database as Prospects? I'm using Excel 2010. Deleting duplicates doesnt work for this. I want a function that looks at data in one spreadsheetA and if it finds it in the second spreadsheetB, it deletes the row out of SpreadsheetB.
View 9 Replies
View Related
Jun 17, 2008
I have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:
Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
Worksheets.Add
On Error Resume Next
Worksheets("ChartData").Delete..........................
View 3 Replies
View Related
Sep 1, 2013
I am trying to write a macro that should be able to copy existing row and insert values and formula into a new row just below the row. As an example below:
A
B
C
D
E
001
ADESF
500.00
1001210
EMF
The values in column C and D for the new row will be the same.
View 1 Replies
View Related
Nov 7, 2011
I have a workbook with several tabs. the first tab is a data input sheet: subtotals from this sheet get posted to cells on the other sheets. at the top of the Data sheet is a drop down validation list that the user will select the next month from. On each of the other sheets, is a grid with calendar months down the left hand side and years across the top:
Excel 2007BCDEFGHI26Incoming111009080727Month201220112010200920082007200628
JAN1201135317522050194029FEB1392150817082130217230MAR1538164117831823246831
APR1338132717301819211632MAY1485129615762008244933JUN14991380162618652028187034
JUL15491382166020362091170335AUG16531383148619252132197236SEP15901411155620051982214937
OCT23391324159420732014247738NOV1440146718842186282139DEC15631602203721472197Incoming
I am trying to figure out a simple method to check whether there is data on any of the four other sheets in the spot corresponding to the month selected by the user on the Data tab. the format of the validation list dates are dd/mm/yyyy in cell B2 on the data tab.
View 3 Replies
View Related
Nov 5, 2012
I have a Multi Select ListBox with values from a range of cells.
I wish to automate the selection of one or more existing value(s) via VBA code so when User views the List, The check mark is visible. This will alert them to Active values.
i.e ListBox:
North
South
East
West.
If the active region is West, The check box beside West is Active, i.e contains an "X"
View 4 Replies
View Related
Oct 28, 2006
I have this example array:
A1
B1
C1
D1
A2
B2
B3
C3
D4
C5
D5
E5
...along with this example table:
.12345
A
B
C
D
E
In my table, I want to notate every occurence of A=1,A=2,B=1, etc; by placing an "X" in the correct cell. So, the table should look like this:
.12345
AXX
BXXX
CXXX
DXXX
EX
I cannot figure out the formula. I've tried this:
=If(VLOOKUP($L6,$I$6:$J$15,2,False)=M$5,"X"," ")
....but it seems to only return the first value found, and not all occurences.
View 3 Replies
View Related
Dec 17, 2007
My problem may seem similar to "conditional sumif", but I could not solve it with that approach. The examples of sumif or sumproduct always use
E2:E25="Low"
notation, in my case I need a range on the right hand side of that comparison. I have several brands which belong to some producers. I'd like to sum the values of all brands belonging to 1 producer. The brands and their producers are listed on the same sheet. An example is attached.
View 6 Replies
View Related
Jan 26, 2009
I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.
These are the instructions I'm currently sending:
In cell n1 enter:
full phone number
in cell m2 enter:
=RIGHT("00000000" & J2,8)
Drag m2 to last record
In cell n2 enter:
=CONCATENATE(61,I2,M2)
Drag n2 to last record
In cell o1 enter:
Date of call
in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))
View 6 Replies
View Related
Feb 3, 2012
I need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.
Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.
The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.
Code:
Sub Macro1()
On Error Resume Next
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
[code].....
Is there a shorter way to handle this code-wise?
View 3 Replies
View Related
Jun 7, 2012
I have a table in which the rows represent cities and the columns represent certain characteristics/ attributes a city can have. In the cells every city & attribute pair receives a "grade"
City EatingBeachSki
Barcelona 210
Chamonix 002
I would like to transform this so that every row represents a city & attribute pair and includes its corresponding grade.(I want to do this in order to be able to add more information about city- attribute pairs rather than about cities only)
City AttributeGrade
BarcelonaEating 2
BarcelonaBeach 1
BarcelonaSki 0
ChamonixEating 0
ChamonixBeach 0
ChamonixSki 2
I am aware that I can use a pivot table to sort out all the pairs with a specific grade. and then I could paste this into a new table. But my data base (number of cities x number of attributes) is huge and I was wondering if there is no automatic process to make this transformation.
View 4 Replies
View Related
Feb 22, 2008
I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
View 9 Replies
View Related
Mar 24, 2014
I have a workbook which contains 8 worksheets. I want to able to run a macro that looks for specific words in column A of each worksheet and hides any rows in between the specified words. The following code works except if a worksheet does not contain the specified words. Is there a better way to accomplish this?
Attached is an example of the spreadsheet. The code below works just fine on the tabs highlighted in green, but halts on the tab highlighted in red. The tab highlighted in yellow is showing you the rows I need to hide.
Example.xlsx‎
View 5 Replies
View Related
Mar 12, 2014
I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
View 1 Replies
View Related
Feb 28, 2014
I have a file with many different row sizes and after calculating the figure, we will need to add up the total for a particular job. Due to the irregular data on each job (some with only 1 line item where sometimes the job have few line items).
The cell to sum already has a formula for the data.
For example, I already have a formula on Column L to get the figures. After few line items, I will need to add a total to the lines item, also on Column L. I think we can uses 2 sets of data from 2 different column as "pointers", Column D and the formula column itself (but I don't know how to). The columns that I need a sum function will be from Column L to X. After doing the sum, can we at the same time draw a sum line? (single top line and double bottom line).
Am I being "too greedy" ? Imagine if we have more than thousands lines of such data to be added manually and to draw these lines manually.
View 3 Replies
View Related
Feb 5, 2014
I have used a formula to achieve this before which seemed to work although now it seems to be replacing other data already in the worksheet so I am unsure of how to fix, thinking I should just start again.
The file in question is a tracker of activities, Column A (sheet1) is the Status and once the status is "Closed" I need to run a macro that copies all closed activities to Sheet5 at the bottom of previous closed activities. Once this is done I already have a macro to delete all "closed" activities from Sheet1 meaning that it is vital the new closed activities stack onto the bottom of previous closed activities.
The Column Headers start on Row 11 with activities running from Column 12.
View 1 Replies
View Related
Aug 22, 2013
[URL]
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
View 2 Replies
View Related
Sep 16, 2013
I'm trying to place a pivot table in an existing sheet in order to have a list of names next to a P&L. I've got several criteria to filter the names, but when I apply the filters to the pivot table, not all the people show up. The weird thing is that the total at the bottom of the pivot table, which is a simple sum of time in a given month per person, calculates the correct number as if all the employees are there.
When I do the same table in a fresh sheet, in the same file, it shows correctly. It's only when I try to put the table in an existing sheet that it abbreviates the list.
View 1 Replies
View Related
Jan 11, 2012
I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.
I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:
Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.
I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.
I'm running Excel 2007.
View 5 Replies
View Related
Jan 6, 2014
I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?
Example.
Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1
I've given the value of cell A1 in Sheet1 a name of dgwd.
How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?
View 4 Replies
View Related
May 14, 2014
I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.
For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.
how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.
View 1 Replies
View Related
Feb 9, 2010
For simplicity sake if I wanted to paste the letter A in to a cell that already had the letter B in it to make the cell read AB how could I achieve this?
View 10 Replies
View Related
Jan 25, 2013
I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:
1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?
I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.
Test Tracking Sheet.xlsx‎
View 4 Replies
View Related
Oct 8, 2006
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
View 3 Replies
View Related
