I have a spreadsheet which contains 6000 rows where column A1 lists a name, B1 an address, C1 an amount and B2 the 4-digit zip code and place. Now I want to move the content of B2 to D1. I can't use offset because the spacing of the zipcode cell is not always the same.
View 4 RepliesWhat I need to do is have the numbers on the x axis count up with a even increment. For example, 1,2,3,4,5. That part is easy and I can do that, the next part is not.
Next I need the X axis of my graph to have, what the graph I'm trying to replicate calls, a "squared scale". So the numbers need to look like what is above but be spaced in squared intervals.
It would look something like this: 1 2 3 4 5... note the spacing between each is increasing.
Below are pictures showing how the book does it by hand.
20140625_085909_zps204851fe.jpg
20140625_085922_zpsbc03f1bd.jpg
I think I could get it to look like this with a dummy series with the axis labels listed but I need to be able to have a point read off the actual values so the dummy axis wont work.
I want to enter a number 0 to 999 in cell A1 and when the enter key is pressed move the contents of cell A1 to A2 and A2 to A3, cascading down until A40. So after five numbers the first number I entered woul now have cascaded down to cell A5 ready to go into cell A6.
Would I have to use some kind of program or does excel have a command for this?
i have code like this to copy certain word in cell and place it at new sheet:
VB:
Sub Foo()Dim i As Long, iMatches As Long
Dim aTokens() As String: aTokens = Split("Accommodation", ",")
For Each cell In Sheets("Sheet1").Range("C:C")
[Code].....
the problem is the cell source is not deleted. what line should i change in code above to move the result to new sheet and delete row source
At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.
The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).
In my procedure I have a for ... next loop with "For i = 1 to Number"
How can I determine the value of i during this loop.
Whenever i is uneven some cell have to change color, if i is even cells remain unchanged.
I have most of the code figured out, except for this part
I want to separate data in cols A,B,C to match my results in cols D,E,F,G .
Have tried using left , mid , right functions but data is not evenly matched .
The most important parts I need is the results in cols D , E and G
Sheet1 ABCDEFG1ABCResult AResult BResult CResult D23SurvivedJonathan Riddell3SurvivedJonathan Riddell 37RacquetorCraig Grylls7RacquetorCraig Grylls 42KodemaOpie Bosson2KodemaOpie Bosson 56-Clutha Lad-Shane Dye(2.5L), 6Clutha LadShane Dye2.568-Viceroy-Johnathon Parkes(3L), 8ViceroyJohnathon Parkes3711-Omana-Rory Hutchings (a)(5L), 11OmanaRory Hutchings589-Sunbeam-Matthew Cameron(6L), 9SunbeamMatthew Cameron6912-Zulou-Kelly Myers(7.8L), 12ZulouKelly Myers7.8105-The Sheriff-Hayden Tinsley(12.6L), 5The SheriffHayden Tinsley12.61110-Inazuma-Bridget Grylls (a3)(12.9L). 10InazumaBridget Grylls 12.9
I'm looking to combine a number of worksheets into a summary sheet .. but on some the narrative is more than the 255 characters. How can I , where this is the case, insert a row below.. say input "Continued" in column A and then copy the contents of the cell which is more than 255 to the cell immediately below it.. and maybe do this again where users input more than 510 characters.
Alternatively, is there a way of getting around the copy and paste values.. where it only inputs 255 ?
I would normally sort out using Text to Columns, but I can't in this case as the remaining elements can vary.
I have a list of names with Forename and Surname and then the elements after that vary.
Names are written in one cell under a single column like so:
John Smith 13/04/2000
James Smith 123 123 1233
Jane Smith 07122 12345
Another Smith AB99 9CD
All I want to do is end up with 3 columns of data,
Forname | Surname | Other
Is it possible to use a formula to make the breaks into each column at the first 2 spaces only, therefore the contents after the second space would just end up in one cell?
I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
formula in excel to calculate payback and discounted payback periods with uneven cash flows.
EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444
I have the below code that I would like for the data to fall in the exact same space underneath their proper headings. I cant create an html shot for specific reasons and so I am stuck with creating the table in this manner. Does anyone know how to on a single row to accomplish placing date so it looks like a table
10 spaces
20 spaces
30 spaces
that way the number will always fall on space number 10, 20 and 30 on same row
strbody = "Please find attached the daily position report and Profit/Loss report for COB " & FileDate & Application.Rept(Chr(13), 10) _
& "SPREAD " & " UTILIZATION" & " FLAT " & " UTILIZATION" & " OPTION " & " UTILIZATION"
I have a list with fractions such as 25 1/4, 33 5/16, 20 15/16, etc. i have them formatted as FRACTIONS 2 DIGITS 21/25. Manny of those have double spacing ex: 25 1/2, So i am wondering if theres a way to take the extra spacing off the ones that have double.
View 10 Replies View RelatedI have alot of data with data and time in one column, and temperature in another. All my data is bunched together, i have temperature measurements every 3 minutes, 24 hours a day, for about a month... and then after that, i have temperature measurement 10 minutes a day, 24 hours a day for several months
I want to group my data by hour, so for my 3 minute measurements, 20 values make up one hour. And for my 10 minute measurements, 6 values make up one hour. I want to know if there is a way to space out my data so that it groups it/ spaces it out. So for the 3 minute values, it would list 20, then put say 3 blank rows, then 20 more values, then 3 blank rows etc.... and for the 10 minute values, it would be 6 values, then 3 blank rows, 6 values, then 3 blank rows etc...
I have 10 items on my x-axis. Is there a way to space item 10 further apart than the other 9?
View 9 Replies View RelatedIn the below example each of the items listed in Row A have an extra space before the word, is there an easy way of taking that space out i've tried text to columns, paste special. Not sure what else can be done ...
View 9 Replies View RelatedSpreadsheet
aaa hhh
111 222
ww lllmm
between here there are about 50 rows
zzz 222
I need code that would reduce the spacing too about 10 rows between the "ww" in column A and the zzz in column A.
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
how to leaved blank cells for missing dates in a series of sequential dates. For example, If my series is (in (m/d/y) format):
1/10/1999
1/12/1999
1/13/1999
1/14/1999
1/17/1999
1/18/1999
But I want it to be:
1/10/1999
(blank cell)
1/12/1999
1/13/1999
1/14/1999
(blank cell)
(blank cell)
1/17/1999
1/18/1999
How do I do this?
I got a number, OK lets make it every even "5","10", "15" est and i add another one beside it to be added 0.01 every "5" times, so ok i'll make it a bit better some how,
(A1)
lv 1
=1
lv 2
=2
lv 3
=3
lv 4
=4
lv 5---(A2)----(A3)
=5 + 0.01 = 5.01.............
um I had one idea that did and one that did not work, 1. to do it manually or 2. (that did not work) us "=IF function"
The email macro I set up didn't work when I referenced the sendto from a cell in sheet 2; I changed it to reference an email address on sheet 1 and it worked with spacing errors. I need to fix the spacing errors, and I need to be able to pull the email from sheet 2. Code referencing sheet 2:
View 3 Replies View Relatedhow to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
I have a column of data. I would like to space this out so that each value is separated by two spaces.
E.g.
1
2
3
-->
1
2
3
I can do this manually (by inserting two cells), but I wondered if there is a way to do this automatically?
(I have a column organised with the spaces immediately to the left of the column I want to change.)
if you look in Column G you will notice the number on the right side of the column is indented one space too many (for any number that is 6 long in column A).
Is there any way to do an IF C=6 and then have it remove a space from before the number in Column G to have it line up?
I am using excel as a base for formatting reports, and collating logical paragraphs, which I want to display in one massive merged cell on another worksheet.
The paragraphs are different for each report, and I do not wish to manually enter them in each time. I also want them to display as seperate paragraphs on the page, but in the same cell.
To throw a spanner in the works, the paragraphs are all of indeterminate length, otherwise, I could use different cells. If I combine them in the massive merged cell by using =cell1&cell2&cell3 then they will just appear merged as one whole huge paragraph. I want them to appear as separate paragraphs in the same big cell.
If my file is like so
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
i NEED THE OUTPUT IN NOTEPAD
A1B1 C1 D2 E1
A2 B2 C2 D3E2
The spacing varies from row to row
if i am saveing as notepad file....in macro how can i give the spacing properties give an example macro and i will customize it according to my need
I have been able to produce a column that gives me key date information. I might have many empty cells in that column before it hits another date. I need to get this data over to another sheet and place it in a row.
I am trying to get it to look like this:
B2(value), C2(value),D2(blank),E2(Value),F2(Value),G2(blank) etc depending on how many more elements are found in the sheet.
I am able to find topics that allow me to copy a range of data but not one that will allow me to copy a range then skip a cell.
i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.
46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285...............
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).