# Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

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
ADVERTISEMENT
Apr 18, 2014

how can I retrieve a unique list coming from a combination of 2 columns. Using VBA would be much better.

e.g.

Col1Col2

RED 1

RED 2

BLUE 1

BLUE 1

RED 1

BLUE 3

BLUE 2

BLUE 1

RED 1

Result will be

RED 1

RED 2

BLUE 1

BLUE 3

BLUE 2

View 3 Replies
View Related
Mar 23, 2014

I need to find the which two columns that give the greatest combination.

Row 2 shows some possible combinations, a total of combinations is 21.

Attached File : test.151515.xlsx

View 12 Replies
View Related
Jul 19, 2009

Is there an easy way to concatenate two columns and provide every possible combination?

E.g.,

A B

1 A1 B1

2 A2 B2

3 A3

The function should result in

C

1 A1B1

2 A1B2

3 A2B1

4 A2B2

5 A3B1

6 A3B2

View 3 Replies
View Related
Nov 20, 2013

I have a (phylogenetic) matrix with a species in each row, and morphological character in each column, scored as 0, 1, 2, 3 or 4 for each different species, so something like the following but much bigger:

Character 1 Character 2 Character 3

Species A 1 0 1

Species B 3 2 2

Species C 1 2 0

I want to identify the minimum combination of different columns (i.e. their values, but also which columns) that make a particular row unique.

View 14 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
Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO

406AMZN

451AMRC

404AMAD

605ANCC

405ADRC

The result would be:

302 ALTO

406 AMZN

451 AMRC

404 AMAD

605 ANCC

405 ADRC

View 6 Replies
View Related
Nov 30, 2007

i have one work sheet. Column A contains 800 names bolumn B contains 15,000 names.

I need to compare column A to column B then if the name exists displat the names in a seperate column.

View 2 Replies
View Related
Jan 27, 2009

I have two columns of card numbers (16 digits) in text format, as column A and column B. In column C, I'm trying to figure out how to compare column A and B for matches, and display the match in column C, if there is a hit.

View 4 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
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
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