I would like to be able to search through a column of numbers (say A1=2, A2=4, A3=1, A4=7, A5=5, A6=2, A7=3) and for the formula to list all the possible combinations of cells that add up to a particular number, in this case we will say 7 (i.e. A4 … A1+A2+A3 … A2+A3+A6 … A2+A7 … etc.).
Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies View Related= SUMIF( 'Worksheet'!$A$6:$A$22,"*Living*",'Worksheet'!$P$6:$P$22)
But i has one a major defect, it doesn't search the whole worksheet, the reason why i limited the search area (Worksheet'!$A$6:$A$22) is because this formula must only find the word 'living' under one part on the worksheet which its rows have the number 3 in them. So from A6 to A22 all rows have the number 3 in column D but after the A22 the number changes to 4 and so on.
Is there a way that the formula can search the number 3 in Column C from C6 to C500 first, then find the word Living from A6 to A500 then sums the rows the formula has found from P6 to P500.
I import excel generated data (in form of a worksheet) from our supplier provided programs into an excel spreadsheet. My excel spreadsheet then performs an analysis using only certain data columns of the imported data.
Background:
1. Currently, my excel spreadsheet has formulas that includes the cell references of the imported data.
2. However, the suppliers' data format may change - what I mean is that another supplier program may have the required data in a different column. I use the same program for all suppliers.
3. What does not change is the heading titles of the imported data worksheet - it is basically consistent among the different supplier programs.
Question:
Is there a way to write a formula so that the formula can go to the imported worksheet and search for a specific heading title of a column, and then place the data from that column in another worksheet?
In other words, let say a specific heading title in the imported worksheet is "Total Benefit" and that it is located in column D and the data needed is in rows D2 - D60 (inclusive).
Let's say - in another worksheet, I need the data in cells D2 - D60 of the imported worksheet in cells A10 - A68.
Currently, I write the formula in A10 and copy it to A11 - A60 knowing that the needed cells of the imported data worksheet are D2 - D60. But another supplier's program may have the "Total Benefit" in column BB (and therefore the required data in cells BB2 - BB60).
Therefore, if my formula in cells A10 is Imported sheet!D2 x 100 - it will be incorrect when I import this other suppliers' data (because it has the required data located in cells BB2 - BB60).
Therefore, if the formula can search by heading title (which is constant amongst the suppliers data), it will make it easier to locate correctly the required cells.
In other words, can the formula perform a search on the Imported sheet! worksheet for the heading "Total Benefit". Once it is found, the formula then takes the data in that column - obviously, it has to match up properly - in that the A10 takes the data from D2, A11 from D3, A12 from D4....
Constraint - Cannot use VBA macros in this solution.
I attached an example excel sheet. I tried to figure out but could not get a way. I need a formula which takes the value in D2, search it in column A, if the value is in the list, type the corresponding result in E2.
View 7 Replies View RelatedI need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".
Case is not sensitive.
Words
Title
Displayed?
Christmas
Coworker has chronic hiccups
Yes
Excel
I Love Excel
Yes
Cartman
I Like Turtles
No
Ninja
Oh Christmas Tree
Yes
Tiger
Case of the Mondays
No
Chronic
Cute Monkeys
No
I have two cells. The first cell has the formula:
=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))
which results in a col and row number (such as D65).
The second cell has the following formula:
=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2)
^^
I wish to replace the 'D1" in the Match function with the results of the first cell's formula.
I assume Indirect would work, but I don't know how to code the formula to use it.
I would like a formula for cells B1, B2, & B3 that will give me the sum of the numbers in Column B which are next to their corresponding plan in Column A. The attachment is a sample of a sheet that will be having approx 500 rows to work with. I have tried using the "Sumif" funtion, but I can't seem to get it to work correctly.
View 4 Replies View RelatedI have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??
View 2 Replies View RelatedI 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 RelatedNeed a formula to calculate a absolute total from a column of totals.
I thought this was simple, but the formula I made didn't work.
I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
is there a way I could search a column i.e column M to find out how many times a number apears in it i.e if the number 3 was in 100 time over 1000 rows then it will return the total of 100.
I don't want the code to add the the number i.e. it find 3 one hunder times and returns 300.
there will be diffrent number is column M i need a total of how many times they are repeated, i.e dont want it to show up in a message box
I need the information to be sheet 2
ie.
3 found 300
7 found 22
100 found 5
I need to see if either of the two numbers exist in a list and if yes, then perform a function accordingly. The exact case is if 0 or "NA" exist in a list in range A1:A20, then I need to print message box with message that "Either of the two numbers exist". Not really sure how to write a small code in visual basic to do this.
View 4 Replies View RelatedI have 2 columns of data (Column A and Column B).
I would like to be able to search the two columns and see if any of the data repeats in either column. Here's an example:
15844721565538
15907771565575
15936501574897
15946991576501
15970061576502
16095501578841
16124741578842
16124781578843
16124821578844
16166161578954
1616781 1616781
The list is much longer than this. I need a formula or whatever that would scan both lists and say, nothing matches, or 1616781 matches or highlight the matches or whatever, just so long as I don't have to go down the entire list multiple times.
I would have downloaded Htmlmaker and provided a glimpse that way, but the web site is blocked here at work.
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows.
Columns are:
ID- a unique iderntifier for each entry- integer
Date- date the entry was made- date
Project Number- a reference to an internal project file- string
Operator- company we were working for- string
Installation- where the work took place- string
Category- type of problem encountered- string
Application- field of problem encountered- string
Issue- string
Background- string
Problems- string
Lessons Learned- string...........
I have the below data to look at every day. The Data codes column A contains a part number and the Info Column B, the information about the part. I have in column C the serial numbers that change daily, I copy and paste this column in everyday.What I would like to do is search within the serial numbers for the data codes and return in the Result column D the info relating to the data code.
Data codesInfoSerial numberResult118Type A118F2041Type B118F BNG59617Type C2041F2151Type D2066F=9617570Type E2151F800Type FEXPEDITE COPY:570F=MEDIACOM PDF687Type GEXPEDITE:118F
Currently I spend hours removing all of the text and symbols (there are normally 2500 to 3000 serial numbers) and then do a simple vlookup. Is there a formula that could search the serial number to find the data code? Or some code I could use in a macro to do the job for me?
The serial numbers change daily but the data codes and info in the first 2 columns remain contstant - there are nearly 250 data codes.
how do i count through a string of numbers until a specified number is found?
View 9 Replies View RelatedI am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
I would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers. how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital
View 3 Replies View RelatedIs there a function or macro that can take a list of about 200 numbers and search for these numbers throughout the workbook, If 1 of these numbers exists anywhere in the workbook, it changes the color of the number or does something to the number where I will know this number is part of the exception list? The list of 200 numbers in non changing, however the numbers I want searched will change daily. There will also be worksheets added and taken away that contains these numbers.
View 9 Replies View RelatedI have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
In sheet lists I have a list of names and to the next column, I use 0ne 0f these threee options 1/3 OR 2/3 OR O(zero). So what I need is this When a user choose name from the combobox and then type numbers in textboxes, I d; like these numbers to transferd in data base column M, but if number of the specific customer in Lists Sheet is 2/3, then the 2/3 of the choosen numbers should transferred rounded to the nearest biggest number. Example: If I choose Maria, and type 30--10--15--25--40, in text boxes, then numbers 20---7--5-17--27, should transfer in database column M.
If i choose Stevens, then numbers should tranfered as ii is. No chance.
I am setting up a spreadsheet with two columns. The first column will contain a persons name the second column will contain four numbers.
Name Numbers
Joe 1,3,12,24
John 4,12,23,24
Jill 6,14,19,26
I need to be able to search to find the following: the correct sequence of numbers e.g. if 1,3,12,24 were pulled out then that search is easy. If I was looking for someone who had the numbers 1,12,24 then I have a problem. I was thinking about putting the four numbers in to four separate columns but my Excel knowledge is poor.
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.
View 3 Replies View RelatedI want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies View RelatedIs it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them
View 9 Replies View RelatedI'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?
View 1 Replies View RelatedI have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.