I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 RepliesI want to copy cells (include conditional formatting) and paste in mspaint How can i write this code ?
View 9 Replies View RelatedI'm tracking weeks by weeknumber in the format YY-WW. At the moment I am entering the weeks manually in a general cell, preceeded by a '. I would prefer to have the sheet work out the weeks for me as we move the sheet every week. I would like to format the initial cell as follows:
09-08 (or '09-08 if I must)
then have the next cell in sequence read 09-09, 09-10, and so on. With the initial week in cell A3, I have tried to accomplish this with:
=LEFT(A3,3)&RIGHT(A3,2)+1
However, this does not work for weeks 01 through 09 because excel kills the leading zero in the second part of the formula, regardless of how I format the cell (format 00-00 does not work.) How can I force Excel it to keep the leading zero in the week number?
I'm attempting to use Excel as a writing tool.. it's incredibly powerful as it will allow me to customize my workspace, create links, flow charts etc... but an area I'm most concerned of is at the heart of this endeavor, which is text... lots of text!
I believe there is a limitation regarding the amount of text within a cell ( something like 250 characters?) Is there a way to customize/format a cell to include much more text?
What route would you advise? ..Forms, pivot tables, textboxes?
I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.
What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.
I am looking to build a VBA to do the following action:
Look up an ID number from column F (ID numbers are separated by an empty row at current),
then check whether there is a corresponding value in columns N to P,
if there are values, copy them onto a different sheet into three different columns
if there values below this particular row, i.e. if the rows below say row 9 (where we looked up the ID number) in columns N - P are not empty, also copy all these values and add additional rows as needed on the sheet this is copied to
then look up the next ID in column F and do the same until there are no more IDs
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.
I would like a cell to show either OK or No depending on the requirements of three other cells as below
G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.
I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)
=IF((G6+365)
I have a file with a bunch of columns in it. Columns C and D have names in them. I want to filter on these two columns where the results show all rows where the selected name is in both.
Example, If I filter on Column C first on 2 specific names (Joe and Chris), it will not show the results in column D if that name was aligned with a different name in column C (Mark and Steve). I need all rows to come back.
Column C
Column D
Joe
Joe
Chris
Chris
Joe
Chris
Joe
Mark
Joe
Steve
Chris
This is my formula and it returns: -55 bps.
=[ASK.xls]QTR!$AT$81&" "&"bps"
However, I want it to return (55 bps). I don't want to just add the parenthesis because in the future this formula could return a positive number. I tried to format the cell differently, but that did not work.
I need either a foumula or macro to add leading zeros to a number based on text in another column
Column A is the original number
Column B is blank and where I want my result to go leaving column A original
Column C is where the text is that the leading zeros will be based on
A1 = 14M3652100
B1 = Blank
C1 = If this column has text "ASP" or "POSMOS" or "ANISON" I need B1 to result in 0014M3652100
I have a spreadsheet full of site codes which should be the format:
AA1111
or
A1111
Where A = any alpha character & 1 = any numeric character
unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.
At the moment I manually correct this. Is there a better way to do this?
I have looked and experimented on how to include multiple ranges on a pivot table in excel 2011, i have found how to to it with the wizard but i can not find the wizard.
How to separate the ranges in the selection box? They are going to be going across sheet is that makes any difference.
In my sheet I have a range on the go called 'cells_replen', I then need to search through the sheet and if a cell matching a given criteria is found I want to add it to the named range. The idea is that when I've done all the adding I can just select the range and then go back through the cells later on without searching for them all over again. The first code that declares the first cell I need in the range is:
ActiveWorkbook.Names.Add Name:="cells_replen", RefersTo:=Range("A3")
That works fine, I can also manually add cells into the range as below:
ActiveWorkbook.Names.Add Name:="cells_replen", RefersTo:=Range("cells_replen, A9")
No problem if I want to just add 'A9' but because I'm searching through the sheet, I need some way to just add 'ActiveCell' or similar.
I have created (via recording) a very simple macro in Microsoft Word to convert a fraction of the form a/b to the form where a is directly over b with a horizontal line between them (like how you would actually write it in math class). Before I send it to my co-workers, I want to beef it up a little.
Right now, it will only work if a and b are integers or if a and b are words or phrases with no spaces (i.e. change/time will convert correctly but the phrase change in x/change in y will not). Also, it would be great if it could work with parenthesis (i.e. (2x-1)/(2x+1)).
Just so you know, I am very comfortable working with code in order to come up with a beefier macro. However, Word uses Visual Basic and I'm afraid I don't know much about the syntax of that code. So, if any of you know of a site I could view to learn a little Visual Basic or if you know a site that already has good code already written for this purpose, I would be much obliged.
So quick sample of data :
B74
B74
9
94
1
948
B74
So if I have this data in a column you notice they have different length. Now I want my macro to add leading zeros until the length of all occurrences is 4.
I know for numbers you can do a range.numberformat = "0000".
But this won't work for B74 because (obviously) it will not be recognized as a number.
I know in a formula you can do it with the command TEXT. However I do not want to create extra columns I want something to alter it in place just like the numberformat does but then for numbers and text.
I'm trying to convert a set of numbers between 1 and 9999 into text strings, with the number at the end as a four digit string, so 1 becomes "COLUMN0001", 123 becomes "COLUMN0123", etc. In excel, it would be easy - I would just use the text function and convert the number to a string and append it to the word COLUMN, but as far as I know, there isn't an equivalent function in excel and running excel functions in VBA is notoriously slow (especially as I will need to do this about 360,000 times).
View 4 Replies View RelatedI need to import thousand of txt files into 1 worksheet keeping the file names as data. Each txt file has 2 columns :
1 0.65914
2 0.65945
3 0.86062
... ...
and each txt file represents recordings made at specific time and date (e.g 0158.DSG_RAWD_HMS_21_ 0_ 0__DMY_29_ 2_12_pulses). I would like to have 2 columns: 1) with the time taken from the name of the txt file (e.g. 21:00:00) and 2) the associated recording. Something like this:
21:00:00 0.65914
21:00:00 0.65945
21:00:00 0.86062
and so on for each txt file and all the recordings piling up in 1 spreadsheet. I have tried to run few codes in VBA, but I have no knowledge of it and none of the code worked. I am using Excel 2010.
Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"
View 5 Replies View RelatedI am looking for some VB to import multiple csv files, stored in a single directory, into a single excel worksheet. However I am looking to also include all or part of the filename, so to differentiate each file.
I've attached an example spreadsheet, inc the below code, of what I am trying to achieve.
Current code is (this parsers all .csv files in the specified folder into a single worksheet):
[Code] ......
Attached File : csv_loader.xlsm
i cant do this from importing the data, so instead i have to try and fix it with code. I get a sheet of data from an external source that removes the 0's in the front of the number. Its only a 4 digit number so 21 would be 0021. Now i have been able to trap the data i think i would need. i created a =len() statement to tell me how many numbers each cell has. here is the code i think how it would work.. i just need a little help with filling in the blanks. I would assume its something to the lines of " 00+string value if it was 2 numbers needed.
B2 has the Len values (4,3,2,1 etc) and A1 is the original string that has the values.
So i want the code to add 1 2 or 3 zeros to the front of the number based on the len value.
Range("B2").Select
While ActiveCell.Value <> ""
ActiveCell.Offset(0, 0).Select
Select Case ActiveCell.Value
Case Is = "3"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 1).Select
Case Is = "2"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 1).Select......................
I have a spreadsheet I work with which contains a formula that needs to take an item ID letter/number combo, substitute the preceeding letter(s) with leading zeros (if the initial numerical characters are fewer than 5) to make the result return a 5-digit number.
The original ID can be in several possible formats (beside each I will post the desired result):
V7684 07684V366 00366V88 00088V60827 60827VS57871 57871VS67 00067
I have the following formula which works like a charm for all except the last example:
=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS","),"V",")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","0"),"V","0"))))
If the first 2 characters are letters ("VS") and there are fewer than 5 numerical characters, the formula results in a 4-digit number, rather than 5. Using the above example, VS67 returns 0067 instead of 00067.
P.S. I just tried
=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS",""),"V","")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","00"),"V","0")))) which only works when there are 3 or fewer numerical characters.
I have two columns of data and cannot get the column to change the format to display as currenty or accounting, with a currency £ sign and comma separator...I select it all and go to Format cells to do the changes, but it does nothing, at all!
View 2 Replies View RelatedI have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
I have attached excel with the logic I am using, but I have a problem and needed logic to update to make sure it is not inserting same data (duplicate data).
Please update the logic so that if it is having same data in sheet 3 it should not insert or delete duplicate.
Code:
Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 9), bi1 As Byte, vData1(1 To 9), bi2 As Byte, vData2(1 To 9), bi3 As Byte, vData3(1 To 9), bi4 As Byte, vData4(1 To 9)
Set ws = Sheets("Sheet2")
For bi = 1 To 9
[Code] .........
i have a list of about 20,000 phone numbers that I need to edit.
all start
01 123456
01 123457
01 123458 etc
I am trying to create a macro that will change them to
+001123456
+001123457
+001123458 Etc
However, no matter what I try it always comes out as
+001123456
+001123456
+001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost.
I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
Is there a way to filter column A but ensure it includes multiple lines of data in a different column.
I have attached an example of what I am looking for:
I need to filter by community but keep all the information listed in columns, B, C, & D.
So when I filter to Thunder Bay I would still see the 5 members listed in column D.
I realize the easy solution is to fill all the empty cells in column A to pick up the data in the other columns, but I do not want to Fill all the empty cells to include the name "Thunder Bay" (not the look I want for this spreadsheet).
I believe that the best way to evaluate this request is to look at the example. I have 2 buttons to demonstrate what I need, along with written instructions.
eMailSampleTest.xls
I have file with so many columns and i want to keep only columns i want.
Data
genredyellowgreenwhiteblue
1aaggttccbb
2aaggttccbb
3aaggttccbb
4aaggttccbb
5aaggttccbb
expected
genredwhite
1aacc
2aacc
3aacc
4aacc
5aacc
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
I want to sum number values in a column. I have used "=SUM(C4:C100)" and it works a treat. Now the twist. I have an adjacent column (Column D) and only want to add values in Column C, where a corresponding value is in Column D on the same row.
So for example if I have C5 as $1000 and D5 as 13/5/13, I want to include the $1000 value in the sum of Column C. However, if I have C5 as $1000 and D5 as blank, I don't want to include it in the sum.