Concatenate Text And Numbers And Then Sort On Original Number
Jun 26, 2013
I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?
Here's an example
A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5
I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:
A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5
Each column is sorted by the numeric value, but has the name label from column 1 attached to it.
I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)
Code:
Col A Col B 1Billing Accuracy 2Billing Time 3Credit Check Accuracy 4Credit Check Time 2.1Bill preparation
[Code]....
This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
I have a column of data that contains alphanumeric ID codes, some of which contain no alpha characters (i.e., Excel treats them as numbers). When I do a data sort, Excel puts all the purely numeric codes first, then the alphanumeric codes. I want to sort the numbers among the alpha codes.
I know there's a way to do this, but I've spent half an hour looking and can't find the answer. And no, manually editing each numeric entry with an apostrophe is not an option.
I have a CSV spreadsheet full of data: many rows and many columns. I want to sort based on values in some column. Then, I want to divide my large worksheet into smaller worksheets based on these sorted column values. For instance, if column 4 was 20,000 items long and contained 6 unique values I would want to create 6 smaller spreadsheets. Each spreadsheet would still have a column 4. Now, however, all the column 4 values would be the same for a particular spreadsheet. It would be nice to have a pop-up window query me for which column to use to divide the data with, instead of hardcoding a value in (like 4).
I am trying to crank this CSV file through MATLAB and it is just too big. My PC has 1GB memory and it still craps out. I am guessing that a dozen or so smaller files will be easier for MATLAB to digest. Any help is appreciated!
For the record, I am doing the following to sort a column, then copy it to a scratch worksheet and then determine all the unique values. I have hardcoded in column lengths and would really like to know how to make these variable expressions:
I've got data in a table that has am induvidual "job number" assigned to each row, but this job number could have a variation such as 1000A 1000B 1000C but they may not necessarily be one after the other in the table (other job numbers may appear in between) so i need a to macro to remove all job numbers lets say that start with 1000 and paste them and the end of the table. so i end up with consecutive numbers. Can this be done? I've played around with the find funciton to loop through the column and find all relating job numbers then cut the entire row but cant quite seem to get it to work
Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.
Example:
376 377 421 376AB 376XY 377NC 421GQ 421EF
Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:
In a spreadsheet I have a text column with entries such as: 2012 10 Times 10.5 Times 101 Times 25 Times A Pure Text Entry
When I sort by this column I expect to get: 10 Times 10.5 Times 101 Times 2012 25 Times A Pure Text Entry
Instead I get the 1st order. Why? I've selected 'Sort numbers stored as text' separately so it shouldn't be treating 2012 as a number. Besides I've checked & the cell formatting is Text.
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code: PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range) Const cnNUMCOLS AsLong=256 Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow Static rOld As Range Static nColorIndices(1To cnNUMCOLS)AsLong Dim i AsLong IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
Lets say cells A1 to A5 contain these lines of information:
Till 174 (T0215) - till keeps turning itself off. Till 245 - stuck on windows screen 116 - keyboard is unresponsive Berkel Scale is constantly beeping ped not reading cards Till 156
How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.
I need excluding text from a cell and come out with a random number up to 3 numbers. Please see the attached image;
If you notice in column D there is text and a number that starts with a decimal, for example on the first row is .437 the next row is .215 next row is .63 etc... Is there a way to come out with a random number mixing it up to 3 digits and output those number on column "E"? Im only interested in filtering and coming out with the random number i mentioned .437 .215 .63 etc... not the +/-.010in.
There is no right or wrong number, i just need to come out with random number using up to 3 numbers up.
Maybe for .437 three numbers up could be .440 for the next row .215 maybe a number up .216..etc..
Hope there is a way to do this, my excel file is too long to type a number manually.
I have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In" IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))
How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
Is there anyway of converting the output (which is 7 and in text form) to a useable number format so that I can take advantage of the VLOOKUP function (instead of getting a #N/A)....
There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:
"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.
The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.
I want to use the text to columns function when the original cell has more then one line (lines are separated with Alt+Enter). Somehow Excel does only notice the first line.
how can you custom a cell format in which you may in type in information that is displayed as ###-##-####-@# I searched everywhere and cannot find this.
For example, a medicare number.
I would need to type in 581566223b6, and it should be displayed as 581-56-2236-B6.
See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?
In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?
I need a formula (a VBA script won't work, as it also has to work in a google docs version) that concatenates numbers taken from a specific range in a single row with ", " in between, but only if 2 conditions in the particular column those numbers are in are matched.
Let's say, my numbers are in Row 1, from B1 to N1.
My conditional values are in Rows 5 and 6 respectively, also from B to N.
Values in Row 5 are text strings (Names of people), Values in Row 6 are Dates.
I want the numbers from Row 1 to be concatenated, but only if a particular Name and a date from a specific month are in the same column.
Row 1: 1 2 3 Row 5: Name Name Name Row 6: Date in Feb, Date in March, Date in March
Upon selection of a month and name in a dropdown cell menu, i want the output to look like this:
I need a mcaro to concatenate my project. I have 1 number in six separate cells that I need to be concatenated into a 3 digit number without duplicates. My data is in cells A4:C5 (six numbers) note that I have two duplicate numbers so the results should only yield (10) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly.
My data in cells A11:C12 (six numbers) are all different so the results should yield (20) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly. I also wanted to know will I be able to edit the macro to concatenate 5, 6, 7, 8 or even 9 numbers into 3 digit numbers without duplicates. If so, how can this been done? The results would be 5#'s:10 results; 6#'s:20 results; 7#'s:35 results; 8#'s:56 results;
I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).
Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this :
=CONCATENATE(B4:B127)
How do I add spaces to a range?
My problem is very similar to the question asked in this thread [URL] .... but I am using numbers and would like to separate them with spaces.
I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9). Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range?
my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html but I am using numbers and would like to separate them with spaces.
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
