# Sequence Based On Column A Index

Sep 18, 2006

I am stuck on what should be a simple formula. I have a spreadsheet that is sorted by "target" number in column A, basically 1,2,3,4. I am trying to create a formula that will increase the number in column B by one based on column A. So when the value in column A changes column B will reset to 1. Also I need this sequence number to be preceded by zeros up to 0999. So 1 would be 0001 and 895 would be 0895.

## Macro For Putting Index Reference Numbers In Numerical Sequence?

Nov 12, 2012

I have a document that is a subject index for book that has terms that reference page numbers. The structure is for example: Employment, 587, 592, 553, 605, 233 The term is always following by comma and then space and listing of page numbers that subject reference applies. In many cases the page number references are out of sequence and I need them to be in numerical sequence. i.e. Employment, 233, 553, 587, 592, 605 There are many subject terms in index and need to check and fix sequence of page references for each.

## Index All Items In Column Based On Multiple Criteria?

May 15, 2014

I am trying to create a list of all instances where contents in A3 is found in C5:C12 and return the values in D5:D12 without any spaces. Right now I can do it in two steps but I'd like to clean it up and do it with only one formula.

## SUMIF Formula With Sum_Range Based On Column Index Number

Jun 22, 2009

Following is a summarized example of my data and what I am trying to accomplish.

[Column A] contains a list of account numbers. [Column B] contains current balances, [column C] contains balances from one month ago, [column D] contains balances from two months ago. Within the same spreadsheet I want the ability to type in the account number in one cell and then the column number in another cell. For example, If I type in the account number 1234 and the column number 3, I would get the balance from [column c]...if I typed in the column number 4, I would get the balance from [column D].

My first thought was to use a simple SUMIF formula that would compare the account number I type with the account numbers found in [column A]. The problem is getting a formula that can translate the number 3 to [column C] or the number 4 to [column D]. Note: the actual spreadsheet I am using extends out to column BI.

This is simuilar to the Column Index Number used in a VLOOKUP formula.

## Create Sequence - Put Order In Second Column Of Item That Is In First Column

Dec 28, 2011

I need to put the order in the second column of the item that is in the first column.

In the last line item Computer is recorded for the third time.

MATERIALSSEQUENCEComputer1Computer2Mouse1Mouse2Computer3

## Sequence Adding Based On Name?

Jul 1, 2014

I want the numbers to increase based on the labels from a list, such as the first john chosen will get a 1 on the first cell on sheet 2, if john is chosen again the 2nd john will get a 2 on the next cell on sheet 2 and so on for the other names.

I tried to use IF(COUNTIF(D:D,D2)=1,LARGE(IF(D:D=D2,E:E)+1),1) but it didn't work out.

## Look At Column A Number In Descending Sequence In Column B

Mar 20, 2008

there may even be an excel formula answer to it rather than a macro. Coloumn A has a series of numbers, I want column B to show those numbers in what would be there Descending Sequence.

Thus
Col A
100
97
14
87
32
108
21

So with this routine you would then see

COL ACOL B10029731478743251081216 The above I suspect is not too difficult, however if two (or more) numbers are the same I want the sequence to reflect that thus COL A COL B100297 314 797 332 5108 121 6 and no... the data - Col A - cannot be sorted, it must stay in that sequence.

If possible I would want this done automatically when any Col A value changes (any unused cells would be 0 by the way). Oh, there will in fact be 25 cells in column A for this.

## Sequence Number Based On Date?

Jul 25, 2006

I'm trying to set up an auto sequence number(col A) based on a date entry (col B) as in the example below. The sequence number should reset to 0001 each time the date in Col B changes.

Col A Col B
072306-0001 07/23/06
072306-0002 07/23/06
072306-0003 07/23/06
072406-0001 07/24/06

## Creating Number Sequence Based On For Loop

Aug 1, 2014

I am trying to create a number counter which runs up to a value which is determined by a COUNTIF Statement in the spreadsheet. I can get the number counter to run to the given value. However, due do formatting reasons I do not want the counter to start in cell A1 but A3 instead.

