Increment a number based on four adjoing cells

I am using an xlsx spreadsheet for indexing metadata for file folders. Based 
on the metadata selected from dropdowns in columns H, I & J, the spreadsheet 
builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined 
number; nnnnn= sequential number).

I'm using the following formulas:
Col B    =VLOOKUP(H577,FUNCTIONLU,2)
Col C    =VLOOKUP(I577,CATEGORYLU,2)
Col D    =VLOOKUP(J577,TYPELU,2)
Col E    
=IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$D576,0))+COUNTIF($D$2:$D576,D576)-1)))

B, C & D work great. However, Col E should +1 based on all four columns, but 
it's only incrementing based on Col D.

Here's some sample data of what should happen:

B    C    D    E
------------------------------------------------
LE  EN  01  00001
LE  EN  01  00002
LE  CO  02  00001
LE  EN  01  00003
CO IA  01   00001  <=== My formula is making this 00004
LE  CO  02  00002
CO IA  01   00002

Do I have to do some sort of concatenation for it to look at all the fields?

0
Utf
5/4/2010 12:34:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
954 Views

Similar Articles

[PageSpeed] 55

Hi Nolene

Try the below in cell E2 and copy down as required..  

=SUMPRODUCT(($B$2:B2=B2)*(B$2:$C2=C2)*($D$2:D2=D2))


'If you want this as text then try the below
=TEXT(SUMPRODUCT(($B$2:B2=B2)*(B$2:$C2=C2)*($D$2:D2=D2)),"00000")

-- 
Jacob (MVP - Excel)


"Nolene" wrote:

> I am using an xlsx spreadsheet for indexing metadata for file folders. Based 
> on the metadata selected from dropdowns in columns H, I & J, the spreadsheet 
> builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined 
> number; nnnnn= sequential number).
> 
> I'm using the following formulas:
> Col B    =VLOOKUP(H577,FUNCTIONLU,2)
> Col C    =VLOOKUP(I577,CATEGORYLU,2)
> Col D    =VLOOKUP(J577,TYPELU,2)
> Col E    
> =IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$D576,0))+COUNTIF($D$2:$D576,D576)-1)))
> 
> B, C & D work great. However, Col E should +1 based on all four columns, but 
> it's only incrementing based on Col D.
> 
> Here's some sample data of what should happen:
> 
> B    C    D    E
> ------------------------------------------------
> LE  EN  01  00001
> LE  EN  01  00002
> LE  CO  02  00001
> LE  EN  01  00003
> CO IA  01   00001  <=== My formula is making this 00004
> LE  CO  02  00002
> CO IA  01   00002
> 
> Do I have to do some sort of concatenation for it to look at all the fields?
> 
0
Utf
5/4/2010 4:21:01 AM
Thanks, this works fine. However, it takes a really long time to save the 
spreadsheet now. If there is a way to speed that up great, otherwise -- since 
this is just a stopgap solution -- we'll just have to wait to get a proper 
program to do this.

"Jacob Skaria" wrote:

> Hi Nolene
> 
> Try the below in cell E2 and copy down as required..  
> 
> =SUMPRODUCT(($B$2:B2=B2)*(B$2:$C2=C2)*($D$2:D2=D2))
> 
> 
> 'If you want this as text then try the below
> =TEXT(SUMPRODUCT(($B$2:B2=B2)*(B$2:$C2=C2)*($D$2:D2=D2)),"00000")
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Nolene" wrote:
> 
> > I am using an xlsx spreadsheet for indexing metadata for file folders. Based 
> > on the metadata selected from dropdowns in columns H, I & J, the spreadsheet 
> > builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined 
> > number; nnnnn= sequential number).
> > 
> > I'm using the following formulas:
> > Col B    =VLOOKUP(H577,FUNCTIONLU,2)
> > Col C    =VLOOKUP(I577,CATEGORYLU,2)
> > Col D    =VLOOKUP(J577,TYPELU,2)
> > Col E    
> > =IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$D576,0))+COUNTIF($D$2:$D576,D576)-1)))
> > 
> > B, C & D work great. However, Col E should +1 based on all four columns, but 
> > it's only incrementing based on Col D.
> > 
> > Here's some sample data of what should happen:
> > 
> > B    C    D    E
> > ------------------------------------------------
> > LE  EN  01  00001
> > LE  EN  01  00002
> > LE  CO  02  00001
> > LE  EN  01  00003
> > CO IA  01   00001  <=== My formula is making this 00004
> > LE  CO  02  00002
> > CO IA  01   00002
> > 
> > Do I have to do some sort of concatenation for it to look at all the fields?
> > 
0
Utf
5/5/2010 10:25:01 PM
Reply:

