I have several columns of info on my worksheet. I want to output a list of all possible permutations from this worksheet (not the number of permutations but the actual permutations themselves).
View 9 Replies
i have two columns in tow seperate workbooks to be compared:
in say inp.xls, there is a column(COL A) containting a lot of entries(numbers) - roughly around 60,000. Now in the other workbook, say mem.xls, i have a smaller set of numbers in a colum(COL B) - roughly around 300.
Now, the problem is, COL B, has numbers which may or may not match with numbers in COL A.
* If there is a match(between COL A and COL B), the row number at the match in COL A.
* If there is no match, the row of the closest number (in COL A) before to the number in COL B.
Example:
ROW COL A-----> COL B---------->
1 2184058252 2184276560
2 2184058280
3 2184058296
4 2184058312
5 2184058320
6 2184773640
7 2184774216
8 2184774272
9 2184774424
Now, i want to find 2184276560 in COLA, since it is not there, i want it to return the row number of the closest(previous) viz. row 5(2184058320).
Now, since this is a huge comparision set, im wondering what is the best way to do it. Im using VBA....
The problem is that there are two sets of up to thirty three (33) numbers each, numbers are NOT duplicated in each set. The goal is to make an excel file that combines all the numbers from both sets in combinations of five numbers each. There should be a maximum of three numbers chosen from each set, in order to limit the total numbers chosen.
I would like to see the five set listed on the left of the excel file and the intended numbers sets on the right of the same sheet. This would permit me to see at a glance if there are any missing numbers.
For instance, from the first set of numbers 1, 2, 3 are chosen and from the second set numbers 30, 31 are chosen. Therefore the table would show as:
1, 2, 3, 30, 31.
1, 2, 3, 30, 32.
1, 2, 3, 30, 33, ...
1, 2, 4, 30, 31
1, 2, 4, 30, 32, etc.
This would continue until all of the numbers are used with each number listed in a separate excel box but next to each other without the comma.
each numbered set of five numbers does not have any numbers repeated (i.e. the number three appears only once per set of five numbers).
If further clarification is needed please go to: [URL] ..... where n=33 and r=3.
Repetitions are NOT allowed and order is important. This will calculate a total of 32,736 permutations to choose from. NOTE: this calculates the first set of three numbers only and is used as an example of the intended output, however the second set of numbers is not included there which I would like to have included in the excel file.
I have an unusual situation which is best explained by way of an attached example.
In summary adding and subtracting four cells containing integer values outputs an unexpected non-integer value, which should in fact be zero.
The situation is best explained by way of example which I attach to this post : Excel Problem.xlsx
Basically, Cell E1 should be zero.
I can retrieve the weeknum of any particular date using =WEEKNUM(x) or =TRUNC(((x-DATE(YEAR(x),1,0))+6)/7)
But I'm looking for a UDF to output the tax year week number where the tax year always starts on 6th April. The std weeknum for 6th of april pretty much varies around week 13 but as it varies I don't ever get a strictly true result by deducting 13 from WEEKNUM().
I have a sumproduct formula, I am trying to divide the output by the number of members used in the calculation:
This is my data set
4/10/2014102.6214/10/201499.5144/8/2014106.144/8/2014105.4
4/9/2014102.3284/9/201499.3864/2/2014105.8734/7/2014103.6
4/8/2014102.0884/8/201498.7953/17/2014105.5864/3/2014103.95
my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?
=SUMPRODUCT(--(A1:G9=A11),(B1:H9))
Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?
View 3 Replies View RelatedI am trying to put all my parts with quantities on a seperate sheet called "Parts List" Every time you select a quanity for one of the parts, I want it to pop up on my parts list. This will make it easier to identify the exact parts I want and also the quantity I need. This will be much more convenient then scrolling down my parts list and trying to find the one's with quantities.
I think I need to use a vlookup or even a Macro but I don't know how to go about doing this.
I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...
View 2 Replies View Relateduse CTRL+ Navigation Arrow Key on keyboard i.e. if you wanna go to the last Non-Blank row in a column press CTRL+Down Arrow key if your range contains empty cells then use this shortcut SHIFT+CTRL+Arrow key
if you wanna Get the Value of cell in a column you can use this formula
=INDIRECT("A"&COUNTA(A:A))
This formula will work only if there is no Blank Cell in the range.
I have to create a 12 row 14 column table which will be printed out and put onto a (around) 20 by 20 poster board...so it has to be HUGEEEEEE. How do I do this, all the boxes inside the table I want to have control of meaning size and stuff. I will have to write in all boxes.
P.S: Yes. I know you guys prolly think im a idiot but this is my first time and my due date is appraoching fast, if anyone could help that would be great.
I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open?
There is 3 tabs and very little data. How can it be so huge?
I'm working out some vba script, that could filter up to 300,000 rows long list.
here is sample from that list:
1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409
2000999999027020300189640900000000000005740200070063
1000999999027FRESH PASTA FETTUCINE WITH SPI102002P N0809100000025000000015700000100+0000000+ 0000000000000000+0203001896454
20009999990270203001896454000000000000057010180265125740200070070
As you can see, the rows are starting with 1000 and 2000. And that would be the criteria.
I'm importing the loooooong list file onto sheet1, and i'd like to sort the list so it puts all the codes starting with 1000 onto sheet2 and rows starting with 2000 onto sheet 3.
With regular filtering mode it's impossible and keeps giving errors.
I know it's possible, but i can't figure it out anymore.
I was thinking about to make script that would copy one row from sheet to sheet and loop it. But my mind just crashes doing that.
Is the a more efficient way of sorting a huge sheet (One Column only)? Currently, I use the below code and sometimes it throws an error (due to insufficient memory/ cannot undo this step)
VB:
Sub sort1()
Rows("1:1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Clear
[Code] .....
When i'm working with huge data on excel say for exam 50000 records and about 25 to 30 columns, everytime i use any formaula and while saving it the file is not responding, what ever the formula like vlookup, countif, converting in values applying filters and removing filters and with that whatever the changes i do that is not seen next time when i open file.
View 9 Replies View RelatedI'm currently stuck with the following problem. I have a fairly large database with 10,000+ companies (in the rows) over 10 years (in the columns). I want to aggregate industry averages of a financial item in so-called SIC codes (there are approx. 1,000 different 4-digit SIC codes).
I've been using the daverage function, which works for one criterium (e.g. SIC = 0100) but not for the entire range of criteria. Does this mean I'll have to manually adjust every daverage function for every single SIC code? Or can this process be automated in some way?
Attached is a simplified version of my database file.
I'm sure I should be able to do this but my brain is out of gear today!
Can someone tell me how I can create a list of every 5 letter permutation using only letters (A-D).
I also need to do the same for 4 letter and 3 letter, again using A-D.
Would prefer to do it without VB but if this is the only way......
You will find a VBA Code for a PRIVATE example - displaying all 6 numbers combinations out of 10 (1-10).
My code displays them (in column "A") BUT What my question to you is - does someone have a General/Universal code to handle other kinds of combinations.
To my opinion, the usage of Nested (For-Next) Loops will not be the ideal solution - so maybe by using VBA Recursion ?
I must first thank the forums for the amazing help in making my life just a little easier, especially member "shg" for helping set up the VBA code to help me.
So I have a sheet to develop all possible combinations of units, the letters with their associated values above, as well as the maximum number of units possible. The user inputs a top value, this is followed by some subtraction leading to a working value with a tolerance level in which the sum of the combinations needs to remain within.
What my predicament is that while the VBA code in the attached book works to develop most combinations, when I input a small number such as 405, the combinations that arise are not complete. The results are shown in the attached workbook: show combinations of H and PP, but any human can read that there should also be 2 HN in the results, as the math add's up to within the tolerance level.
I am creating a class of permutations. The problem is that there are many ways to describe what a permutation does. for example.
Dim aPerm As New clsPermutation
Dim bPerm As New clsPermutation
aPerm.DoesTranspose(2,3) : Rem aPerm.Action({a,b,c}) = {a,c,b}
MsgBox (aPerm Is aPerm.Inverse)
I want the message box to return True. But since aPerm and aPerm.Inverse are different instances of clsPermutaion, it returns False. (.Inverse is a read-only property that returns a clsPermutation.). What I would like to do is have a collection, myPermutations. When I specify the action that aPerm does, I would like
1) if nothing in myPermutations does the same thing as aPerm, then add aPerm to myPermutations.
2) if something (xPerm) does the same thing as aPerm, then
Set aPerm=xPerm
so that "xPerm Is aPerm" returns True.
The problem I have is that in the class module,.....................
I have a huge data file. I would like to have excel automatically delete all rows with the value of 7 in column b. Can I do this without manually selecting all of the rows (I can sort by that value, but there are 120,000 rows).
View 6 Replies View RelatedI have a sheet that has 65000 rows and 3 columns, I need to load this sheet into a system that will only deal with 25000 rows at a time. How do you do a huge range select with Excel?
I need to select range a1:c24999 cut it out and paste it into another sheet. I have about 75 of these these sheets to do is there a way with a formula, a script or? to do these huge selects?
I am a Mac user, running MS office 2008 for Macintosh.
The project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.
The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.
I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.
Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).
The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....
I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.
The file is attached : Example.xlsx
We just upgraded to Office 2010 and now on many of my regular Excel files, when I open them in the new version, the last row that's visible in the window ends up with some massively huge row height such that you can't even page down past it.
I can resolve it easily by clicking on the row number and re-sizing it, but I'm getting tired of doing that.
What's wrong with Excel 2010?
Is there anyway to put a background on a worksheet, without the file size of the workbook becoming monstrously huge? I have a small jpg file of our company logo, and I think that it would be a nice touch for it to be the background to our spreadsheet. But when I use this 50k file as the background, the workbook suddenly grows to a size of 7MB.
View 4 Replies View RelatedI need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.
Example given below and also attached the file for your reference.
New Keyword – Singular Proper Case
Singular-PR
Singular-LC
Singular-UC
[Code]....
Simple permutation problem that will most likely requires nested loops. I need a list of every possible permutation of words in a row broken out by column. Order does matter and not every row has the same number of words in it. I have attached a sample worksheet with a sample table and then the needed output format.
View 5 Replies View RelatedIs there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.
Once I have a result set, I need to get rid of any groups which have a value contained in another group.
Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75
Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.
One possible combination would be:
Item1 Item2 Item100 170
etc...
I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.
For Ex:- If there are three Items.. A B C and forming a group of 2
then the the total no of combinations would be permut(3,1) = 6...
I need this to be shown as follows depending on the no of itmes and no of group no=2 in this case...
Permuations & Combinations are as follows:
AB
AC
BC
CA
CB
BA