Essentially my code looks like this:
...
ActiveSheet.Cells(3, 1).Select
Dim i As Integer
For i = 1 To Range("A1")
Cells(i, 1) = i
Next i
...

## Filling Column With Number Sequence Whenever Value Changes

Apr 28, 2014

I am trying to fill a column with a number series starting with 1 whenever a value changes in the adjacent column. So for instance I am trying to do something like this :

87 1
87 2
87 3
88 1
88 2
88 3
88 4
89 1
89 2
90 1
91 2
92 1
92 2
92 3
92 4
92 5
92 6

## Searching For Sequence Of Numbers Down A Column?

Jun 6, 2014

I was wondering whether it was possible to search for a sequence of numbers.

For example, if this is down a column:

A1: 5
A2: 4
A3: 5
A4: 6
A5: 9

Is it possible to have a find function to search for the sequence of numbers "4 + 5" and consequently highlight cells A2 and A3?

## Create Ascending Sequence In Every Column

Apr 22, 2012

Creating a sequence in every column? It should look like this:

Original matrix (Top left cell is "A1"):
40 50 60
30 10 20
70 20 0

And I want to replace it with:
2 3 3
1 1 2
3 2 1

I need to create an ascending sequence of numbers in every column. First column contains: 40, 30, 70. That's why its ascending sequence is: 2, 1, 3.

Btw, I got N rows and M columns.

## Copy Sequence From Rows To Column?

Aug 11, 2012

I have a set of data in cells sequencially in every 3rd column. I would like to create a new rows where the data I have in columns are placed into rows in accordance with its catogory.

So all EUR are in one row sequencially over the colums and the same for USD & GBP. This way I can create a graph.

Jan 12EUR
USD
GBP
EUR
USD

[Code].....

## Automaticly Populate Dates In Sequence Based Off Of One Cell

Jul 3, 2009

How do i get cells to automaticly populate dates in sequence based off of one cell. I.E C1= 4/Jul/09 I want A2-A7 to populate the dates in sequence Jul 4 - Jul 11. Is it a formate issue?

## Macro To Select Cells Based On Number Sequence

Feb 21, 2014

I have a sheet with hundreds of rows of customer information; including a 7 digit customer number. I need a macro to delete all the rows where the customer number sequence start with 1 thru 8 (that would be the first digit of the customer numbers).

## Grouping Same Values By A Number Sequence For A Column

Mar 13, 2013

I am trying to group the same values in columns together and i was thinking about having a number count. What's the function that might be able to display this (display whats in Column B in the example below)?

Values
5000 1
5000 1
6000 2
7000 3
7000 3
8000 4
8000 4
8000 4
9000 8
10000 9
10000 9

## Determine If Column Of Values Contains Sequence Of Consecutive Matches

Oct 12, 2012

I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:

=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")

And copy down. I then test column B to see if there are any non-blank cells.

If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.

I can do this with a UDF, but the user has rejected this option.

## Index/Match: Match Column "G" To Column "L" And Count Column "H" Based On The Table To The Right

Apr 3, 2009

I am having a little trouble with tying an index/match formula that would probably require an "if" portion to the formula as well.

If you take a look at the attachment, I'm trying to match column "G" to column "L" and then use the appropriate price according to the specified piece count in column "H" based on the table to the right.

## Nested Index / Match Array - Return Value In Column C When Matching Column A And B But With Few More Criteria

Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A
B
C