Similar Artilces:

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

how do i see the numbers of pages at the bottom of my work sheet?
i used to see the page numbers at the bottom of the screen when i worked on a multi-page document, so i could easily move from page to page. now i have to go to edit- then go to page- which is very inconvenient. what happened maggiekins wrote: > i used to see the page numbers at the bottom of the screen when i > worked on a multi-page document, so i could easily move from page to > page. now i have to go to edit- then go to page- which is very > inconvenient. what happened ============================= View / Status Bar. -- ******John Inzer******** **MS Picture It! MVP** ...

Cell Contains Data.
How can I tell a cell that IF a cell contains data (numbers, text, whatever) do x or y. How can I identify data?? I don't want to tell the function to look for a specific number or text, just data, just something in the cell... =IF(A1<>"","Data","No Data") "GEM" <GEM@discussions.microsoft.com> wrote in message news:07916799-F312-4E89-A926-59356B74ED89@microsoft.com... > How can I tell a cell that IF a cell contains data (numbers, text, > whatever) > do x or y. How can I identify data?? I don't want to tell the function ...

Active cell highlight
Hi, I would like to know if there was a way to have an active cell always in yellow or any color. Example: If the active cell is moved around, the active cell will be always in yellow. Thank you in advance maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm E wrote: > > Hi, > > I would like to know if there was a way to have an active cell always in > yellow or any color. > > Example: If the active cell is moved around, the active cell will be always > in yellow. > > Thank you in advance -- Dave Pet...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

Validation Rule for IBAN Account number in MS Access Form field
Hi, In my table (and the form field linked to it) I have a field for the IBAN account number, which must be 2 Upper case characters A to Z, followed by 18 manadatory digits, and up to 28 total. So in my input mask I have this: >LL00\-0000\-0000\-0000\-0000\-9999\-9999;; and it works fine. But when the user enters less than 20 characters total (including the two letters at the start and at least 18 digits) Access gives a very unfriendly ''The value you entered isn't appropriate for the input mask '>LL00\-0000\-0000\-0000\-0000\-9999\-9999;;' specified for thi...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

convert text to predefined number in single column
Hi all, Greeting to all of you out there! I am a newbie to this forum and now I have a question here. I have a worksheet which contains thousands of records. Within a particular column (column "N") there are single character in the cells throughout the records (eg. "M", "S"). My question is how can I convert from "M" to "1" and "S" to "2" using formula? Kindly advise. Thanks and best regards. Eric --- Message posted from http://www.ExcelForum.com/ Use a help column, =LOOKUP(N1,{"M","S"},{1,2}...

Setting Hyperlink base
Is it possible to set the hyperlink base in VBA? How would I go about it? Thanks, John ...

How to exclude cells from a datasource?
Hi, I have an XY chart in excel97 that uses a range of cells for the data source. Every now and then I would like to exclude a XYnode from the chart. Without having to reselt the datasource. Is there a value one can enter in the cell that exludes it from the graph (compare: putting text in a cell excludes it from the SUM() function). For example point X Y a 1 1 b 2 2 c 3 3 Gives a graph with two lines connecting the three points While point X Y a 1 1 b ? ? c 3 3 Should give a graph with one line connecting point a and c. Point b is left out. Or is the...

What must I do to get gridlines printed for both filled and empty cells?
Hi, What must I do to get gridlines printed for both filled and empty cells? I'm a teacher, and I'm trying to replicate and print, in Excel, an attendance book format, with a series of tiny squares next to each student's name. The printed form of this would allow me to register if a student were present or absent on a particular day. Hubert -- Hubert Earl, vendor of fine Jamaican art, coffee, etc. on eBay: http://www.stores.ebay.com/id=12295024&ssPageName=L2?refid=store; Fine Jablum Blue Mountain Coffee sales page: http://stores.ebay.com/Jamaican-Coffee-Art-and-More-Store_Jab...

