I have a substantial schedule that has been completed by a 3rd party who has used the comment function for important data. Can anyone suggest how i can utilise a macro to extract all of the comments into the adjacent column?
The comments are currently against cells in column E and i'd like to extract them to blank cells in a newly inserted column F.
I have two columns,
Name
Marks
Amit
45
Ankit
54
Aditya
Arun
Ayush
43
Amar
Akas
32
Akshay
21
I want to extract first data from column Name where in the data corresponding to it in Column Marks is blank, and if that is filled, i want the next data to be shown automatically
I have been asked by my supervisor to make a performance review template a little bit more user friendly.
Basically what I have is a drop own list in cells in one column (various cells with the same drop down menu) that you select from either 0-10 in a drop down box.
I have another cell which I want to have a drop down box with text comments that you select from when. Only problem is the comments different dependent on the ranking score you give a person (the number selection in the previously mentioned cell from 0-10). So if you have a score between 9-10 in the cell then the comments box will give you certain comments to choose, 7-8 different comments and so on and so forth.
I would like to use a macro to automatically extract the comments in Sheet 1 ( those comments are Customer Names and Their addresses ) to a new Sheet 2 which will print shipping labels.
I have manually created the first three labels on Sheet 2 to show what I want to accomplish. The tricky thing is, that some Customers receive 2 or more artworks...
I have been provided a spreadsheet which has a Quantity column "H". In this column is the quantity of the items and a note containing the serial numbers of the items. The note has no real format. Here are some examples:
Example 1: Serial numbers: 161702, 161722, 161740, 161741.
Example 2: Dec 2006: s/n 63194
Jan 2007: shipped 1 widget, 52480
Example 3: Sent in 2004: 717, 718
Apr 07: shipped 63522 and 63524.
Oct 30, 2007: K. Grande reported only two widgets onboard, 63522 and 63524. 717 and 718 are missing.
Nov 2007: 160302.
Dec 14, 2007: shipped 161093.
There are thousands of serial numbers on hundreds of rows like this. I am looking for code that would copy a new row to another sheet for each serial number in the comment with the serial number placed in column "J". I don't know if the extraneous text can be filtered out, but if I can at least get everything on a seperate row, I can sort/delete what I don't need a lot faster than doing this manually.
In have uploaded an example with before and after sheets. The After sheet is what I have done manually so does not match the examples I used on the Before sheet.
I have a file where comments are used for some cells. The user does not insert any text into these comments fields, but uses the comments feature to display pictures.
A picture is used for the fill pattern of the cell (inserted through fill effects) and that picture is what is displayed when someone moves the mouse over the cell.
The "fill pattern picture" becomes part of the excel file and my question is how can I extract this picture and use it somewhere else.
I have names that look like this:
Juan Jose De La Cruz died single.
I need a formula that will extract the "died single" and put it in the adjacent cell.
I need to return the values in Column A IF any of the adjacent cells (columns) contain a number.
Sample data and expected Results...
Sheet2 ABCDEFGHIJK1DateData1Data2 Data3Data4 Data5 Result
201/01/20132 01/01/2013 302/01/201311 02/01/2013 403/01/2013 1 03/01/2013 504/01/2013
13 04/01/2013 605/01/2013 06/01/2013 706/01/2013
1 08/01/2013 807/01/2013 11/01/2013 908/01/2013
1 12/01/2013 1009/01/2013 13/01/2013 1110/01/2013 15/01/2013 1211/01/2013 2 1312/01/2013
1 1413/01/2013 1 1 1514/01/2013 1615/01/2013 3 17
I cannot use VBA, Advanced Filter or a Helper column, but I could use one additional cell to hold a count, so, the solution can only be a formula.
Am trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.
View 9 Replies View RelatedI have the following code which deletes the specified value from each cell in column B that contains that value, (note it only deletes the value from that column and not all in the row).
Code:
Sub delete1()
Dim lr As Long, i As Long
With Sheets("Database")
lr = .Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If .Range("B" & i).Value = 1 Then .Range("B" & i).ClearContents
Next i
End With
End Sub
Now what I need is to adapt it slightly so that it also adds a comment to the cell in Column I and the current date in Column L, (on each row where the deleted value was).
The comment would be something like "old data archived" and the date in any format, preferably dd/mm/yyyy.
Need one column to combine three columns I have a in a spreadsheet.
Column A - Comments
I have a comment called Duplicate or blanks
Column B - Comments 2
I have a comment called Loss Fund or blanks
Column C - Comments 3
I have a comment of Y or N
I just a want formulae which put the following in one column
Where it is Duplicate in Column A
Where it is Loss Fund in Column B
Where it is Y in Column C
So I have one field for all comments.
I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.
View 11 Replies View RelatedI have an worksheet that refreshes every day from a .csv report. I would like to be able to have a comments column at the end that users type in. The problem is how to keep the comment next to the row it was on before the refresh.
When it refreshes there will be new rows, rows that are no longer there and all in a different order.
There is an Order Number column that can be used as an ID column.
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.
If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)
Capture.JPG
I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.
The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana
B1 - 3
B2 - 2
B3 - 1
B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies View RelatedIm wondering if this can be done in excel..
2 columns
if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%
this is the way I was trying to do this...
=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)
I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.
View 9 Replies View RelatedI have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively.
What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
In the B column i have dates. In the C column i have total hours.
Right now the following code counts how many instances of a date (Dt) are found and makes a decision if it is more than 3.
VB:
IVAL = Application.WorksheetFunction.CountIf(WS.Range("B:B"), Dt)
If IVAL > 3 Then
What i need is to SUM the hours (C column) for each instance of the date (Dt) in the B column, and test if it is greater than 24. so it would be similar to this:
VB:
IVAL = Application.WorksheetFunction.SumIf(WS.Range("C:C"), Dt)
If IVAL > 24 Then
Except that this code attempts to sum the hours of the C column and also wants to recognize the dates from the C column which would not work. I need something different than SUMIF. I need it to Sum the C column for the appropriate dates (Dt) in the B column.
I have a spreadsheet with a list of account numbers and values(sheet1) and on a seperate sheet (sheet2) a list of all unique account numbers that appear in the transaction data sheet.
What I am trying to do is get a total sum for the values of each account number. What I am getting well I'm not quite sure what I'm getting. The problem appears to be when the xSubtotal variable is reset to 0 at the end of calculating all the values for the account number.
The first two columns are an example of my data set; I would like a formula for the 3rd column (Length+1) which will give me the length at the next age (i.e the value in the cell below in most cases) unless the following age is 1 in which case I want it to return zero.
Age Length Length+1
1 22 51
2 51 78
3 78 94
4 94 0
1 31 46
2 46 69
3 69 85
4 85 96
5 96 103
6 103 0
1 19 36
2 36 52
3 52 0
I have two columns with multiple sets of data, based on the entered value I
need the corresponding header returned.
For example:
Column A Column B
Apples Green
Red
Granny Smith
Mutsu
Red Delicious
Oranges Naval
Manderin
Clementine
Tangerine
Now based on a response in another sheet I need to return either "Apple" or
"Orange".
=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,0)) will work if I copy
Apple next to each apple type, but for other items the list of options can
grow dramatically.
When I record a macro I use the paste down feature, but in the code it just sets the paste down to the last cell and sets that number in the code.
What do you put in the code so it finds the NEW bottom of the column when the columns get longer or shorter? I tried recording in relative reference and that did not do it - what's the trick?
I want to write some VBA code that simulates the double click of the square in the lower right of a selected cell. When you double click it, it fills every cell in column A until the first blank in column B. I was able to find this code searching around:
Code:
Selection.AutoFill Destination:=Range("A10:A" & Range("B10" & Rows.Count).End(xlUp).Row)
Selection.Copy
The issue with the above is that it copies the data all the way down to the LAST row in column B with data.
I want to have the macro stop when it finds the FIRST blank cell in column B.
Just as an example, the last row of data in column B is 100, but in row 67 the cell is blank, so I want it to stop at row 66.
Sub For_Adam()
For x = 1 To Range("G7")
Calculate
Range("G11").Select
Selection.Copy
Range("B15").Select
ActiveCell.Offset(x, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next x
Application.CutCopyMode = False
End Sub
Now all I want is for this to be repeated in the next column, so everything is exactly the same but it doesn't start in B15, but starts in C15, then D15 etc.
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies View Related
