# Sorting Rows With A Combination Of Text And Number?

Aug 3, 2013
I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."

e.g.:

My field:

1

2

N.A

4

5

N.A

9

I want to sort the cells by descending value. But the result appears as:

My field:

N.A

N.A

9

5

4

2

1

But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."

EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."

View 5 Replies
ADVERTISEMENT
Oct 2, 2013

I have a series of identification values that begin with text and end with a number. The trouble with sorting on this field only affects where the the first 9 entries end up since the number portion is not prefixed with a zero. Is there a way to sort these numbers so that ID2 (and not ID10) follows ID1?

ID1

ID2

ID3

.

.

.

ID9

ID10

ID11

View 4 Replies
View Related
Apr 15, 2014

I have two columns, left is called OD right is called ID. In each OD there are multiple ID's but this is shown for example as:

OD-----------ID

254000----0

254000----127000

285750----0

285750----127000

285750----158750

304800----0

304800----127000

Therefore, showing duplicates for the OD. The required format is:

254000---0---127000

285750---0---127000---158750

304800---0---127000

This shows each OD in the first column (no duplicates) and the corresponding ID's in the columns on the right. The example is done with small amount of numbers however, the data I am working with consists of thousands of rows therefore is not practical to do by hand. Please see attached spread sheet for example template with both unsorted and sorted data shown.

I attempted to do this by using the following code:

[Code] .....

Attached File : testing.xlsx

View 3 Replies
View Related
Oct 29, 2007

I am trying to sort in rows, a large number of rows. I can do text to columns, delimit by colon, and sort individual rows. But I have spreadsheets that are 100s of rows long. Is there anyway to automate?

View 4 Replies
View Related
Jan 11, 2008

sort the list below:

40105

40106

40107

40105A

40105B

40105C

40105D

40106A

40106B

40106C

and the list will look like this:

4010

4010A

4010B

40105

40105A

40105B

40105C

40105D

40106

View 9 Replies
View Related
Dec 21, 2012

I have letter and number combination code in two collumns and they differ for 10.000 numbers:

BAM98314

BAM88314

BAM90000

BAM80000

As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.

View 2 Replies
View Related
Mar 14, 2014

1

0

0

4

6

=largeodd(a1:e1)

=largeeven(a1:e1)

write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.

[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]

View 9 Replies
View Related
Jan 26, 2009

I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9

it would look like this

4 0 3 9 0 4 3 3 2

9 2 5 6 5 6 9 6 6

8 9 9 3 1 0 2 9 8

1 6 7 5 9 9 8 2 5

2 7 2 2 2 8 5 1 3

0 1 4 7 4 7 6 0 9

3 5 6 4 3 3 0 4 4

7 8 8 1 8 5 7 8 7

5 4 1 8 6 1 1 5 1

6 3 0 0 7 2 4 7 0

each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.

View 9 Replies
View Related
Aug 22, 2006

Can Excel work out every possible combination of 3 numbers from a numbered set 1-24.

I need every combination of 3 numbers.

View 3 Replies
View Related
May 3, 2012

I want to convert a Lexicographic Index Number, better known as Combination Sequence Number (CSN) to a combination using an EXCEL formula.C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.

I have a lookup table in cell M1:R56 which holds the correct data, that I am pretty sure about.

In cell G1 I have the Lexicographic Index Number I want converted to a combination.

In cell J2 I have the value 39 (n).

In cell I2 I have the value 5 (k).

In cell K2 I have the formula =COMBIN($J$2,$I2).

In cell A1 (the first number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1,$M$1:$R$56,6))

In cell B1 (the second number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,5))

In cell C1 (the third number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,4))

In cell D1 (the fourth number in the combination) I have the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,3))

In cell E1 (the fifth number in the combination) I have the formula:

=IF(G1=0,"",$J$2-($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,1)))

This works for the vast majority, but gives an ERROR when numbers 35,36,37,38,39 are in the combination, but funny enough NOT all the time.Lexicographic Index Number 575757 which is the maximum combination gives ALL ERRORS.

View 9 Replies
View Related
Jan 15, 2010

I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.

Here's what I mean (though I can't figure out how to create neat columns in this post):

[C1] [C2]

Prod1 CharlieCo

Prod3 AlphaCorp

Prod2 BetaInc

Prod3 BetaInc

Prod1 AlphaCopr......................

View 5 Replies
View Related
Oct 28, 2011

I'm looking for another excel game changer (for the work I do anyway).

I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?

Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.

View 6 Replies
View Related
Jul 9, 2008

I have a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).

The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.

Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.

View 9 Replies
View Related
Jan 30, 2013

I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".

So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?

Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.

The purpose of this data is for reference to question #2, so that i can utilize some percentages.

View 1 Replies
View Related
Jun 19, 2007

The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it - sorry!

Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file.

The number of columns and number of rows in each column will be changed regularly ...

View 9 Replies
View Related
May 22, 2014

I need a formula that can provide all possible alphabet combination in another cell .

example

COLUMN A A1 = RED

COLUMN B B1:B50 = BELOW COMBINATION / RESULT

RED

ERD

DRE

EDR

DER

RDE

