# 5 Column Combination?

Nov 13, 2013
I have data in a 5x5 area and I would like a VB Script or Function that can give every possible combination of one number from each column added together.

i.e.

500 400 300 200 100 1500

400 300 200 100 500 1900

300 200 100 500 400 1800

200 100 500 400 300 etc...

100 500 400 300 200

View 5 Replies
ADVERTISEMENT
Jun 19, 2007

The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it - sorry!

Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file.

The number of columns and number of rows in each column will be changed regularly ...

View 9 Replies
View Related
May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",

IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

View 6 Replies
View Related
Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.

View 9 Replies
View Related
Mar 20, 2009

I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.

View 4 Replies
View Related
Feb 28, 2014

I need to consolidate several amount and make sure which of them falls into a sum of an amount.

I wonder if excel can find all the calculation in range A, and highlight those that equal to the cell D10 (example as per my attachment).

Calculation.xlsx

View 5 Replies
View Related
Jan 16, 2010

how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.

e.g. Alpha,Bravo,Charlie,Delta,End

(AlphaBravo=BravoAlpha)

Choosing 2 = 10

Choosing 3 = 10

Choosing 4 = 5

Choosing 5 = 1

View 11 Replies
View Related
Jun 22, 2007

I have a list of numbers from 1-7 would look like this each number in a seperate cell.

3 5

1 2 3 5 6 7

2 4 5 6 7

6 7

4 6

6

I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.

The combos that would work:

row 1 use 3 ----------- row 1 use 5

row 2 use 1 ----------- row 2 use 1

row 3 use 2 ----------- row 3 use 2

row 4 use 7 ----------- row 4 use 7

row 5 use 4 ----------- row 5 use 4

row 6 use 6 ---------- row 6 use 6

5 would complete ---------- 3 would complete

Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.

View 9 Replies
View Related
Nov 15, 2008

on combination of numbers

on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).

Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2

I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.

View 9 Replies
View Related
Feb 10, 2006

I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:

1, 2, 3

1, 3, 2

2, 1, 3

2, 3, 1

3, 2, 1

3, 1, 2

The functions in Excel available only give the total number, but I want to see these combinations!

View 6 Replies
View Related
Jan 23, 2014

I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?

View 2 Replies
View Related
Mar 10, 2014

I have set of people where by based on Level & designation want to find the percentile on their CTC.

Attached is the sample file. Percentile.xlsx

View 6 Replies
View Related
Apr 6, 2009

Can Offset be combined in an Index Match Match formula as per the attached sample?

View 4 Replies
View Related
May 11, 2009

I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.

View 6 Replies
View Related
Jul 25, 2009

Ive attached my example but explaination of what i am trying to do is below:

In sheet 1 i have products listed with a product ref

In sheet 2 i have a list of features by product ref.

I want to be able to put each feature next to the relevant product in sheet 1, some products may have 3 features, others may have 5 or more.

View 4 Replies
View Related
Oct 9, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.

View 2 Replies
View Related
Aug 18, 2009

I would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.

Function FirstCheck(Count1, Count2)

Select Case FirstCheck

Case Count1 = "", Count2 = ""

FirstCheck = ""

Case Else

FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2))

End Select

End Function

View 3 Replies
View Related
Jan 17, 2010

I have created a program where there is a spreadsheet containing all of the items in a loan tools store, which are issued out to people. I have created a "Search/Find" function within the "Issued Items" sheet. Within this search function, you are able to search all or any combination of the following criteria: Serial; Description; Name; Location. For example: If you just type data in the "Serial" field, it will just search that column and select the cells which contain that value.

The problem I am having is when searching multiple criteria, each and every cell in the columns which are searched is selected. Whereas, I would like only the cells which match all of the criteria to be selected.

For example: If I was to type "1" into the "Serial" field, "2" into the "Description" field, "Liam" into the "Name" field, and "Workshop" into the location field:

Current: Serial column is searched and all cells with "1" in are selected. Description column is searched and all cells with "2" in are selected. Name column is searched and all cells with "Liam" in are selected. Location column is searched and all cells with "Workshop" in are selected.

What I Would Like: Program to search each of the specified columns and only select data which meets the searched criteria. For example: Rows 20 & 28 to be selected as they both contain, Serial "1", Description "2", Name "Liam", Location "Workshop".

Note: The sheet will have password protection.

View 10 Replies
View Related
Oct 17, 2007

