I have a spreadsheet with many many columns...the top of each column has a string (it's a bird name). Below in the same column are various blank spaces or letters (defining species abundance, if there), example:....
View 2 RepliesI want to concatenate the text of several cells into one cell that satisfies some condition (simular to a SUMIF). For example:
1 AB
2 CD
3 DE
The formula I'm looking for should return ABDE in case of 1 and CD in case of 2.
I have many text files basically with the same data from different dates. I am trying to select specific lines from each text, one that includes ".txt" which is the date, ex: "2008_308" and one that includes the word "PIXEL" and the following 5 lines after that.
I found a very similar post to mine and someone provided the script using VB for the question. It works, but it was only for one text file. I would like to use it for multiple text files. The files are all dated, i.e. "2008_308.txt". Although some of the dates are missing.
This is the script, from another posting, for importing selective lines from one text file:
Sub test()
Dim fn As String, txt As String, delim As String, a() As String
Dim i As Long, ii As Long, iii As Long, n As Long, x, y
fn = "c: est.txt" ' Change here (File Path)
delim = vbTab ' Change here (delimiter)
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
If InStr(1, x(i), "txt", 1) > 0 Then
n = n + 1 : y = Split(x(i), delim)
For ii = 0 To UBound(y)
a(n, ii + 1) = y(ii)..........................
This is my formula with concatenation: =K3/K1*100 & "c" & " Each"
This is the result of the values in K3 divided by K1: 6.41666666666667c Each
I would like to limit the decimal places to two: 6.41c Each.
I have tried to format the cell and none of the formatting options have any effect.
If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies View RelatedI'm currently trying to sum across a row only if certain text is present. So, if A1 has CT8 and A2 has 5 and A3 has CT5, I want to just sum the cells with CT in them. I've already worked out how to strip the CT from the numbers in the sum, but not how to specifically only sum if CT is present. And of course, the sheet uses other letter/number combinations that need to be separately summed.
I have a macro which is identifying unique values between two sheets (sheets '4' and '5') then pasting these unique values at the end of the list (column C) on sheet '5'. It also drags down the formulas in columns D and E in sheet '5' as long as there is text in column C.
Does anyone know how i can add to the macro so that for every unique value added from sheet '4' to sheet '5' the word "uncategorised" is added to the respective cell in column F on sheet '5'.
I only want this to be for the new text added (the unique values form sheet '4') because column F will be blank for these cells.
I want to transfer certain data from one workbook to another. The problem is that I want to copy certain rows from my main workbook which has 3026 rows to a smaller workbook which has 1545 rows, both workbooks have the same product numbers in column A - except my smaller workbook has 1481 products omitted
These are the first 3 rows in both sheets:
Main Workbook
ABA19
Dar ABACUS Spare Glass Dropper For ABA4750/5050/5250
ABACUS Spare Glass Dropper For ABA4750/5050/5250
ABA191
Dar ABACUS Spare String Accessory For ABA5240
ABACUS Spare String Accessory For ABA5240
ABA4750
Dar ABACUS 800mm G9 Square Flush Polished Chrome
ABACUS 800mm G9 Square Flush Polished Chrome
Second Workbook
ABA4750
ABA5050
ABA5240
As you can see both 'ABA19' and 'ABA191' are not included in my second workbook but 'ABA4750' is, therefore I want to only copy the row of data for that product in my main workbook to my second workbook.
Need to search a sheet and find cells that contain the text "Requirement". If found then i want that cell to become blank.
example
so in sheet1,
i have a number of columns and a number of rows
in cell A3 the value is - " there are requirements"
in cell F23, the value is -"the Requirement is"
since both cells have the word requirement, I want these cells to become blank.
Need to search a sheet and find cells that contain the text "."
Want all cells that don't contain a "." (dot) to be erased from the sheet
Example : so in sheet1,
I have a number of columns and a number of rows
in cell A3 the value is - " there are requirements."
in cell F23, the value is -"the Requirement is."
since both cells have "." ( dot) , I want these cells to remain in the sheet, but the rest of the cells should become blank.
Column A consists of cells with a variety of five digit numbers. My formula needs to return a text value if numbers are present in column A, this is what I tried:
=if(A1="","Chassis") but it returns "False" instead of "Chassis".
is there a formula that will find a word in a sentence written in a cell and if present then enter specif text in another cell? for example, if the word "Hotel" appears in the sentence in cell A5 then put in the text "Hotel and subsistence" in cell B5...
View 2 Replies View RelatedI am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
IS it poss to sort an entire sheet by one column then another, but to exclude certain rows that include certain data.
This is what i mean, I have a sheet where data is entered on a daily basis. this data has tobe kept with in its dates. Each day starts with a start time and ends with an end time, and on occasion a break is included within the data. then between dates there is a blank line.
IS it poss to sort the data and exclude the blank lines and the start, end and breaks within the data, but keep the data within date, so all data for the 12/4/09 stay within this block...?
I have a inventory report that has multiple stores. I am trying to add the total of all stores in two columns named "AE" and "ASC". Anytime the store name says "AE" i want to add the totals of that store in the "AE" column. Anytime the store name says "anything except AE" I want the totals to add in the "ASC" column. Hope this makes sense. How do I do this? Do I use conditional formating?
View 9 Replies View RelatedI have a worksheet that has data in cells A3 - J3. One value will be placed in one of the cells directly below A3-J3. Is there a way to look at the cells A4-J4 and determin what cell has the value and then preform a concatenation on that cell pluss the one directly above it.
I have included a worksheet as an example.
why this code continues to give an error?
Sub TakeOut()
'
'
Windows("EXTRACT2.xls").Activate
Sheets("Sheet1").Select
For i = 1 To 100
Range("A" & i).Formula = "=CONCATENATE(""Trip "", ""=RIGHT('[EXTRACT.xls]Sheet1'!A & i,3)"", "" = "", '[EXTRACT.xls]Sheet1'!E & i)"
Range("A" & i).Value = Range("A" & i).Value
Next i
End Sub
I have turned off show zeros on a sheet. This is good in general, but for certain cells I would like excel to show zeros if the formula output for that cell is zero.
Background information: I am working with a very large workbook, and the final sheet is my output file. This sheet is then exported to a CSV file and imported to another program in my workflow process. I need most blank cells to remain blank, so that they are interpreted as blank in the output program. Some of my cells are actually zeros which I intentionally inputted as zeros...those should be interpreted as zero and not blank.
I have a cheque list with Column A for Cheque number, Column B for Date, Column C for Payee Name and Column D for Amount
Cheque number ranges from sequence 1200 to 3600
Is it possible to set up a formula to sum the amount of cheques in column D based on the cheque sequence number of column A . In other words if based on criteria cheque sequence number is
i have a VLOOKUP formula
=VLOOKUP(W3,CAT_A,3,1)
where CAT_A is a dynamic named range on the CAT sheet, I also have CAT_B thru CAT_F
I would like to make the VLOOKUP formula neater by using INDIRECT and the initial letter from column I as follows
=VLOOKUP(W3,INDIRECT("CAT_" & I3),3,1)
However I keep getting #REF, where have I slipped up
i have this issue, i named column J. now it says instead of using Social Security numbers as a unique identifier, they are considering using an ID of the first 3 letters of the last name (L Name) followed by the first letter of the first name (F Name). If the last name is fewer than 3 characters, the letter Z replaces each missing character.
View 3 Replies View RelatedI have two columns that consist of strings that I want to combine in a certain way. The first column has a string that consist of numbers and letters and the second column just has 3 letter codes. What I want to do is get rid of the letters in the numbers string and then add the 3 letter code on to the end of the numbers, and have this placed in a seperate column. For example: if column 1 contains "12F51Q" and column 2 contains "ABC" then the result would be "1251 ABC". Is this possible??
View 3 Replies View RelatedI have a table with 6 columns (with headers A -F) and 10 rows. There are values in each cell. I want to take a value from each of the 6 columns and concatinate them. I would like some code that would generate every possible resulting string and I would like the possiblities set out in one column.
Apart from the fact that it might be tricky enough to produce the code , by my calculations there will be 10 to the power of 6 possibilities and as far as I know there are only 65000 rows in my version of Excel. If they had to be put into 2 or 3 columns I would not mind
I've got a series of dates and times, each in a different cell, all in a row, and it looks like this:
19 Jun 07 17:30Z UNTIL 21 Jun 07 01:30Z
And I have a formula that says
=CONCATENATE("VALID TIME", E23,H23,J23,K23,N23)
However the result of the formula looks like
VALID TIME377900.729166666666666UNTIL377920.0625
Now this looks to me like something wrong with the formatting of the cell that has the formula, but I've tried different format with numbers, dats, times, text, etc and nothing reaclly changes.
I need to reverse concatenate a column of addresses, but text to columns won't work. I'd like to have a formula that takes into account each of the following scenarios (basically any standard address you can think of):
102 Bart St
104 Homer Simpson Ave
106 US HWY BSN 805 W
108 N Springfield Rd
What I need is to have the result in four columns. The first field would the house number. The second column would be the prefix (direction) of the street name, IF PRESENT (so the first three examples would have no value in the second column, but the fourth one would have an "N". The FOURTH column would have the suffix, whether that is a street type (like Rd or Ave), or a post-directional like in the third example ("W"). The THIRD column would have everything else (whatever is between the prefix and the suffix). In other words, using the examples above (* indicating a new column):
102**Bart*St
104**Homer Simpson*Ave
106**US HWY BSN 805*W
108*N*Springfield*Rd
I'm tring to concatenate the contents of column C with column H - the results to appear in column R. It will be an unknown and ever changing amount of rows.
I believe that it's the value for field 'NewField' that's causing most problems, but it could be other stuff.
Dim I As Integer
Dim LastRow As Integer
Dim NewField As String
LastRow = ActiveSheet. Range("A4").End(xlDown).Row
For I = 4 To LastRow
NewField = ("R" & First)
NewField.Value = (("C" & First) & ("H" & First))
Next I
I have a workbook with several columns, some of which are for internal company use only.
Sometimes we need to send an updated list to business partners, but they don't need all columns.
Is it possible to make another workbook which takes only specific columns, and excludes the stray info at the bottom of the original workbook? See examples.
Alternatively: Would it be simpler to make an extra sheet in the original workbook, and somehow choose "Safe only this sheet to a new document" whenever someone requests the list?
i have a column that has some values in it. Most values are #N/A but some are numbers. I am trying to create a function of some other snipet of code that would substract one value from the previous value and have the result next to the first value.
I am trying to wite a macro that will allow me to pull up specific files. All files will be located in the same folder. For example, I want to type the workbook name "sawn" into a cell and hit the macro button. The macro will then look in a "pool" folder for the workbook named "sawn" and open it. Is this even possible?
View 14 Replies View RelatedThis is a formula in an existing spreadsheet that I need to amend.
{=SUM(IF(WeekNo=2,IF(Workgroup="REASSIGN - CAP",1,0)))}
WeekNo2 and Workgroup are names defined and are offsets that define two separate columns.
I have created a new name realclosetime and it is an offset to another column of data.
I have tried to change the existing formula so that it is not actioned if the realclosetime is empty. Its a date and time field and if it is not completed I dont want to be including that row in the SUM calculation otherwise I end up with N/As.