View 8 Replies
View Related
Mar 9, 2014

I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.

Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.

I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.

I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1

View 3 Replies
View Related
May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",

IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

View 6 Replies
View Related
Aug 14, 2007

I have a problem of having to repeatedly copy and paste a series of data based on the number of occurrences in each row of data series. For instance:

[Road Name] [Building Name] [No. of occurrence]

(1) [Scotts Rd] [Tangs Building] [38]

(2) [Orchard Rd] [ABC Building] [3]

(3) [French Rd] [DB Bank] [1]

I will need to duplicate (1) 38 times with the location name and building name. Similarly, I need to duplicate (2) 3 times. How can I simplify it programmatically?

View 3 Replies
View Related
Jan 25, 2013

I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance

I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)

Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..

Is there a possible way for the formulas to "find" the correct line to start calculating data?

View 6 Replies
View Related
Apr 5, 2012

I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.

Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?

View 3 Replies
View Related
Apr 26, 2007

For example

Name Address Phone # zip

Danielle 4561

Danielle 9852

Danielle 22

Danielle 69

Joe 895

Joe 28

John 9821

John 1114

John 698

Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?

View 11 Replies
View Related
Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.

View 9 Replies
View Related
Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO

406AMZN

451AMRC

404AMAD

605ANCC

405ADRC

The result would be:

302 ALTO

406 AMZN

451 AMRC

404 AMAD

605 ANCC

405 ADRC

View 6 Replies
View Related
Jan 10, 2008

I have data that I am trying to sort that starts with a number and contains a number within, then sometimes ends with a letter. Here is the dataset I will use as example.

1-TP-2

1-TP-4A

1-TP-11A

1-TP-12

13-TP-22A

13-TP-22B

13-TP-102

13-TP-103A

13-TP-103B

Excel wants to sort as:

1-TP-11A

1-TP-12

1-TP-2

1-TP-4A

13-TP-102

13-TP-103A

13-TP-103B

13-TP-22A

13-TP-22B

Is there a formula that I can use to sort the data in numerical order by the last digits exactly like the first dataset? There are all sorts of veriations of the first digits and the last, but the "-TP-" always remains constant.

View 11 Replies
View Related
Nov 14, 2008

I'm running Excel 2003 on Windows 2000. I'm working with a worksheet that is about 10,000 rows long and 25 columns wide. The first and second columns all have data in them, and the third through twenty fifth could have data. From row to row, if there is data present it will be in columns from left to right. By that I mean that if there is data in four columns of a particular row, it will always be in columns 1-4, not spread out throughout the 25 columns.

What I'm trying to do is sort the data in each row so that values ascend from left to right. Here's a very simple version of what the data looks like:

A B C D E F

1 a 6 3

2 b 5 9 2 8

3 c 6 5 2

4 d 9

5 e 3 9

When I'm done I would want the data to look like this:

A B C D E F

1 a 3 6

2 b 2 5 8 9

3 c 2 5 6

4 d 9

5 e 3 9

[url]

View 9 Replies
View Related
Jun 4, 2009

how do I cut rows and paste into another sheet/file based on the values in a row.

For example in row A

A

1 123

2 123

3 123

4 333

5 333

6 444

7 333

8 444

I need to copy all the 123 into a sheet/file and the 333 into another sheet/file.

View 9 Replies
View Related
Mar 11, 2009

I have a spreadsheet with information in columns a-x. In column A there are part numbers like: RH630-34, PH630-343, 6-255, 16-01, 72500, There are may combinations of just numbers, and numbers first letters second, and letter first number second.

All usually seperated by a hyphen. The entire spreadsheet will be sorted by Column A first.

I need to sort them so the order would be numbers first and combo with number letters next. finish product: 6-255, 16-01, 72500, PH630-343, RH630-34. Is this possible? I have seen other posts and suggesting putting spaces before the numbers. That seems to work but in the case of 6-138 and 6-1038 the 6-1038 is first

View 2 Replies
View Related
Apr 20, 2012

How to correctly sort data when it contains both text and numbers.

This is what is currently happening

LA100LA102LA109LA11LA117LA118LA12LA120LA121LA199LA21LA216LA220LA221/SLA227LA229LA23LA230LAK102LAK107LAK11LAK117LAK120

BUT I NEED IT BE IN ORDER LIKE THIS -

LA11LA12LA21LA23LA100LA102LA109LA117LA118LA120LA121LA199LA216LA220LA221/SLA227LA229LA230LAK11LAK102LAK107LAK117LAK120

View 2 Replies
View Related
Dec 30, 2008

I have part numbers in a column that look like the numbers listed below. All numbers begin with A, so the default sort begins with the first number. I would like to sort the list using the middle three numbers denoted by the red x's in the first example. Is this possible?

A 385 XXX 0055 A 385 466 0060 A 385 466 0160 A 385 584 7024 A 387 284 0185 A 388 017 0160 A 389 260 1485 A 389 262 0293 A 389 262 4935 A 389 262 9134 A 389 267 2819 A 389 267 3319 A 393 328 0065 A 398 267 3319 A 403 990 0210 A 403 997 0620 A 404 260 0074

View 9 Replies
View Related