# Lexicographic Index Number To Combination?

May 3, 2012

I want to convert a Lexicographic Index Number, better known as Combination Sequence Number (CSN) to a combination using an EXCEL formula.C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.

I have a lookup table in cell M1:R56 which holds the correct data, that I am pretty sure about.

In cell G1 I have the Lexicographic Index Number I want converted to a combination.
In cell J2 I have the value 39 (n).
In cell I2 I have the value 5 (k).
In cell K2 I have the formula =COMBIN(\$J\$2,\$I2).

In cell A1 (the first number in the combination) I have the formula:

=IF(G1=0,"",\$J\$2-VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,6))

In cell B1 (the second number in the combination) I have the formula:

=IF(G1=0,"",\$J\$2-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,5))

In cell C1 (the third number in the combination) I have the formula:

=IF(G1=0,"",\$J\$2-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1),\$O\$1:\$R\$56,4))

In cell D1 (the fourth number in the combination) I have the formula:

=IF(G1=0,"",\$J\$2-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1)-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1),\$O\$1:\$R\$56,1),\$P\$1:\$R\$56,3))

In cell E1 (the fifth number in the combination) I have the formula:

=IF(G1=0,"",\$J\$2-(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1)-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1),\$O\$1:\$R\$56,1)-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1)-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1))-VLOOKUP(\$K\$2-G1-(VLOOKUP(\$K\$2-G1,\$M\$1:\$R\$56,1)),\$N\$1:\$R\$56,1),\$O\$1:\$R\$56,1),\$P\$1:\$R\$56,1)))

This works for the vast majority, but gives an ERROR when numbers 35,36,37,38,39 are in the combination, but funny enough NOT all the time.Lexicographic Index Number 575757 which is the maximum combination gives ALL ERRORS.

ADVERTISEMENT

## Sum Index And Match Combination

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.

## Index / Match / Rank Combination

Jan 7, 2009

I have several months worth of data, lets say January to December in cells A1:L1. In the rows underneath I have data, however, it is several "sets" of data. For example, I have data in A1:L10 (the first batch) and another batch in A21:L30 and so on. So you can see that there are rows between each batch of data (this must remain thus).

What I would like to do is set up a formula that reads along the the dates at the top, then reads down to the batches of information only, and then ranks them.

Ultimately, what I want is to tap in say "September" and then in a table I want to have the top 10 ranked in order from the batches of information from the September column, and this will change according to the month I tap in etc.

I think it may be some combination of Index / Match & Rank, but I am struggling with the Rank formula applied to non-consecutive ranges!

## Combination Of AVERAGE With VLOOKUP And MATCH / INDEX?

Aug 14, 2014

What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.

(some examples from the file)
NR2 NR DATE
100707987121951
100702347121960
100707750121960
100707721121960
100702422121960

[code]....

So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.

I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)

## Vlookup, Index, Match, Offset, What Combination Should I Use?

Sep 22, 2009

I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.

The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.

## Decrease Letter And Number Combination Code By Number

Dec 21, 2012

I have letter and number combination code in two collumns and they differ for 10.000 numbers:

BAM98314
BAM88314
BAM90000
BAM80000

As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.

## Largest Odd And Even Number Combination?

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]

## 2 Digit Number Combination

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.

## Every Possible Number Combination/Permutation

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.

## Sorting Rows With A Combination Of Text And Number?

Aug 3, 2013

I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."

e.g.:

My field:
1
2
N.A
4
5
N.A
9

I want to sort the cells by descending value. But the result appears as:

My field:
N.A
N.A
9
5
4
2
1

But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."

EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."

## Number Of Unique Customers For Every Possible Product Combination

Jan 15, 2010

I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.

Here's what I mean (though I can't figure out how to create neat columns in this post):

[C1] [C2]

Prod1 CharlieCo
Prod3 AlphaCorp
Prod2 BetaInc
Prod3 BetaInc
Prod1 AlphaCopr......................

## Find Number Combination In A Group Of Cells

Oct 28, 2011

I'm looking for another excel game changer (for the work I do anyway).

I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?

Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal \$257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.

## Find And Replace A Specific Letter/number Combination

Jul 9, 2008

I have a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).

The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.

Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.

## Pivot Tables - Finding Number Of Individuals With Particular Filter Combination?

Jan 30, 2013

I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".

So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?

Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.

The purpose of this data is for reference to question #2, so that i can utilize some percentages.

## 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.

## Get Sheet Index Number

Jan 2, 2007

Is there a straightforward way to find a sheetnumber based on the name of that sheet? Eg if I have 10 sheets and the fifth sheet is called "number5", I want to get a 5 based on an expression that uses the sheetname "number5".

## Row Index Number Increasing Automatically

Mar 29, 2007

I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?

I am attaching a SAMPLE of data. The actual worksheet is much more larger.

Hlookup.xls

## Do Userform Controls Have An Index Number

Dec 18, 2009

The form that I have created has a number of controls that are created at runtime. If a user triggers the event that originally created the controls, I want to be able to delete all of the controls before recreating them.

Is there a loop that can delete all of the newly created controls? I have 9 controls on the form that need to stay put but everything after those 9 controls I want to delete. Is there something like:

## Automatically Number An Index Column

Oct 24, 2005

I am working with a group of individuals that will be passing around an
excel spreadsheet to one another, and wanted to come up with a way to have
the 1st column act as an index, with the key component requiring that the
index column would automatically re-number itself, if someone entered a new
row.

A typical value in the first column looks like this: 8-5-012-005