1
Cat 1
January 1, 2014
John

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX(\$A\$1:\$C\$4,MATCH(1,(\$B\$1:\$B\$4>=B7)*(A\$1:\$A\$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

## Copy / Drag From Sequential Column To Non-Sequential Ones While Retaining Sequence

Jan 29, 2014

I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.

I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.

Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.

I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.

## Lookup Cells Based On Index Value

Sep 30, 2009

I have used this handy formula
=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A\$1:A2))

to create an index page that looks like this right now:

Index
35035-9
37032-14
37065-6
37079-4

Now what I would like to do is get cell B1 on each of these sheets to post in the next column without hardcoding the name of the sheet into the formula. So in the cell next to 35035-9, I would like the formula to look at worksheet name, and then go to that worksheet, and return cell B1. Same for all the other worksheet names.

There will be multiple additions of worksheet to this list over the course of the next year and I want to be able to index them easily.

## Index Items Based On Criteria?

Jul 9, 2014

I have a list of words in the column A, and then columns E:SA where are numbers. I need to set up a piece of code/VBA that will index(write) all the words from the column A that have a number 1 on the same row in the column E for example (I need to do this for each column - E:SA).

I know it's hard to understand, I'll give you an example:

Column E has in E16 number 1. So the program will index the text(value) of the cell A16
and so on ... for every column E:SA

I need to index the values in the 1748th cell(and higher) of each column (E:SA)

## Lookup Or Index Based On Three Cells

Mar 27, 2009

I have a spreadsheet that I am trying to make more functional. Basically, when I click on my dropdown (sheet1), it changes the value of the cell in B3 in sheet2. Also in sheet 2, I have column and row heading where I would like to return data via this lookup.

Rows = customers
Columns = product groups

So, you click on the drop down and it changes the month's data that you want to see (at least that's the theory). On sheet3, I have the sales data by customer, by product group and by month. If it weren't for the month, a simple index/match combination would work. How can I get that same functionality, to also look at the month?

## Copy Row Based On Color Index

Oct 20, 2006

to loop through each row in sheets("Layer Layout") and check if there are any red fonts in its cell..If there is, i need to copy the header ("A1") and the rows containing the red fonts to sheets("Report")..

## Sum Cells Based On Color Index

Nov 13, 2006

I am trying to use a function kindly listed by Dave, that allows for a SUM to be done on cells which have a certain color index. I have modified it very slightly, but unable to set the call to it without getting errors, perhaps someone can shed some light on what i am doing wrong. Dave's original code is

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function.......

## Add The Color Index Into A Column

Mar 19, 2007

i would like to add some sorting code by colors and i need to add the color index into a column in the same file + below formula = colorindexofcell(a1,false,true) i need to exclude 2 columns for deleting the report.To reason for that everyday we run the report and next time there are new entries and report needs to be run again

when i double click anywhere on report sheet it deletes the colorindex and formula column as well is there anyway to modify below code by excluding two rows such as "ag" and "ah" column,for deleting process

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call Delete_Report

Range(Range("a1"), Cells(Cells.Rows.Count, 1).End(xlUp)) _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
unquote

## Set A Variable To A Column Index With

Aug 2, 2009

I'm trying to set a variable to a column index with:

Set .timecol = (Asc(Column.Value))

"but I get an object reuired error"

## INDEX MATCH Based On Part Of Strings

Feb 7, 2014

Am trying to use index match to return value in column B based on matching 2 first chars on the left in column A.

Col A Col B
POS Majalt
POS Minalt
POS Instinctive
NB Viral
NB Sierra
NB Pierce
CLM Team1
CLM Team2
OS Tr1
OS Tr2
HR Tetra
HR Pentagon
IT penelope

Cell C1 : NBA MyFormula : {=INDEX(B:B,MATCH(LEFT(C1,2),"*"&\$A:\$A&"*",0))} <<== I need to find the match of NB only in range A:A to return the related values in Col B.

Expected Result:

NB Viral
NB Sierra
NB Pierce

Other than formula stated above, I've also tried with Isnumber(search), etc but all return errors.

## Colouring Cell Based On RGB And Index Numbers

Jan 28, 2014

As you can see from below I already have a spreadsheet which updates the fill colour of cells based on a certain criteria. This was initially set up in Excel 2003....I have now moved onto 2010 and want to use a certain colour based on it's RGB value. I tried as you can see from below, the part which is commented out (as it didn't work)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long, lngIndex As Long
For n = 6 To 842

[Code].....

## VLOOKUP / INDEX And MATCH For Second Value In Column?

May 1, 2014

If I have a column of data with a bunch of values (which can't be sorted & which is constantly changing so cant be broken into another column and then sorted) what formula do I need to output the second occurrence of a value in that column?