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 RepliesSay 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 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 workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies View RelatedIs it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
I have a chart of accounts that is probably 30,000 accounts long. I have another list with say 500 accounts. I need to check to see if the 500 accounts exist anywhere in the 30,000 accounts. The next step is, the 30,000 accounts is separated into different categories, so say it's 30 blocks of 1000 accounts. I need to check the 500 list in each of the blocks. I'm just trying to see which accounts are missing from the 30000. So if they exists in each block then all is good, if they don't, we have problems.
View 3 Replies View Relatedis 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
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
[URL] .... If you take a look at the picture, what I'm trying to do is add the list of Prices up so that I can differentiate the amount received in $ and in £.
I need to add up all the numbers that don't have a $ in front into the 1 cell, and all the ones with the $ in front into another cell.
FEdLkuj.jpg
Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33
View 2 Replies View RelatedA
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.
Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)
PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.
2- I have another list with 2 columns which arranged in order. I mean:
A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.
My request is I want to make this list 1 column as arranged.
A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.
3- Macro will add a new line to the end of the above list with this format:
Z00001xxxx
Z00001 is static
xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits.
Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
I 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.
= 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 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.).
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 RelatedLet me see if I can explain my question in an understandable fashion....
I have a table containing data for about 2000 ID numbers. Some of these numbers are unique and some are duplicates. I would like to convert the ID numbers into a consecutive list of integers while preserving the unique numbers. For example, if the first column of my table is currently:
ID#
18578
19644
19644
20247
20974
21361
21361
21419
I would like to change that to something like:
ID#
1
2
2
3
4
5
5
6
I need to know which records (i.e. which rows of data) are from the same ID# but want to remove the actual ID#.
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 RelatedI have to create lists of data in the following format:
123121321,12321215,121351213 (numbers with , with no spaces)
These numbers are sent to me in excel in columns and I need to convert the columns into rows using the format above.
The formula I have been given:
=INDIRECT(ADDRESS(((ROW()-1)*10)+2,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+3,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+4,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+5,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+6,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+7,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+8,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+9,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+10,1,1,TRUE,"SSOs")) & "," &
INDIRECT(ADDRESS(((ROW()-1)*10)+11,1,1,TRUE,"SSOs"))
currently I only get 10 numbers per row
I need at times for up to 500 numbers to be listed on a row. However even when I try to extend the formula it still only gives me 10 numbers per row and breaks up the 500 numbers to 10 per row.
Is there another way to combine up to 500 numbers in one row?
From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed
For example
34.95
21.88
21.00
56.00
45.77
Only those valaues ending in .00 will list. I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents
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 currantly have in a cell this:
=IF(Search1="","No Place Found",INDEX(Locations,MATCH("*"&Search1&"*",Locations,0)))
Which searches a large column called Location. The above statment searches for a word that matches the Search1 cell...
What I would like to add, is the ability for it to list, in a drop down box all the contents of the cell that the word is matched to, i have tried, but feel that this is out of my own and books expertice!!
I attached partial file so you can see what i mean. I pasted only a few combinations cause the file was to big and i wasnt able to attach it...there are 142506 combinations . But you can see what i mean. A VBA or a macro on the worksheet will do the job?
View 9 Replies View Relatedis it possible to search a drop down list for matching values?
View 8 Replies View RelatedI created a drop down list with the names of 700 of our clients. Now what I want to do is, just as with Access, type a letter and it will bring me to that part of the drop down list instead of having to scroll all the way to S, for example. Is there a simple way to do this WITHOUT VBA?
I am using XL '07
On a related note, I am using VLOOKUP to print the Client ID right below this list. For some reason, I can't figure out it is not printing the correct client id but the one right above it.
here is my formula
=VLOOKUP(B6,H:I,2)
B6=drop down box
h=client names
i=client id's
2=print client id's
I'm looking to make a macro to search within a list of dates (In column A).
I need it to find the end of the year if it's 2007, 2006 and 2005, insert a column underneath and sum up column C, but only for that year. So for example, all the amounts in column C for only the year 2007 will be summed up in a blank row on column D before the 1st January 2008, and the same for previous years.
Then what needs to be done is for the year 2008, the same thing applies, but for each Month.