List All Possible Combinations Of Single Range In Two Separate Columns
Apr 25, 2013
I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:
Example Column A contains : 1,2,3 Column B contains : 7,8,11,15 Column C contains : 12,16,18,19 Column D contains : 17,30,31 Column E contains : 30,31,32,33,34 Column F contains : 37,39,40 The rules are:
In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.
1. In my first column(Let's call it "input"), there will be a variety of values from 1-10 which will appear in cells as 1,7,10 or 4,5,6,7 or any other similar variation. The quantity of numbers can vary and will always be comma delimited.
2. Following the first column are 10 additional columns. For simplicity, let's say they're labelled 1-10.
3. If 2,5,6,9 is present in the first cell, I want the number 1 in the columns labelled 2,5,6 and 9. If 1,4 is present in the first cell, I want the number 1 in the columns labelled 1 and 4.
The input will only be in the first cell so I am hoping to find a way to automate the placement of 1's in the appropriate column depending on the input in the first column("input").
As a bonus, I'd like all cells not present in the first column to appear as a "0".
I have a long list of 2 columns containing data as follows: country1 date1-1 country1 date1-2 country1 date1-3 country2 date2-1 country2 date2-2 country3 date3-1 country4 date4-1 country4 date4-2 country4 date4-3
which I would need to move to get one column per country with the top cell the country name and below each name all the related dates, e.g. country1 country2 country3 country4 date1-1 date2-1 date3-1 date4-1 etc. date2-1 date2-2 date3-1
I have searched and found almost similar questions and tried to apply it to this case, but not being versed in VBA it was a failure on my part
I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?
[Code] .....
For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?
I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.
Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".
I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this: DCM1005-1 DCM1005-10 DCM1005A MTR1005-1 MTR1005-10 MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas) A1, B1, C1, D1 DCM1005-1, DCM, 1005, 1 DCM1005-10, DCM, 1005, 10 DCM1005A, DCM, 1005, A MTR1005-1, MTR, 1005, 1 MTR1005-10, MTR, 1005, 10 MTR1005A, MTR, 1005, A
I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?
I know the COMBIN function, i want to list all combinations from a given set of cells (numbers), each item of the combination - in separate cell. Example:
1 2 3 4 5 a b 6 ....
after combin (x,3) i want to list me this: 123........but each number must be in separate cell (1-one cell, 2 -one cell, 3 -one cell)
I am trying to write VBA code that will list all possible combinations of data contained in a worksheet based on a few conditions. I have a list that varies in length. In the first column is a list of numbers denoting a particular course number. In the second column is a list of numbers denoting the specific class number for the course. As an example, I have a 6 courses. The first course has 1 class, the second course has 3 classes, the third has 11, the fourth has 3, the fifth has 7, and the sixth has 6. Thus, my columns look like this:
A B 1 1 2 1 2 2 2 3 3 1 3 2 3 3 3 4 3 5 ...
I need to list out all possible combinations of classes given that I must have all six courses. EXAMPLE (Course, Class):.......................
I have two columns of data in a worksheet, and I need a third column that shows all of the possible combinations of the data in those two columns. For example:
A A AA
B B AB
[Code] .....
I don't need permutations so, for example, the third column should only give the combination "AB" and exclude "BA".
I need to count how many times a code appears over a 6mth period only on a single day. The data would look something like:-
1/1 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 10/1 etc a b c a a b a a b b
If counting b the result would be 2 on the above 10 days as it appears on the 9/1 & 10/1 it would not be included. It is to be used to count the number of times somebody only appears on a single day over a period of time but not count them if they come back the next day.
I cant think of a way to do this using in built formulas (Countifs, sumproduct, etc) The example is basic and there could be up to 20 codes.
I have two columns of data, one with an ID # and the other one with a date. Some ID# + date combinations are repeated and I need to delete the duplicates. Here is a visual:
Have a monthly report process in which I need to count to combinations of values in 2 columns. Example:
A 1 A 1 A 2 A 3 B 2 B 1 B 3 B 3 B 4
Count results: A&1 = 2, A&2 = 1, etc. I'd like to get a summary table of the results. I see how to use sumproduct to count combinations, but that requires a sumproduct formula for every combination. The 2 colums are lengthy and the contents vary each month, so building the formulas each month will be a fair amount of work. Is there another way?
I have been sent an export from Outlook in a .csv file. The Body of the Email is all contained in one cell for each row (each email detail) and is in the following vertical format within the cell:
Column Header blank row Data blank row Column Header blank row Data blank row
....and so on. I've tried to do something using Transpose and Text to Columns, but I've not been able to successfully seperate out the data into a useful format in Excel. The data are responses to a website and contain Account/Contact information which I hope to be able to import into a CRM system as new Leads. way of extracting this information, so that I can get each row into a format that gives me the column header with the related data below, as you'd normally expect to see in Excel?
formula to separate information in a single cell into multiple columns. I have hundreds of rows of data, so a formula to quickly separate the information into individual columns would be good. Below are a few lines of data.
ALMADEN STATION* 900 227 168 182 176 OAKRIDGE STATION* 900 276 332 362 349 OHLONE‐CHYNOWETH STATION* 900 469 480 522 504 ALUM ROCK STATION 901 947 925 1007 972 BAYPOINTE STATION 901 181 196 213 205 BONAVENTURA STATION 901 137 140 152 147 CAPITOL STATION 901 650 680 741 715 CONVENTION CENTER STATION 901 788 814 886 856 CURTNER STATION 901 569 571 621 600 ORCHARD STATION 901 87 94 91
I need to be able to create a list of all combinations (where position doesn't matter, therefore its not a permutation) of a list of numbers with repetition enabled. I need to provide anywhere up to at least 10-15 numbers if possible and they are integers normally between 1 and 72. Example: (for numbers 1, 2, 3)
How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.
An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.
I would like results for my request of 50 matches to be displayed as follows.
HHH HHD HHA HDH HDD HDA
[Code] ...........
Where: H=HOME D=DRAW A=AWAY
Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.
What I am trying to achieve using the example below:
ID Subject 1 Subject 2 Subject 3 1 Italian French German 2 Italian Art Physics 3 German French Italian 4 French Italian German
the result:
Italian French German 3 Italian Art Physics 1
As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.
I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.
Is it possible to have a single userform put data into two separate sheets within the same workbook.
I have a userform set up for entering client data into a quote register,with no problems ( sheet 1)
A1, A2, A3, A4, A5, A6, A7, A8, A9
What I would like is for the data to also go to sheet 2, where I have a form set up to print off for our technicians to take on site.
The problem will be that I would like the data A1, A2, A3, A4, A5, A6, A7, A8, A9 not to store on this sheet after it is printed, so the next set on entries , can be printed etc.etc
I have 7 cells containing strings but not numbers on a row.
Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.
For example, I got 7 cells like this.
A B C D E F G (each letter in ONE cell)
And I want to list out all the combinations like this:
ABC DEFG ABD CEFG ABE CDFG ABF CDEG
etc.
For more details, please refer to the attached sample (an .xls file being zipped).
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.