i've attached a worksheet yet removed my attempt at formulas as it would have made most of you cry... what i'd like to do is select an item from a dropdown list (B1) (that i've built and it works, phew) and display the summary data (B3:B10) from the column or sum of columns in array (A12:F21) as explained in the relationship matrix.

I've tried ifsum and dsum (copied in each cell B3 to B10 of course) yet it doesn't extract items from the array with the drop-down selection. nor does it add columns together.

View 14 Replies
View Related
Apr 8, 2009

I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.

View 4 Replies
View Related
Dec 24, 2012

My Columns are as follows:

A1-Criminal Name, B1-Crime, C1-Age, D1-Ratings, E1-Punishment

In the punishment column one will get 22 years life imprisonment if he fulfills the following conditions.

1. He must have done Rape OR Murder

AND

2. His Age should be >30

AND

3. His Ratings should be>8

It should throw 22 years in the Punishment column only if the above conditions are met otherwise it should be Nil.

More Info on this:

1. Crime column includes Murder, Rape, Robbery, Assault, Kidnap etc

2. Age column ranging from 22-75 years.

3. Ratings column ranging from 1-10 points

4. There are 3400 records we have in the list

How to write an IF AND OR combination formula for this ?

View 9 Replies
View Related
Jul 23, 2013

I have to write a formula which states the following:

if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",

if of these cells AA1,AB1 &AC1 contains a number then "OK",

if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"

I believe an If and AND combination could work but its not working for me.

View 8 Replies
View Related
Mar 14, 2014

1

0

0

4

6

=largeodd(a1:e1)

=largeeven(a1:e1)

write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.

[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]

View 9 Replies
View Related
Jan 26, 2009

I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9

it would look like this

4 0 3 9 0 4 3 3 2

9 2 5 6 5 6 9 6 6

8 9 9 3 1 0 2 9 8

1 6 7 5 9 9 8 2 5

2 7 2 2 2 8 5 1 3

0 1 4 7 4 7 6 0 9

3 5 6 4 3 3 0 4 4

7 8 8 1 8 5 7 8 7

5 4 1 8 6 1 1 5 1

6 3 0 0 7 2 4 7 0

each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.

View 9 Replies
View Related
Apr 26, 2009

The numbers are file attributes, as you know these are

Normal = 0

Read Only = 1

Hidden = 2

System = 4

Volume = 8

Directory = 16

Archive = 32

These numbers are cumulative, so if a file has an attribute of 5 it is Read Only and System (1 + 4), it can't be anything else. Or if it has an attribute of 6 it can only be Hidden and System (2 + 4).

What I need is a spreadsheet that calculates every possible combination of these numbers, so I can check my Select Case statement has covered all possible combinations.

If it was just a one off project I could just work it out "by hand", but I have realised that there are several other projects I have that this would be useful in.

e.g. I am doing a skills matrix at work. If I give each skill a number, then give each employee a cumulative total number then I can have a spreadsheet that shows their skills. For each employee number there will only be one possible combination of skill that add up to that number.

My employer often adds new skills, so each time this happens I will have to check every combination is covered.

So I really need a spreadsheet solution, something I can input a group of numbers and it will show me a list of every possible combination of those numbers. The number of numbers in the group will vary, so a solution that only works for a group of (say) 6 numbers won't work. It has to work on a variable group of numbers.

View 9 Replies
View Related
May 18, 2009

How do I let a macro execute a certain key strok combination, such as Alt, D, G, H?

(Didn't really know what to search for...)

View 9 Replies
View Related
Aug 19, 2009

I am trying to do a lookup from the price list based on the below data file, my problem is I need the price that falls within the qty range from the price list, the price list is not consistent and changes with every item. I would like one formula that is sophisticated enough to work no matter where the qty range lies.

Item #_____Qty Ordered________Price List

20800______1,500

20800______4,000

20800______6,500

20800______8,000

20800______9,050

20800______11,000

20800______14,000

Price List

Item #____Order Qty________List Price

20800_____2,000___________$3.00

20800_____4,000___________$2.95

20800_____6,000___________$2.90

20800_____8,000___________$2.85

20800_____10,000__________$2.80

20800_____12,000__________$2.75

20800_____14,000__________$2.70

View 16 Replies
View Related
Dec 22, 2009

formula below... currently is giving me a:#N/A

=IF(EG55="FCST",VLOOKUP(--EE55,Control!$R$8:$R$20,6,0)*(SUMPRODUCT(--($HH$11:$HH$62=EE55),--($HJ$11:$HJ$62=EJ55),--($HL$11:$HL$62))),NA())

If I remove the vlookup part the formula return the correct results, but I want the resulting number be multiplied by a percentage (that what's the vlookup is trying to accomplish).

View 9 Replies
View Related
Aug 22, 2006

Can Excel work out every possible combination of 3 numbers from a numbered set 1-24.

I need every combination of 3 numbers.

View 3 Replies
View Related
Sep 20, 2006

I have a form (userform) which has a combo box and this combo box functions as a selector. I mean, when the person chooses one of the items (sheet1, sheet2, sheet3, etc.) inside the combo box, it views the sheet he/she picked. How will I do that thing? What code/s will I write in my module?

View 4 Replies
View Related