what I need is to insert the data in a cell, and instead of copying this same data to other cells in the same sheet or in other sheets, I need a way so that once I insert the data in one cell, it will be copied to the other cells at once.
For example, if I insert data in the cell [Sheet1,A1], it will be copied to [Sheet2,A4] and [Sheet3,E8] and [Sheet3,I7]
I am writing a simple VB app to allow my analysts to export data from a SQL table into an Excel spreadsheet. I have the following
lngRow = lngRow + 1
Do While Not rs.EOF
lngCol = 1
For Each objField In rs.Fields
objExcel.Cells(lngRow, lngCol).NumberFormat = "@"
objExcel.Cells(lngRow, lngCol).Value = objField.Value
lngCol = lngCol + 1
Next
rs.MoveNext
lngRow = lngRow + 1
Loop
The code works perfect... except... it takes far too long (as one would expect from iterating through 11,000 records cell by cell)
Is it possible to set the values for an entire row with one statement (or even better, the whole sheet at once), like maybe I have a string that is tab delimited or something
I am trying to a input a list of cells in a column depending on what option i select from a drop-down list (or validation list) eg. If in A1 I select "fruit basket" (from a drop down or validation list) I then want this option to input in Column B Items located in another dynamic range eg. Apple, Banana, Orange, Grapes.
I have attached a sample of what I am trying to do to help explain my situation.
I find these forums a great key to learning the intricacies in Excel.
I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .
Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.
This is a reformulation of this post: [URL] ...........
I've been using the following macro ....
View 8 Replies View RelatedI have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
I have a string of numbers ex. 1234, 5678, 91011 in 400 rows. Before each string I would like to add 88.
View 4 Replies View RelatedI am trying to write a formula to insert in a macro for multiple lines of data.
Column B contains the date that I am comparing the date in Column C against. I want to be able to highlight the content on that row if the date in column C is greater than or equal to column B. how to write this?
Sample data:
3/12/20123/12/2012
3/12/20123/1/2012
3/6/20123/6/2012
2/29/20123/2/2012
I would need row 1 and 4 to highlight in red. this is part of a long Macro that is written and includes many other steps, but I cannot seem to make this step work correctly.
I need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.
My starting data looks like this:
HEADER
DATAROW1
DATAROW2
DATAROW3
-emptyrow-
HEADER
DATAROW1
DATAROW2
DATAROW3
I need it to look like this:
HEADER
DATAROW1
-emptyrow-
DATAROW2
-emptyrow-
DATAROW3
-emptyrow-
[Code] .....
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
Input
Count Value
1 400
2 101
3 300
4 450
Output
Count Value
1 400
2 101
20
3 300
3 0
3 0
4 450
4 0
4 0
4 0
I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2
I have posted in a existing thread at mrexcel as i believe my problem and possible solution is very similar to that already detailed there. However i have not recieved a response there yet and was looking to draw upon more minds here ...
View 9 Replies View RelatedMy cell contents looks like this.
H1111111111111
I want it to look like this..
H11-111-111111-11
I know this can be done by formula but i dont know it. This info is in col B.
I have two spreadsheets in Excel 2003.
Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.
When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.
I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.
I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
I am trying to ask excel to look at the information that appears in certain cells,add it together and then divide it by another cells data. The problem is the info to add up is not all together so i cant say, for example, =SUM(A1:A5). The cells I need info from are, however, in a pattern of every 8 rows. Example:
C5,C13,C21,C29. Rather than have to type every cell into the formula I am sure you clever people have an easier solution!!
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedIs it possible to combine datas from over 200 cells (all cells are from the same row) in one cell? I have tried CONCATENATE function and e.g. =A1&B2&... but is there shorter formula for this?
View 4 Replies View RelatedData layout in this excel sheet. I need to break it up properly in order to have a better way to import it into SQL. Is it a good idea to cram multiple data points into each cell. From the DB POC column, i need to separate that which is before the "/" from that which is after the "/" and put each item into it's own row. So there needs to be a new column for "DB" and a new column for "POC". The value before the "/" will go into the "DB" column and the data after the "/" goes in the POC column, with one new row for each item.
View 9 Replies View RelatedI attached sample file with data I received (more than 50k rows in each file) is contained in 86 files with varying population lengths in each file that needs to be broken down as shown in the attached file.
View 5 Replies View RelatedI have a spreadsheet where I am trying to add up codes and quantitys, after this I want the repeated info deleted. For example;
12.004RWHB 4 Row Wooden Handle Welders Brush
12.004RWHB 4 Row Wooden Handle Welders Brush
20.004RWHB 4 Row Wooden Handle Welders Brush
24.004RWHB 4 Row Wooden Handle Welders Brush
4.004RWHBSS4 Row Wooden Handle Welders Brush S/S
10.004RWHBSS4 Row Wooden Handle Welders Brush S/S
12.004RWHBSS4 Row Wooden Handle Welders Brush S/S
So what I want to do is add the numbers up so I can get one value. For example, 4RWHB I want to add them all up so that it totals 68 still shows the code and description but then only shows 68 4RWHB 4 Row Wooden Handle Welders Brush and not the same line repeated. I want to condense all the information down so where there is same model numbers I can add it up and it doesn't give me multiples but summarises it down into one line per model number.
In column A i have values that may repeat throughout Column A, then next to the value in ColB, there is a sequence number 1,2, 3 for each of the same value in ColA. Then the Text in ColC that i need.
What i Need is to be able to search ColA, Then get the correct Sequence number from ColB, and place the contents in ColC into ColD or into a new worksheet if necessary.
I need to prepare a lot of data for upload from Excel to a system. Example, In cell A1: I have very long text that I need to parse into B1, C1, D1, etc, depending on the length as each cells can support up to 40 characters only.
It seems easy as I can just find the length of A1 and use mid to parse them to the rest of the cells. But the sales person wants the data to be readable, meaning that I can't just split the data simply based on the character limits.
I have 1 column with a varying amount of rows (around 2000+ rows) with data that is about 10 chars long. Since I cannot print it all on a few pages, I would like a way to re-orgnize the data into cells so I can print on as few pages as possible such that the qty of columns isn't wide then an 8.5 x 11 page.
One idea is the following.
Here's what i have.
Col1
1
2
3
4
5
6
7
8
9
10
...
Here's what i want.
Col1 Col2 Col3 Col4 Col5 Col6 Col7 ...
1 2 3 4 5 6 7
8 9 10 ...
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
View 2 Replies View RelatedBackground: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
I have a sheet labled Parsed and a sheet labled Prices. The Data on the Parsed sheet is in W2:W1000. the data on the Parsed sheet, can have anywhere from one value to up to ten values in it. (again all sparated with a space.)
the data is separated by a space between each number value. I am trying to find a way to take the values from Parsed!W6 for example and separate the data into the prices sheet in to different columns. Some cells may be blank.
Example:
PARSED SHEET.
W
X
1
Equipment
[Code] .......
I'm building an inventory report based upon a daily report downloaded from a website. The downloaded report changes from day-to-day. All of the data I need remains there, but the location of the columns changes, with the exception of "BOL". I need to sort all of this data in a macro. I have most of my macro figured out, but I'm having trouble with one "sort". I need the data to be sorted FIRST by whether or not the 4th column contains "Clear" or "Dyed". I have the next two sorts already coded, based on SPOT vs Contract and then numerically by column A. But I cannot find macro coding or an excel formula (either would work) that reliably searches the 2nd column for the word "Clear". I have tried different combinations of IFERROR, FIND, SEARCH, IF, ISNUM, etc. in hopes of writing a new adjacent column containing the values "Clear" and "Dyed". I have found many combinations that work if I search only the correct cell. But when I extend the range to search through ~26 cells in one row, they all fail. Below is a (small) example of what I start with and what I want to end with. I've also included my current relevant Sorting code in my macro.
FinPro
Sup
Veh
1Dn
2Cn
2Dn
Bio
Injec
[code]....
I have about a thousand Excel timesheets that all contain 'hours worked' data in a column. Each row contains the area of the project they have worked on and therefore the amount of time they have spent on it. The timesheets also contain the person's name and a w/e date.
I want to sequentially work through each timesheet held locally in a single folder and copy the person's name, w/e date and the hours held in the column into a single new spreadsheet. I need to transpose the data so each amount of time spent on an activity ends up in a column.
I have attempted to record a macro for this but each time I try and run it after the intitial run, it moves to a completely different cell or set of cells to the ones I have directed it and consequently there is no data copied to the new sheet.
I believe this is the first problem..! The second is working through a high number of spreadsheets held in a single locattion but whilst browsing this site I saw the "Excel VBA Loop Through a Folder of Excel Workbooks" page and think this should work fine.
Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.
View 1 Replies View Related