Creating a sequence in every column? It should look like this:
Original matrix (Top left cell is "A1"):
40 50 60
30 10 20
70 20 0
And I want to replace it with:
2 3 3
1 1 2
3 2 1
I need to create an ascending sequence of numbers in every column. First column contains: 40, 30, 70. That's why its ascending sequence is: 2, 1, 3.
Btw, I got N rows and M columns.
How to create a column that has ascending values ie: increment of 1 eg. 1,2,3,4,5,6 ......
View 2 Replies View RelatedI want to do is sort in ascending sequence, a range defined as rTrainRow which is a row of text data. Each cell in the row can contain space, blank, one Alphabetic character or a special character such as $, %, (, +, etc.
View 2 Replies View RelatedI need to put the order in the second column of the item that is in the first column.
In the last line item Computer is recorded for the third time.
MATERIALSSEQUENCEComputer1Computer2Mouse1Mouse2Computer3
I want to create a 4 character alphanumeric sequence. The sequence is to be like this: a1a1-z10z10. If it's a number, increment 1 until you reach 10, then move to the next column, if it's a letter, increment a until z, then move to the next column. Excel's autofill feature doesn't seem to work well for me due to the letters involved, so if someone has a macro handy that would be great.
View 11 Replies View RelatedHow can I create a sequence with a formula starting in A1 that will give 1,1,2,2,3,3 and so on horizontally?
View 3 Replies View RelatedI am trying to sort a column of data in ascending order but I am not getting the results I expect. An example of the data held in the cells is:
4972-1A
4972-10A
4972-2A
4972-1B
4972-3C
When I sort it in ascending order it sorts like:
4972-10A
4972-1A
4972-1B
4972-2A
4972-3C......................
I have three columns of values that are in no particular order that need to be sorted in ascending order in another column.
View 9 Replies View RelatedI am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:
Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.
ColumnA ColumnB
1 -------Part A
2 -------Part B
3 -------Part C
5 -------Part E
6 -------Part F
As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.
ColumnA ColumnB
1 -------Part A
2 -------Part B
3 -------Part C
4
5 -------Part E
6 -------Part F
I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.
Is there a formula that will allow you to add an ascending suffix to duplicate data in a column?
For example:
1234
1234
1234
1234
1234[code].....
I've been trying to figure it out on my own for a while now with no luck...
there may even be an excel formula answer to it rather than a macro. Coloumn A has a series of numbers, I want column B to show those numbers in what would be there Descending Sequence.
Thus
Col A
100
97
14
87
32
108
21
So with this routine you would then see
COL ACOL B10029731478743251081216 The above I suspect is not too difficult, however if two (or more) numbers are the same I want the sequence to reflect that thus COL A COL B100297 314 797 332 5108 121 6 and no... the data - Col A - cannot be sorted, it must stay in that sequence.
If possible I would want this done automatically when any Col A value changes (any unused cells would be 0 by the way). Oh, there will in fact be 25 cells in column A for this.
I am trying to fill a column with a number series starting with 1 whenever a value changes in the adjacent column. So for instance I am trying to do something like this :
87 1
87 2
87 3
88 1
88 2
88 3
88 4
89 1
89 2
90 1
91 2
92 1
92 2
92 3
92 4
92 5
92 6
I was wondering whether it was possible to search for a sequence of numbers.
For example, if this is down a column:
A1: 5
A2: 4
A3: 5
A4: 6
A5: 9
Is it possible to have a find function to search for the sequence of numbers "4 + 5" and consequently highlight cells A2 and A3?
I have a set of data in cells sequencially in every 3rd column. I would like to create a new rows where the data I have in columns are placed into rows in accordance with its catogory.
So all EUR are in one row sequencially over the colums and the same for USD & GBP. This way I can create a graph.
Jan 12EUR
USD
GBP
EUR
USD
[Code].....
I am stuck on what should be a simple formula. I have a spreadsheet that is sorted by "target" number in column A, basically 1,2,3,4. I am trying to create a formula that will increase the number in column B by one based on column A. So when the value in column A changes column B will reset to 1. Also I need this sequence number to be preceded by zeros up to 0999. So 1 would be 0001 and 895 would be 0895.
View 2 Replies View RelatedI am trying to group the same values in columns together and i was thinking about having a number count. What's the function that might be able to display this (display whats in Column B in the example below)?
Values
5000 1
5000 1
6000 2
7000 3
7000 3
8000 4
8000 4
8000 4
9000 8
10000 9
10000 9
I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
View 3 Replies View RelatedI was wondering, is there a function that would take, lets say, every 4th cell value from a column and create another column consisting of every 4th number ?
View 2 Replies View RelatedThere is a list that contains provinces, a name, and a number.
How can I find the top 10 names in descending order (highest num to lowest) for a specific province?
Eg. Column A
BC, ON, QC, BC, BC
Column B
Mary, Jane, Jack, Rudy, Hank
Column C
8,3,15, 20, 5
Column D
Top 3 for BC: Rudy, Mary, Hank
I need values from column F in ascending order.
Also I need in column G only the values without formula.
I have never recorded a macro before and what I want, I think, is pretty simple but I can't figure it out. I want a sort button that sorts all information ascending by column B. See attached spreadsheet.
View 8 Replies View RelatedFor input I have in column A:
1
3
2
- (empty cell)
4
I want for output in column B:
1
2
3
4
Can I do something like this using a function?
I'd like to carry out a VLOOKUP on a list of product. The product numbers contain numbers and letters, some start with several 0's. If i were to convert these product numbers to an actual number, the 0's are removed.
So, when I attempt to place the list in ascending order, it's not really in ascending order - it will start with the part numbers that Excel has defined as a number with everything else afterwards.
Is there anyway I can run a VLOOKUP without having to place the lookup table in ascending order?
I have data in a spreadsheet with approximately 100 rows. In column F, the following values are allowed:
Subscription
Redemption
Transfer In
Transfer Out
Switch In
Switch Out
way to sort them in this order, rather than ascending or descending? (I want all of the Subscriptions together, then the Redemptions, and so on...)
I've sorted it by column A. However, sometimes the date is the same and then it doesn't always go right... see f.ex. 17 dec, where the ship arrives in the harbour and then is at sea again and then departs Not exactly what happens. I had the same problem on 28 dec, but there it disappeared when I told Excel to sort secondly on column B.
However, this doesn't work for 17 dec.
I'm using this code to import bulk *.csv files
Sub ImportCSVFiles()
Dim sh As Worksheet, sPath As String, sName As String
Dim R As Range, fName As String
Dim sh1 As Worksheet
But I've noticed that on import run, the macro mixing the ascending order of the files.
An example:
Inside my_folder I have 3 csv files:
advertise.csv
adwords.csv
adwords keyword tool.csv
When the macro runs, it takes advertise.csv as the 1st. (Thats Ok) then adwords keyword tool.csv data
before thaking adwords.csv
(I have over 100 files to import, so you can imagine the mess I'm facing..)
I have a model built that calculates the daily Profit and loss ('Data for Pivot' tab, column M). I would like to calculate the cumulative profit and loss and am having a difficult time doing this with a simple formula. The Daily P/L column is empty (#NA value is in the cells) up until cell C138 as there is no data before that (which is the way it is supposed to be). However, once data has begun to be populated the daily profit and loss is shown (in this case from M138 onwards). The issue I am having is that I cannot seem to be able to create a formula that will capture the cumulative P/L. My desired result is evident in cells N179 onwards, as it simply captures the cumulative p/l. However, this only works if I change the formula once data is populated (in this case cell N177 is where i put in a new formula; the previous formula can be seen in cells N3-N176). If I attempt to put in the formula that is in cell N177 for all of the cells in column N, it gives me an error. Thus, I think that this may need to be programmed via VBA, but I have not been successful in doing this. The issue is that the data population in this model begins on different cells based on the different inputs that I put in ('Mov_Avg_Chart' tab, cells c6 and c8). Thus, I can't hard-code a formula based on a particular cell. If anyone can help me with this,
View 8 Replies View Related