Find/create Missing Codes
Dec 28, 2008
I get a ledger dump every month with 20,000 lines of all transactions. There are 1,200 ledger codes, ten departments, and thirty activities. Each of the 20,000 lines has columns with the ledger code, a department, an activity, and other data. There are tens of thousands of possible combinations of ledger code, department and activity.
I have most of the ones which are commonly used every month, and I use them to create a =sumif summary which totals all the same concatenations of ledger code&department&activity. However, every now and again a new permutation arrives and I need to find it and then create the new concatenation for the =sumif to use in the summary. Is there any way to do this by running a routine which will locate the new combination and create the concatenation in the =sumif summary?
View 5 Replies
ADVERTISEMENT
May 5, 2014
I am attempting to write a small code for the following scenario.I have a workbook with worksheets GP1, GP2, GP3 etc & a template page as the last tab.
All the worksheets column names are same but have different data.Last worksheet is the template for all of them. How do I create a command button in template page... upon clicking the button,
it should create a tab just before the template worksheet incrementing previous tab GP3 +1-> GP4. GP4 should be a copy of the template page along with all the vb codes associated with the template page.So tabs looks like=> GP1, GP2, GP3, GP4, template
View 5 Replies
View Related
Oct 15, 2008
my w/s has a column for codes and then currency columns. Users may select codes from a list of over 600 codes. I need to enter a formula that subtotals by each code used.
table looks like this:
CODE 1Q$ 2Q$ 3Q$ 4Q$ SUBTOTAL ADJ ADJ ADJ TOTAL
8810 10000 10000 10000 10000 40000 50000 45000
8910 10000 10000 10000 10000 40000 50000 45000
8810 10000 10000 10000 10000 40000 50000 45000
8811 10000 10000 10000 10000 40000 50000 45000
so in this example I need subtotals for 8810, 8910 & 8811
not sure if I need to use a pivot table, an array formula or if statement.
View 9 Replies
View Related
Oct 18, 2007
I am using the following vba code to find numeric codes in a column and replace them with the same code along with descriptive text:
Range("Q:Q").Select
Selection.Replace What:="11", Replacement:="11 - GSA"
Selection.Replace What:="10", Replacement:="10 - NYS OGS"
Selection.Replace What:="1", Replacement:=" 1 - Dist Sale"
Selection.Replace What:="2", Replacement:=" 2 - Direct Sale"
Selection.Replace What:="3", Replacement:=" 3 - No Sale: Warranty Exchange"
The problem as you might already see is that when the replacement of "1" happens, Excel will replace the 1 in the 10 and 11. Of course I want to only replace the number 1. Is there a better way to code this operation?
View 9 Replies
View Related
Feb 19, 2010
My question is how many parts havinig end with C?Also arrange these parts in ascending order in a new column. Refer to the attached file and expected result also.
View 5 Replies
View Related
Sep 22, 2007
I've created a Excel spreadsheet. In the Spreadsheet I've got one worksheet which has columns and list of codes.
I would like my other worksheet to look through find any products I've ordered and list them with their codes and quantities, creating a summary of just what I want so that I can e-mail just the important bits.
View 14 Replies
View Related
Jul 20, 2014
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
View 3 Replies
View Related
Apr 19, 2013
I'm sure this query has been answered somewhere else but I can't seem to find it. I basically have information in two worksheets in the same workbook which need to be compared and the missing values from one worksheet need to be listed in a message. There are duplicate values in both worksheets so only need a list of the unique missing values. For example:
Sheet1
Column A
1
2
1
5
5
2
3
5
4
Sheet2
Column A
2
3
3
4
3
4
The message box should state that we are missing 1 and 5 from the dataset as it is not in Sheet2. If the list could be sorted in ascending order that would be great too.
View 9 Replies
View Related
Oct 7, 2009
Say i have a range A1 to A10 that contains these numbers
101
102
103
104
356
106
225
107
108
109
I need some code that will help me find the next highest missing number in the value range of 101 - 199 in that range A1 to A10. In this case it would be 105
View 12 Replies
View Related
Apr 17, 2008
I have a file with approx 3600 rows. In Col B there is a 13 digit EAN-13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the B-column to the cell below and find out if there are some missing numbers. If the numbers are incremental then goto next cell. But if there's a gap in the numbers then it should insert the missing numbers with an increment of 1.
I think the logic statement should look like this:
i = 1
For x = 1 to last row
If the gap between B(i) and B(i+1) < 1 then
Insert a row and put the missing value in the cell on the new row.
Endif
i = i+1
Next x
The comparison has to be made from the 5 digits in place 8-12 in the code.
Here's an example (i've dimmed the digits that shouldn't be compared):
7330985920714
7330985920721
7330985920738
7330985920776
As you see there's a missing number between the third and fourth row. Here the value should be 733098592074 + the checksum that is a bit tricky to calculate.
Between the fifth and sixth row there are four missing numbers that has to be filled in.
The checksum is OK to leave out at this moment. The important thing is to get the 5 digits.
View 9 Replies
View Related
Mar 12, 2013
How I can go about finding missing entries. I have an excel sheet that has column A as employee name and column E with a date. There are 10 employees so there should be 10 entries for each date. I need to be able to determine which employee hasnt entered information for a given date.
View 1 Replies
View Related
Apr 24, 2009
i had a worksheet in which i had a column Named Cheque No. i want to print the missing cheque no's in a series of cheque no's.The logic here is that there will be different series of cheque nos for different transactions.Just check out my worksheet.
View 9 Replies
View Related
Oct 2, 2009
i have two chart A and chart B from ,chart A got nine repeting numbers and Chart B will show what is the number not in chart A or is outside from this number 0.1.2.3.4.5.6.7.8.9. i want auto generate to chart B.
View 2 Replies
View Related
Mar 13, 2009
I have two columns
Date Cab No
1 4501
2 4501
3 4501
5 4501
1 3603
2 3603
5 3603
The cab no 4501 is absent on 4th & cab 3603 is absent on 3rd and 4th i have more than four thousand entries with 50 cabs how to find with count if or any other formula the missing dates of the cabs
View 9 Replies
View Related
Dec 24, 2012
i need to find the missing numbers from 12 rows of 20 numbers.
ie, i have these numbers (only between 1-80) and need to find the *normally* 2 numbers that are not in that list between 1-80. there are 14 rows in this example
134810131417182832464855565758596475
412131417212223303235374044545861707880
57101117202528303435363940495155606264
35121823254148495051565860646670767879
311161719212225262829404447586063697578
126713162326353638394652535863707678
710172021272930414445464750515569727374
[code].....
these numbers wont be the same every time, but in theory after i enter 12 rows there should be only 5/6 numbers left max.
i need a script so that when i copy/paste 12 rows, i can be presented with the numbers not in that field. or even if i have a field of 1-80 displayed, then i copy paste my numbers in the fields below and can somehow highlight which numbers between 1-80 aren't in my pasted fields on the 1-80 field.
btw the numbers missing here are 24-68
ps im using excel starter 10 if that makes a difference
View 6 Replies
View Related
Jul 17, 2013
I have two list of names, mostly duplicates. List in column B has about 30 more names than the list in Column A and I need to identify which names are in column B that are not in column A. Names are in exact same format since they were pulled from the same data base.
View 1 Replies
View Related
Nov 21, 2012
Is there anyway to find missing numbers from column by excel formula?
Here is an example:
A
B
C
01 06 44
01 31 45
01 39 48
04 21 27
07 21 44
27 30 44
[Code] .....
Missing Numbers
32, 41, 49
02, 06, 11, 14, 17, 25,33, 34, 46, 47, 50, 51
04, 05, 13, 20, 29, 33, 40, 41, 49
View 8 Replies
View Related
Jan 29, 2007
I am trying to write a code enabling some calculations. As part of this code I want to find an integer in an excel range. I set the variable (integer) as Rvalue and wrote the following:
Selection.Find(Rvalue).Activate
which works fine as long as the find is successful. The issue I have however is when the variable cannot be found. I tried using the "iserror" function in vba but it appears that excel/vba does not recognise this issue as an error; I could use an excel formula (i.e. if(iserror(vlookup...) but I would prefer to find out how to handle the issue in vba.
View 2 Replies
View Related
Sep 15, 2014
i got worksheet which i store a list of all filing number in the same Col "R"
The combination of Filing Number Example
1) Invoice Filing Number = Inv2014070001 and continual
2) Purchase Order Filing Number = POD2014070001 and continual
3) Sublet Filing Number = SLT2014070001 and continual
Currently i am using a code which it can find the last filing number return to my userform textbox and increase the last number by 1. but this code can only return the very last filing number.
i need to find all the missing filing number in between all the filing number in Col R and return it to my userform listbox
VB:
If me.Remark.Value = "Sales" Then A = "Inv"
If me.Remark.Value = "Purchase" Then A = "POD"
If me.Remark.Value = "Sublet" Then A = "SLT"
Y = Me.TransYear.Value
myName = UCase(A & Y)
[Code]...
View 9 Replies
View Related
Dec 13, 2013
I have a list of products and would like to find what are the missing products which aren't ordered yet from the customer.
As the example: let's assume numbers from 1 to 9.
And I have some numbers, like this: 2, 4, 6, 7. What is the formula which can copy automatically the missing one into one cell?
See the attachment : howtodothis.xls
I know, how to copy multiple cell into one cell (like A4&", "&", "A5 etc), but how to find the missing numbers?
View 8 Replies
View Related
May 5, 2006
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View 3 Replies
View Related
Jul 8, 2008
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
View 9 Replies
View Related
Mar 27, 2007
I have two sheets in my workbook: One containing names with French and other European characters, the other containing a list of ISO entities for these characters, like these:
à#224
á#225
â#226
ã#227
ä#228
å#229
æ#230
I need to replace these characters in Sheet 1 with the equivalent ISO entity from Sheet2 and print the value into the same cell in Sheet1.
View 3 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Feb 5, 2009
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
View 6 Replies
View Related
Jan 22, 2014
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
View 4 Replies
View Related
Jul 14, 2013
I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.
******** language="JavaScript" ************************************************************************>
Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
(A)boutA1=ABCD1This WeekLast WeekMissingAdded
21002103110311002310271032103210274103310331034104051035103410361055610371036Â
165571038Â Â Â 81040Â Â Â 91055Â Â Â 101655Â Â Â Sheet1Â [HtmlMaker 2.42]
View 2 Replies
View Related
Jul 15, 2014
I need a macro that will search a value in column D (sheet2) that is noted in cell C7 on sheet3. After that it must copy enitre entire row where searched value was found to sheet3, last empty row.
an example:
entered value in cell C7 on sheet 3 is ABC
on Sheet2 column D, value ABC was found in cell 150 (D150) so it has to copy for 150 to sheet3, last empy row.
View 12 Replies
View Related
May 3, 2014
I want to create a customer ID for thousands of customers and then be able to find the customer by doing a search for the ID later. I'm even thinking of using a customer phone number for the ID. What function can I use to later find the customer?
View 10 Replies
View Related
Jan 15, 2009
I'd like to create a button that makes the "Find and Replace" popup come up when pressed. How to do this? (Ctrl + F is the shortcut for it)
View 3 Replies
View Related