In the attached workbook I have groups of numbers in Col G
In col H I would like to trim col G to just one each of the numbers where there are duplicates (as shown).
Each group of numbers will have a blank row between.
Is it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them
View 9 Replies View RelatedI have a credit card number problem where I am trying to trim the data so that I get rid of the spaces between the numbers (number sequence look like this: 5999 9999 9999 9999. This is currently formatted as text, and I would like to maintain this as text and not convert it to a number. The trim function can't seem to get rid of the spaces.
View 4 Replies View RelatedI have a column of numbers (column A) which I'd like to compare to an adjacent column of numbers (column B) and, by row, determine if the number is larger.
If it is, I'd like to place a different colour on the cell. What is the formula I need to use to accomplish this?
Ex:
Col A Col B
30,000 5,000 - A is larger than B, therefore A is formatted RED
20,000 20,000 - A is the same as B, therefore no format applied
10,000 15,000 - A is less than B, therefore A is formatted GREEN
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.
View 4 Replies View RelatedI have a spread sheet for equipment that gets loaned out on a regular basis. Cells B4 - B25 have combo boxes in them, with the options to select "On Loan", "In Stock Room" etc in them. What I would like to do is create something so that as the combo box is changed, i.e. from "In Stock" to "On Loan", a date changes adjacent to each combo box.
For example, combo box in B10 has been changed from "In Stock" to "On Loan". The adjacent cell records the date automatically that the combo box was changed.
Combo box date example.jpg
Need to trimming certain information contained in 1 cell and putting them each in a column. I could do left, mid & right but this time there is inconsistency in the content of the cell.
I've attached a file which will further give you an idea exactly what I want (formula to be in Column M to P).
Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies View Related1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.
Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)
PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.
2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.
My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.
3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx
Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
I have a list of people in column A and in B to E there are 4 numbers from 1 to 13. (Each person has 4 different numbers). In the next 24 columns are concatenations of these numbers in every order possible. On a separate sheet I have a list of concatenations of all possible combinations of numers 1 to 13 in groups of 4. e.g. 1234, 1235 etc. I have then counted how many times each of the concatnations from the first sheet appear. What I want to do is put the names of the people whoes concatentaions match the ones on the third sheet.
View 14 Replies View RelatedI have been browsing this site for some time but never posted. It looks like a great community and looking forward to learning!
I am trying to create an array in VB that searches an entire column (L) that has nothing but numbers in it. I want to search for all numbers that are >= 6. If the number is greater than six store the value in column A. At the end, I need to display a message box that has all the values in A that match the criteria where column L is greater than 6.
I have found all kinds of copy to other sheet formulas and static finds.
What I would like to do is have a macro check a column for any occurrence of a few different numbers, and then enter the text "n/a" in a different column of each row that one of the numbers doesn't appear in.
I have a list of part numbers that require a certain operation, and I'd like to mark off a cell for each item that does not require that operation. I've attached a simple spreadsheet that should help explain this better.
I'm working with a dataset from a survey that has recently taken place. The original structure of certain questions in the survey were multiple choice. The ideal structure for these questions, in terms of variables, is that each possible option for the question (A,B,C,D... etc) have a separate value, with a 1 for yes (reported) and 0 for no (not reported). In other words, if Question1 has multiple possible answers (A,B,C...), then there should not be one variable created from this question, but rather, there should be a variable for each possible response: Question1_A, Question1_B, Question1_C, and so on. And within each of these variables, should be a 1 or 0 depending on if that option was reported or not.
The format I received the data in, though, was a single variable with a list of letters, (e.g. B,D,F) for each option reported.
Variable: Question1
Row 1 A, B, C
Row 2 B, E, F
Row 3 B, D, E
The data I have right now is housed in Excel prior to exporting to SPSS and/or STATA. I need to find a way to distribute these values into separate columns (variables). The tricky part, is that if I simply use "text to columns", it distributes the letters to new columns, but doesn't take into account that the first value reported might not be A. Is there a way to take the list of values in the single columns, and distribute them to assigned newly created columns?
The list columns 5,7 and 9 has number entries.The userform has 3textboxes.is it possible to link total amount of this numbers result into the textboxes.Like column 5"CZ" entries total will showup in textbox10,column 9"DD" = textbox14
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim a, i As Long, ii As Long, b(), n As Long
ListBox6.Clear
With TextBox2
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then
MsgBox "No Entry !"
TextBox2 = ("")
Exit Sub..........
i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' )
my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as:
' =counta(b1:namedrange1)
i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies View RelatedSet cell fill color based on number of adjacent cell
I am doing a research with the following information. In column B, I have the name of the buyer company, column B cells that contain numbers 1, 2 or 3 opposite the company name in column B depending on certain characteristics. I would like to mark the companies in column A in different colors (or some other recognizable way) as described below.
columns result
col A col B col C
7 Materials Inc 3
7 Materials Inc 2
6 Rayon Inc 3
6 Rayon Inc 3
6 Rayon Inc 3
8 Commun Corp 1
8 Commun Corp 2
8 Commun Corp 3
8 Commun Corp 2
8 Commun Corp 1
I would like to have on output in column A as shown above according to the following criteria:
4: if there are only 1 in column C opposite the specific company in column B
5: if there are only 2 in column C opposite the specific company in column B
6: if there are only 3 in column C opposite the specific company in column B
7: if there are 1, 2 and 3 in column C opposite the specific company in column B
8: if there are 1 and 2 in column C opposite the specific company in column B
Any suitable function in excel or Macros that can work with the formatting, because I have tens of thousands of companies and it will take too much time to do it manually.
I would like to copy rows based on the value of another cell.
For example turn this
code qty
apple 3
banana 4
orange 2
into this
apple
apple
apple
banana
banana
banana
banana
orange
orange
I have a fairly large spread sheet where I need to count the number of instances where the number in one column matches the number adjacent to it in a second column.
View 9 Replies View RelatedI have several numbers that are contained in non-adjacent cells. I need to create a cell that will evaluate the cells individually to make sure that they are within a certain range. Once that is done, I want the remaining numbers that are within the range to be averaged. How the heck can I accomplish this task? SUMIF and COUNTIF will not allow me to use non-adjacent cell ranges.
View 9 Replies View RelatedAny 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
In my first column I will be entering both negative and positive numbers. In the cell next to it I need the amounts to all show as positive only. So both negative numbers and positive in the first column will all show as positive in the adjacent column/cell.
View 2 Replies View RelatedI 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 have a list of Dates in Col. A
Column B contains both numerical and text values.
I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.
A B
1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8
Value needed = Vac
Solution 1/4
1/7
This is what I am using:
=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)
When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.
I can't keep going manually as I need to go to about 300 cells...
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 RelatedThe easiest way for me to explain it is to give an example: if the list contains Red,Blue,Green,Yellow. i need a formula for the adjacent cell that inputs £1 if red is chosen, £2 if blue is chosen, £3 if green is chosen, £4 if yellow is chosen
View 2 Replies View Related