Comparing 2 Columns With Numbers / Letters And Alphanumeric Values

May 13, 2014

I have 2 columns i want to find out which items match in each column and put the matching value in column c. I have tried Vlookup and continue to get an N/A .. I tried countif and I get either an N/A or a value error. I have tried turning the cells into text but that is not working either..

column A has about 1700 rows and column B has about 4000

MOST CELLS ARE 6-7 VARIABLES.. satrting with either 01, 02, 03 with 4-5 letters following or have a 6 digit number or 6 letter value.

I have 3 departments, each with a value. I want to sort from lowest value to greatest (which I have done) but some departments won't have a value and therefore will have "n/a" in the place of the value. When sorting, "n/a" always comes out as the greatest value but I want "n/a" to be the lowest value - since it means there is no value.

Here is an example of the data: Depts: Value: 580 15.75 558 19.01 538 n/a

Here is the code (sorting is being done on the value obviously, and the switching of the Depts to stay with the value is also done in the code)

Private Sub RankPerformance()

Dim bytValuesArrayCount As Byte Dim A As Byte Dim B As Byte Dim vTemp As Variant 'must be type since value can be number or string ("n/a")

bytValuesArrayCount = UBound(ValuesArray)

The only way I know to do it is to sort using the above code, then do another type of sort if a value is not numeric then it is placed at the end...but I'm trying to make the code as efficient as possible

I want Excel to compare two columns of numbers. Column R has 1000 entries, beginning with the number 1 and ending with the number 1000. Column S will have from approx. two hundred to as many as six or seven hundred entries, looking something like this: 3, 5, 6, 10, 11, 13, 14, 15, 18 . . . . . . 990, 992, 995, 996, 999, 1000. I want Excel to compare column S with column R and display the difference in Column T. Column T will therefore look like: 1,2,4, 7, 8, 9, 12, 16, 17, 19 . . . . . 899, 991, 993, 994, 997, 998. I have to do this multiple times, does Excel have a built-in function(s) that can do this or do I need to write a macro? The numbers can be formatted as text if neces- sary

I have a few columns of letters (ranging from A to Z) - onyl one letter per cell. I need to be able to deduct them such that they result in a numeric difference (ie D-B=2, D-A=3). Is there any way this can be done in VBA? (The actual columns are in a word table, so would be great if there was a VBA way to do this, not an excel function).

I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.

An example:

5CZ975 Van 25RW04 Straight 5CZ975 Van ZJ6026 Van

So, the result I am after is 2 vans, 1 straight truck

I found an array in a another post, but it is not working properly (see cell F7 in the attached):

I have thousands of drawings listed in colums and each in the row has revisions P01 to P10 for Proposal and C01 to C10 For Construction. Is there a way of getting the highest revision of all listed in a row without turning them into numbers when P is older than C , but 1 is older than 10? MAX function doesnt count when value consists of both - number and letter.

Manualy selecting the highes revision will cost me weeks... and brain damage.. as I have thousands of drawings and more are coming...

As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?

I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).

Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.

I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.

I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.

I have tried a few test formulas using COUNTIF function, but with no luck.

I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)

New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx

I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.

I'm new to macros and VBA so I am wondering if it is possible for a macro to compare values in column B of sheet2 and column B of sheet3 and for the values that DON'T match i.e. unique values to column B of sheet2, copy row from sheet2 to sheet4? This will aid my processing time drastically if I can copy all the rows from sheet 2 to sheet 4 where the model number in column B of sheet 2 doesn't appear in column B of sheet 3!!

I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'

E.g.

frt 34.2 36

p34.5

In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:

column a = m454 column b = fsh454-1 m543 fst998-2 m998 fsm434-1

my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this: DCM1005-1 DCM1005-10 DCM1005A MTR1005-1 MTR1005-10 MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas) A1, B1, C1, D1 DCM1005-1, DCM, 1005, 1 DCM1005-10, DCM, 1005, 10 DCM1005A, DCM, 1005, A MTR1005-1, MTR, 1005, 1 MTR1005-10, MTR, 1005, 10 MTR1005A, MTR, 1005, A

Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be asd67,h876 --------> 67876 2,3,ujdj5&34 -------> 23534 909k86m34 --------> 9098634

I import data from another program in order to evaluate it. Unfortunately, one of the fields I need contains copyright data, however, it has been very inconsistently entered into the database. For example, sometimes the data appears "c1999." or "-1999" or "" or "[1999]" or even "19?" and also sometimes "1999, 1990" and many other variations on that. I discovered the link in the excel help file about extracting numbers from alphanumeric strings, but my situation is still too variable for it to apply; that file didn't take into account that alphanumeric strings don't always lump numbers and letters together. I was able to correct a few things, but my command of excel isn't knowledgeable enough to really come up with something effective.

Some ideas I had that I don't know how to implement: is there a way to strip non-numerical characters from an alphanumeric string? (I've been doing some find/replaces to get rid of some of it, but that is obviously not very efficient when I have to repeat this process daily.) Perhaps then I could just detect the first 4 numbers of the string somehow. However, that doesn't solve the problem of when a wild card is used as in "199?" or "20?" etc.

Bottom line, I just need to grab the first four numbers that appear in the string (but NOT additional numbers that occur after a wild card or a space if the year was not completed in 4 numbers; in that case I'd just be happy with a null value).

I've been doing this with a formula so far. My only experience with macros has been in simply recording them, not actually writing them, but I'll give anything a try.

I have about 10000 cells of varying length in text and numbers but they all end with numbers on the ends of them. Using text to columns doesn't work because they're all different lengths and it cuts them off in the wrong place.

Is there a formula I can use that will return only the text from these cells and remove the numbers. It would definitely save me time from individually deleting the numbers at the end.

I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.

01 Agricultural Production Crops 011 Cash Grains 0111 Wheat 0112 Rice 0115 Corn 0116 Soybeans 0119 Cash grains, nec 011901 Pea and bean farms (legumes) 01190101 Bean (dry field and seed) farm 01190102 Cowpea farm 01190103 Lentil farm 01190104 Mustard seed farm

I have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:

The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.

At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result: