I have a list of numbers which I need to compare to a range of numbers. Each number in the list needs to be compared to the range, and depending where it falls within the range, a particular cell needs to be referenced in the result. I've attached the xls with the explanation.
I was going to do this with a nested IF, but with a max of 7 this will not work.
Attached File : Help with comparing to a range.xlsx
I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.
View 2 Replies View RelatedI would like to count all of the cells in a filtered range which contain specific text. These cells will also contain numbers.
The cells either contain one of the following
EXA 130
130
EXB 130
The number could be any number (not just 130) the text will only be EXA or EXB
So I am looking for the number of cells in a filtered range (visible cells) which contain EXA (plus any number)
SUMPRODUCT(SUBTOTAL(102,OFFSET(X8:X3000,ROW(X8:X3000)-MIN(ROW(X8:X3000)),,1--(X8:X3000,"*EXA*")
The above doesn't work and I have attempted lots of variations of this with no luck.
I am trying to find the right format that will show a variety of account numbers properly.
I have these types of numbers displaying like this right now:
9.04585E+13 (should be 90458524211818)
785656.001 (showing properly)
NRDE1312089 (showing properly)
SCGA 396.00 (showing properly)
The formatting is set to general, which works for all but the top type of account number. I tried setting to a custom format like "#.#######" ...The only problem with that format is that the "." shows up whether there is a decimal in the number or not. What's the way around that?
I have 3 sheets. Sheets 1 & 2 contain lists of data occupying columns A to M. Although the data on these sheets generally will be different, there are occasions where sheet 2 may contain identical rows to sheet 1.
What I want to do is two separate things -
copy the duplicated rows to sheet 3, and then in columns N & O indicate the row numbers from sheets 1 & 2 where the duplicates can be found (this only where the entire row matches)
do the same as above, but only where the contents of columns I & J match (rather than the entire row).
[URL]
take a look at the attached example and let me know how to make the formula work?
For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.
In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".
I'm on Vista with Excel 2007, HOWEVER, the solution must be Excel 97-2003 compatible.
I have two columns. One will a Y or N in it, the other will have a number. I need to sum the column with numbers, but I ONLY want the numbers added that have a 'Y' in the same row of the other column ...
I need a function to compare two ranges and return a match. For example
Range A: {A56, B43, C98, D44}
Range B: {A33, G89, D44, K123, OB55}
the function would return D44. Is it possible at all with functions or do I need a macro?
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
I am building a spreadsheet that references 'LHA rent rates' listed in other cells.
Example table
Bed rate Code: LHA per week:
0 - £55.00
1 - £91.85
2 - £109.61
(Ie two columns with six cells. The first number before the dash are in a separate column. Each of these £ amounts will be in a separate cell.)
In another part of the sheet, i want to be able to type '0' in Cell 1, and this will trigger a formula in Cell 2 to retrieve the correct £ amount i.e., £55.00 in the 'example table' above. Likewise if i change Cell 1 to '2', then Cell 2 automatically updates to £109.61.
I think this would be a long IF formula in Cell 2, but how to do it.
I was wondering if someone can at least point me in the right direction with respect to comparing two numbers within a value in two different cells. For example:
If one cell has the value "AB123456"
And the cell below it has a value of "AB124658"
I want to be able to determine the difference (with VBA code) between both cells by just looking at the fourth and fifth characters ("23" and "24", respectively) in each cell value.
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 am trying to solve is as follows, I have groups of 20 numbers in columns, one per column and would like to compare the groups to determine if any group is duplicated on another line.
I had been looking at using VBA to copy the numbers to an array, sort from lowest to highest, concatenate and use that to compare each group. If possible however if there is a suitable formula it would be preferable.
I have attached a small sample to illustrate.
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 have some problem with my data here. I have created a formula to compare the numeric value between the column. I want to have the results in the column but the results I have is wrong. Did i did something wrong with the formula?
I wanna compare the value from A2 and C2, B2 and D2. If the results is correct, then is will display true value in e2 else will display false value in e2.
when i compare 2.3oz and 2.25oz, the value displayed is true which is wrong, it should display "false"
I have attached the file here...
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 am wanting to check columns B:L for data entry on each row. Going at most 500 rows down if that is relevant. If there are no numbers on the row in those columns, then I want either the whole row or just the column range to be highlighted, whichever is easier for a solution.
I think this can be done by defining a named range using some sort of count function and then using the named range in conditional formatting. But that is just concept and I can't actually make that happen.
I have to create ranges of unique numbers that follows the below rules.
1) Have to start with 3 standard digits (ie 101 or 102 etc)
2) The total length of the is has to be 10 digits (ie 1010123456)
3) The second part (the last 7 numbers) must be unique!
4) Can create a lot of numbers (more that 5000 rows)
5) thats all with the unique numbers
Continue,
Somewhere in the sheet has to be a search function to find where a specific id is located, make it red and copy/cut in to another sheet.
I have a spreadsheet that has a column of numbers some of which are consecutive, some of which are not. I would like to have a way to lump all of these chunks of consecutive blocks into ranges. For example:
2759
2760
2761
2762
2764
2765
2766
2768
2769
2773
would return something like:
2759 - 2762
2764 - 2766
2768 - 2769
2773
Any ideas?
I am doing some data coding using Excel and I am unable to figure out how to replace time ranges with numbers. For example, in my data I have
12:08
12:12
12:15
12:27
12:35
I want to code the data by changing any time within the range
12:00-12:10 into 1
12:20-12:40 into 2 and so on.
I have a sheet with a list of cost values and I need to be able to total each set. Each set begins with the word "COST" and ends with empty cells. Some sets have no values, just the word "COST"
There are currently about 500 cost sets, from row 1 thru row 2194.
Here is an example of what Id like to accomplish:
BEFORE:
One of the spreadsheets we have here keeps track of reservations booked in the following way.
FG
101/05/200801/08/2008
201/06/200801/09/2008
301/06/200801/07/2008
401/06/200801/06/2008
The date in F is the check in date and the date in Column G is the check out date.
There is another spreadsheet in the same workbook that counts the dates that these people are checked in.
For example:
AB
1
2JanuaryRooms Occupied
310
420
530
640
751
863
972
1081
119
1210
1311
1412
As you can see column B7 has one room because of the fact that the reservation check in of F1 started on the Fifth of January
B8 is Three because reservations 1-3 are checked in on the sixth of January
B9 is 2 because reservations 1-2 are checked in on the seventh of January
B10 is 1 because reservation 2 is checked in on the eighth of January
Please note that F4:G4 does not count on this worksheet due to the fact that they checked out on the same day as check in.
I would love any help you could give on this confusing subject.
Cells in row C have numbers (number of days between date a and date b)
I want to group the days so I can run a usable pivot table as follows:
Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +
My attempt is as follows but only give two results and not 4?
=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View Relateddefinitions for static ranges and dynamic ranges?
View 9 Replies View RelatedI have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
In the attached file, I have variable range in column A:B, column C:D and in column E:F
I want a macro to do the following:
Start with sheet "A", select the available range in column A
then copy and paste in the sheet "B" but with all the cell values added with the value in H1.
Then in sheet A, simply copy the available range in column B and paste it in sheet B
Do the same until column F in sheet A. Pastespecial if it is odd column. simple past it is even column.
I know the macro code for the simple paste. But I am struggling with the paste special code.
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
Attached is an example.
I get data pulls of logged in time, and shift start times.
Example in column C the person's actual login time is there, and column D has their start time. If there's a variance of 'x' how could I have column E display the variance? In this example 15 minutes?
Basically, if there's a difference between the 2, I need a formula that tells me what difference is.
Attached File : Login Time.xlsx
I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.