Transfer Single Line With Sum Of Multiple Lines

Oct 16, 2007

I import data into an excel spreadsheet where a unique job number may have multiple costs against it. These import as separate lines, repeating the job number.

The data imports on a sheet called 'DataImport'. From that sheet I transfer some of the imported fields onto another sheet.

What I need to be able to do is transfer the job number and a sum of the costs onto the other sheet.

The data is run regularly and I delete all the fields on the DataImport sheet in the macro each time the import is run.

View 9 Replies

Multiple Line Texts To A Single Line?

Mar 30, 2014

I have a multiple line texts I want to convert it in single line like for Example:

The following contents is to be converted in a single line

contents:

[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on

Result:

[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on

View 4 Replies View Related

Multiple Lines To One Line (Rows To Columns)

Aug 12, 2014

I have is a list of the over 500 people with one line per college degree. Some have up to 4 or 5 degrees. Each have SSN as unique identifier.

I need to consolidate into one like per person with multiple columns one for each degree.

See attached : Test Degrees.xlsx

View 6 Replies View Related

Coply Multiple Lines Into Single Cell

Jul 6, 2004

I have a table in word that I'm trying to copy into excel.

The problem is one of the columns in the row in that table may have multiple lines with hard returns.

When I copy and paste into excel, the result gives me multiple lines. I want a single line, with the exact multiple text in a single cell. How can I do this?? Nobody in the world seems to know.

To try this set up a word table with a single row, 3 columns. In the 3rd column put the following value.

Value 1
Value 2
Value 3

Now copy the entire row and attempt to past into excel. Watch as it creates 3 rows.

View 9 Replies View Related

How To Convert Selected Multiple Lines Of VBA Code Into Comment Line

Jan 29, 2014

Adding ' in the beginning of a line converts the rest into comment line. I wonder if there is an easy way to convert a huge area into comment line to try something on code. I couldn't find such an option in the menu.

View 2 Replies View Related

Multiple Vba Commands On A Single Line

May 19, 2008

Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?

I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:

Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"

View 9 Replies View Related

Single Coding Line Into Multiple Using Underscore

Feb 10, 2010

Single coding line into multiple using underscore
How can i break this up

View 3 Replies View Related

Concatenate Multiple Cells Into Single Cell With Line Feeds

Dec 21, 2007

I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary.

would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.

View 7 Replies View Related

Delete 1 Line After Each 65 Lines

Jan 13, 2010

I have 2 columns in Excel wich havn't the same amount of lines.

One colom is 65.000 lines and the other one is 64.000

I am looking for a option to delete 1 line after each 65 lines.

Is this possible?

View 6 Replies View Related

Line Graph With 2 Lines Per Person?

Sep 15, 2013

I need to create a line graph that has 2 lines for each person, one with the amount, one with the points. The X-Axis is by Weeks going up. How can I do this without making a separate series for each? I've attached an example.

example.xlsx

View 1 Replies View Related

Insert 2 Lines Under Line 6 In The Active Sheet

Aug 27, 2008

I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.

First of all I would like to insert 2 lines under line 6 in the active sheet.

I then need to drag the formulas found in line 6 down over the two new lines.

Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"

View 9 Replies View Related

Place 1 Line Of VBA Code Into Mutiple Lines

Jun 11, 2009

I stumbled on this and wasn't sure why the code was placed on different lines and how the highlighted sections in red affect the basic round function.

ColorCompare = Round( _
(111111 + Blue + Red + Green) / (Green + LenPaint) _
, 0 _
)

View 3 Replies View Related

Line Up 2 Sets Of Information And Delete Unneeded Lines?

May 15, 2014

I have 2 lists of codes with values tied to them, columns A & B, and columns C & D. Column A is a list of the codes in use, and column C is every code possible, I need to line up the codes and shorten column C to match column A, while retaining the value that is tied to each code so that I can compare Columns B & D easily.

View 2 Replies View Related

Adding Grid Lines And Line Numbers In The Report?

Mar 4, 2014

See the code below. It works fine but in the report that gets printed off, it doesn't display grid lines and line numbers .

[Code] .........

View 3 Replies View Related

Excel 2010 :: Line Graph Isn't Charting Lines?

Feb 23, 2012

I have been trying to chart temperature differences over 10 day's time for six cities using a line graph. For some reason, the lines are all clustered at the bottom of my graph, with values of 0. My spreadsheet is accurate, without any blank cells. I'm using Excel 2010.

View 1 Replies View Related

Inserting 1 Line In Sheet1 To Enter 5 Lines In Sheet2

Jul 23, 2006

One has around 129 lines for the first years 2006 in sheet1.
The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years.

How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order.

e.g

Sheet1
Name Rev Year
John �120 2006 Row 5
Jack �150 2006 Row 6

Sheet2 (linked)
Name Rev Year
John �120 2006 Row 5
Jack �150 2006 Row 6
John �130 2007 Row 134
Jack �160 2007 Row 135
John �140 2008 Row 263
Jack �160 2008 Row 264
John �150 2009 Row 392
Jack �170 2009 Row 393
John �155 2010 Row 521
Jack �180 2010 Row 522

The Rev changes by formulas in Sheet 1

View 9 Replies View Related

Hit Tab Button To Insert New Line And Preserve Previous Lines Formulas

Feb 23, 2014

I am brand new and I don't know a ton about macros and programming in Excel.

What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.

Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.

See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx‎

View 5 Replies View Related

Broken Line Chart Code Turning Lines Black

May 24, 2007

creating breaks in the line this script now simply turns the line black.

View 9 Replies View Related

Consolidation Of Workbooks To Single Workbook When Data Is More Than 66k Lines

Aug 24, 2012

I have around 21 excel files in a particular folder all the files column headings are same and but number of line differ from 0 to 50K. Overall all 21 files will have more then 2 Lakhs lines.

i require a code which consolidate the all 21 files in to single workbook. if first sheet in full then data should copy to next sheet, so on. I also require file name in the first column of destination file when files are being copied.

View 1 Replies View Related

Formula To Transfer All Worksheets In Directory (only Single Worksheet) Into One Workbook

Jul 16, 2014

I am trying to quickly transfer all worksheets in a directory into one worksheet listing all worksheet names in the tabs in number order.

The formula have so far is below. But it does not name the individual tabs as the worksheet names in no order.

Sub GetSheets ()
Path = "Y:
Filename = Dir(Path & "*.xls")
Do While filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
Sheet.Copy After:=ThisWorkbook.Sheets (1)
Next Sheet
Workbooks (Filename) . Close
Filename = Dir ()
Loop
End Sub

The worksheets appear as follows 1982-001, 1982-003 up to 1982-250 ( I want them in single workbook but as multiple tabs in number order)

View 1 Replies View Related

Select Series (lines) In Line Graph By Adding Userform With Checkboxes On A Chart

Apr 15, 2014

I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?

View 2 Replies View Related

Excel 2013 :: Merging Selected Lines In Worksheet To One Line - Removing Dupes?

Aug 12, 2014

I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.

As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)

All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:

company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx

I atteched an example of the full list and the result i want

View 9 Replies View Related

Calculations On First Lines For Entire Groups Of Data In Single Column?

Feb 25, 2014

I'm looking to do some simple maths with ranges in Excel but I'd like a way to be able to input all these formulas automatically although I am pretty sure a single formula per column could do it all automatically. I've got to put the sums in AD to AF on the first line of each location, but the maths is for the entire location's range.

[URL]

View 3 Replies View Related

Compiling Data From Multiple Workbook Into A Line By Line Master Schedule

Mar 30, 2013

I have about 180 workbooks which I need to compile into a Master Schedule.

All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?

Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?

View 1 Replies View Related

How To Make Function That Works On One Line Work On Multiple Line

May 19, 2014

I have this function that works on line 3 and if the conditions are met, the result is 1

=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))

Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1