Chart changing based on change in data source (number of rows/colu
As I see, the data range for charting is normally fixed. I have to produce chart based on chaning rows and/or columns in data source table. Has anybody got some suggestion for me? Bal Ram Bhui - For Excel charts in general, I suggest Jon Peltier's web site: http://www.peltiertech.com/ For dynamic charts, I suggest: http://www.peltiertech.com/Excel/Charts/Dynamics.html - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Bal Ram Bhui, Jakarta" <Bal Ram Bhui, Jakarta@discussions.microsoft.com> wrote in message news:DD0B5564-26B0-4...

Count the number of worksheets in a workbook
Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! with VBA Function WSNum() WSNum = Activeworkbook.Worksheets.Count End Function -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" <Vincdc@discussions.microsoft.com> wrote in message news:DA1A3F0D-947F-44B7-B5A0-B28671216EC9@microsoft.com... > Hello: > Is there any function or other method to co...

Excel Cell Formatting #3
How do I centeer a picture in an Excel cell? Hi you can't as such objects 'float' above the cells in Excel -- Regards Frank Kabel Frankfurt, Germany "Tom Coffey" <Tom Coffey@discussions.microsoft.com> schrieb im Newsbeitrag news:8E7356E1-4318-42F3-B9C6-3D7EDFBBEEA1@microsoft.com... > How do I centeer a picture in an Excel cell? ...

Bullets and Numbering
I am trying to create a template in Publisher for which i want to customise bullets as user define pictures. I am not able to do so. Is there any way out for this? Format copying from Word document doesnt seem to work also. Might try finding a dingbat font that suits you. Scaling down an image is an option. Bullets are fonts. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Lavanya" <Lavanya@discussions.microsoft.com> wrote in message news:FF3C5721-0A9A-4DA6-AB3D-B64BCAD6E3D1@microsoft.com... >I am trying to create...

Cell possibilities?
Hi, I have a spreadsheet which has dates when people have attended a course. The sheet then updates itself through conditional formatting and formulas to let me know when course dates have run out. Is it possible through using either a formula or conditional formatting that after a certain time period e.g. two months after a course date has run out, that the date in the cell is erased? If so how id it done? Thanks in advance. What formulas and conditional formulas are you presently using to determine when course dates expire? And what exactly do you mean by "erased"? Do you me...

How can I get a combo box to get data based on a text box?
Hi, I have a text box that looks up an ID number from a table. I want the User to type in an ID and then have a combo box on the same form that gives a list of dates that correspond to that ID in the text box. Right now, the combo box displays all the dates from all the ID's. I want it to only show the dates that are linked to that ID. Please help, Thanks Use the text box's AfterUpdate event to modify the combo box's RowSource property: Me.ComboBox1.RowSource = "SELECT ... FROM ... WHERE FieldID=" & Me.TextID & ";" tyler.deutsch@gmail.com wrote...

cells don't adjust
when worksheet is sorted the corresponding cells or the cells linked to the right, don't move with them. they stay in the same spot. Also this is a big work group with worksheets that are linked and referenced together.... In other words the cells to the right of the cells be sorted don't shift down when I sort the page. If you need meto explain more I can! "boraguru" wrote: > when worksheet is sorted the corresponding cells or the cells linked to the > right, don't move with them. they stay in the same spot. Also this is a big > work group with work...

how do I print a booklet from publisher that is on four pages on .
I am trying to print a booklet onto one page (4 page booklet onto one 8 x 11 sheet of paper that can be folded-2 "booklets per side of paper) and it either prints a blank page or the wording is over each other. I haver tried about 30 different ways by my printer settings and can not seem to get it. I was able to do this booklet in the past but it won't do it any more. Any help would be much appreciated! Thanks Hi, Carol -- In Publisher, select Page Setup. Then select layout...from the menu, select "booklet." My system defaults to 8.5 x 11 landscape...giving you...

Changing coler of tracking cells
When I use "track changes" the changed cells are outlined in lavender which is really hard to see. Is there a way to change the highlight color to a darker one? ...

What could go wrong? Incrementing numbers on pages
Someone wanted each page of a document to have numbers incrementing from 106367. I'm sure there's a better way, but what I did was put it in the place of page numbers as ={PAGE} + 106366. It worked, but now I'm wondering what could go wrong, and what the real method of doing that would have been. There still might be time to change it. Thanks again and again! Although calculated page numbers work, they won't be used by Word in cross-references or tables of contents. A better approach is to change the "Start at" value in the Page Number Format dia...