Which in our case means that there are 4 series of number sets, separated by
dashes. So the first set is 8, the second 5, the third 012, and the fourth
005. The available range for the sets are 7 or 8 for the first, 5 through 9
for the second, and 0 through 130, and 0 through 200, respectively.

So the user can pick any of these ranges for when they decide to create a
new record (row).

Here is the way the spreadsheet columns currently look (always sorted by
Tract_ID):

Row-1 Tract_ID Parcel_ID
Row-2 7-5-065-105 01245787
Row-3 7-5-112-005 01245787
Row-4 8-5-012-005 01245787
Row-5 8-6-030-125 01245787

Now, here is the way I'd like to have the spreadsheet columns look with the
Index_No (can be either Numeric or Text - depending on your recomendations).
The sort order is based on 1st, the index number, then 2nd the Tract_ID:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-112-005 0126A560
Row-4 3 8-5-012-005 01005147
Row-5 4 8-6-030-125 01000541

Then, let's say the user wants to enter a new value like say, 7-5-105-021.
That value would need to go between Row-1 and Row-2, which, if they just
inserted the value in the row of their choice, would screw up the indexing.

What I need is a way to ALWAYS create an index (automatically), no matter
where they decide to put the value in the spreadsheet, AND it would update
all of the other Indexes as well (very important requirement).

So the end result would be this:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-105-021 00547419
Row-4 3 7-5-112-005 5126A560
Row-5 5 8-5-012-005 00005147
Row-6 5 8-6-030-125 00001541

## Index/Match & Sum: Returns The First Number

Aug 7, 2009

I have the following formula that returns a number, however it returns the first number it comes to when the criteria is met,

## Find Index Number Is The Color Of Red

Apr 17, 2009

I am editing code that changes a cell's forecolor and I can't seem to find the number for red. What number is red

## Match Index: The Value Yes To Return The Row Number

Jun 22, 2009

in one column I am looking up the value Yes to return the row number.

=MATCH("Yes",C:C,0) in this case it returns a 2

I want to use this row number in a sum...

i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.

## Change The Index Number When Match

Jan 16, 2010

I have to do a vlookup in 12 sheets (named ranges)

I use the formula '=vlookup(a1;choose(1;range1;range2; etc);2;0)

In this case I have to change the 'choose index_num' every time to match.

Is it possible to do the lookup without changing the 'choose index_num'?

## Select Column By Index Number

Nov 15, 2006

How can you select a column by it's index? For example, if I do the following, I get an error.

Dim Counter As Integer

Counter = 157

Columns(, Counter).Select

## Row Index Number Increasing Automatically

Mar 29, 2007

I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?

I am attaching a SAMPLE of data. The actual worksheet is much more larger.

## Find Number In Array That Is Less Than INDEX MATCH?

Jun 25, 2014

Cell P14 = 4.7

My array is H42:N72, in H42:H72 I have numbers 15,14,13....0,-1,-2,-3, etc...

In N42:N72 there is a corresponding \$ amount that I would like to return based on finding the value which is greater than 4.7 (so, looking to match the \$\$ amount to the number 5 in column H42:H72)

I've tried using INDEX MATCH but can't figure this out.

## Find Column Number And Then Use Index Function

May 28, 2008

I have a database with over 100 products listed across the first row.
Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.
For Example

A B C D E etc.
Products --> X Y Z AA
Proj 1 2 3
Proj 2 1 4 5
Proj 3 2 4
etc.

I want to be able to create a report for any given product.
The report could look like,
Product Z
Proj 1 3
Proj 3 2

So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.

I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.

## Vlookup Or Similar With Variable Col Index Number Value

Dec 21, 2007

i am looking to create a vlookup but with the ability to easily change the column number index so that i can use different columns.

As an example. In a worksheet i have a table with the names of cars in column A starting row 3. Column B to m Row 1 is headed Jan to dec, row 2 same columns is a country name eg UK. Column N to Y row 1 is Jan to Dec again and row 2 of these columns is a diff country say Germany. This repeats for a few more countries. The data within Row 4 for these columns i.e per car is all prices. The table therefore shows the prices of cars per country per month.

I then have a seperate worksheet for each country where the cars are again listed in column A and Jan to Dec is in column b to M but the data is hard coded being the number of cars. I would like to use column N to link to 1 of these months hard coded counts dependant on what month i decide to forecast on. The easy way being that if i wanted to use Jan count number i would link the count for that car type to =b4 etc. Is there an easy way to allow me to change the link should i decide i want Feb ?

The second question is within each countries worksheet i want to bring into column p the countries related car price for a month i select. It may be that the count number differs from the price i select.

## Refer To Cell To Update Row Number In Index Formula?

Dec 8, 2012

I have 2 excel files A & B. In cell A1 of file B, I use an index formula to refer to a row in file A. "INDEX('[A.xlsb]A'!\$10:\$10,1,2)" is the formula used (referring row 10 in file A).

If I update the row number in another cell of file B, is it possible for this formula to refer to that cell to get the row number from file A?

## Offset And Index Function With Dynamic Number Of Column

May 27, 2013

i'm running out an option, i must use offset or index function but there are problem found: 1.Offset function cannot use a dynamic height or number of columns and the result are #NAME?, here is the code

VB:
Private Sub CommandButton1_Click()Dim k As Variant
Dim b As Variant
Range(Me.RefEdit1).Name = "item"

[Code]......

Copyrights 2005-15 www.BigResource.com, All rights reserved