I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,

View 9 Replies View Related

VBA IF And THEN Statement On Single Line?

Feb 14, 2014

Difference between these two statements. The first one works perfectly while the second one returns an error. Also why is the best scenario to use IF and Then on a single line?

[Code].....

2nd one with error:

[Code] .....

View 14 Replies View Related

Convert Macro In 1 Line To Multiple Line Code

Dec 8, 2007

i hv following code

(i use generate macro)

my question is how to arrange the code from one line to multiple like :-

following code show in excel macro environment is one striaght line.

' Create new var on yr , and replace 2006 to CY06.

ActiveCell.FormulaR1C1 = _

View 9 Replies View Related

Single Line Border Around A Cell - VBA

Apr 16, 2012

Why is the VBA for putting a simple border around a cell so convoluted. For example cell A2:

Code:
Sub CellBorder()
Range("A2").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin

[Code] ........

So many lines to put a simple - single line border (square) around a cell.

View 3 Replies View Related

Count Number Of Commas In A Single Line?

Mar 16, 2014

I am trying to count the number of commas in each line of my Excel file. So I first decided to count the total number of rows and then count the number of commas in each row. I wrote this code. But I keep getting an error saying Next without for.

Sub Give_The_Last_Row()
Selection.SpecialCells(xlCellTypeLastCell).Select
LastRow = ActiveCell.Row

[Code]....

View 1 Replies View Related

Data From Rows And Column To One Single Line

Nov 12, 2009

I have to worksheets: Sheet1, Sheet2. In sheet 1, I have some data like this (in first col is 1, second col is a, third col is c etc. ):

1 a b c d
2 e f g h
.
.
.
.

I need in sheet 2 in range A1(only in this range one big text), something like this: a,c,e,g ........ ( row1-col2,row1-col4,row1-col2,row1-col4 ......) ( separated by ","). The number of rows from sheet 1 is variable. The dataf a,b,c,d,e,f,g,h ... is some random text.

View 4 Replies View Related