Tjyylli

False written accusations not made public - is there law to cover this?

What evolutionary advantage do viruses have in host specificity?

When can a QA tester start his job?

Why was Lupin comfortable with saying Voldemort's name?

Has any human ever had the choice to leave Earth permanently?

How to deal with an incendiary email that was recalled

What is the purpose of easy combat scenarios that don't need resource expenditure?

How much mayhem could I cause as a sentient fish?

Absorbing damage with Planeswalker

SET NOCOUNT Error in handling SQL call after upgrade

Is there any risk in sharing info about technologies and products we use with a supplier?

Should I reinstall Linux when changing the laptop's CPU?

Move fast ...... Or you will lose

Slow While Loop, Query Improvment Assistance

What is the use of 'de' preposition in 'Yo voy *de* compras todos los sábados'

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

Why exactly do action photographers need high fps burst cameras?

How do I append a character to the end of every line in an Excel cell?

Can we harness gravitational potential energy?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

Why wasn't TEventArgs made contravariant in the standard event pattern in the .NET ecosystem?

Does every functor from Set to Set preserve products?

How to play electric guitar and bass as a duet

Do theoretical physics suggest that gravity is the exchange of gravitons or deformation/bending of spacetime?

How do I generate a sequential number within an IF formula?

Excel 2007 Conditional Formatting is not properly using custom formula providedHow to change a function/formula in a whole column where I also have empty cellsCOUNTIFS not counting blank cells as zero (when reference says it does)How can I check cells for number series?Excel - Random Numbers - and If statementExcel: Find a specific cell and paste the value from a control cell into itExcel: Find duplicates among 2 columsHow do I set up a conditional formula with multiple criteria?Group and sort the rows of a tableManipulate multiple excel columns relative to one another

0

Columns A, B and C contain values which describe a product. I want column D to generate a sequential number based on the contents of a given cell in column A. Together the four values make up the product code.

• Column A: Location


• Column B: Species


• Column C: Date


• Column D: Reference Number

There are multiple locations which will fill the cells in column A. I want the reference number to begin with 1 for each location. My approach is to use a nested IF formula to isolate the locations, but I can't figure out how to generate a unique sequential reference number. Any thoughts?

microsoft-excel-2007

share|improve this question

edited Oct 15 '12 at 23:58

tom

asked Oct 15 '12 at 23:13

tomtom

42

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Could you elaborate on how should the reference number (column D) be generated? just sequenced number, or should it depend columns A,B,C?
  
  – Michael
  Oct 15 '12 at 23:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  An example would be useful, too.
  
  – Scott
  Oct 15 '12 at 23:31
  

  
  
  
  

add a comment | 

0

Columns A, B and C contain values which describe a product. I want column D to generate a sequential number based on the contents of a given cell in column A. Together the four values make up the product code.

• Column A: Location


• Column B: Species


• Column C: Date


• Column D: Reference Number

There are multiple locations which will fill the cells in column A. I want the reference number to begin with 1 for each location. My approach is to use a nested IF formula to isolate the locations, but I can't figure out how to generate a unique sequential reference number. Any thoughts?

microsoft-excel-2007

share|improve this question

edited Oct 15 '12 at 23:58

tom

asked Oct 15 '12 at 23:13

tomtom

42

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Could you elaborate on how should the reference number (column D) be generated? just sequenced number, or should it depend columns A,B,C?
  
  – Michael
  Oct 15 '12 at 23:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  An example would be useful, too.
  
  – Scott
  Oct 15 '12 at 23:31
  

  
  
  
  

add a comment | 

Columns A, B and C contain values which describe a product. I want column D to generate a sequential number based on the contents of a given cell in column A. Together the four values make up the product code.

• Column A: Location


• Column B: Species


• Column C: Date


• Column D: Reference Number

There are multiple locations which will fill the cells in column A. I want the reference number to begin with 1 for each location. My approach is to use a nested IF formula to isolate the locations, but I can't figure out how to generate a unique sequential reference number. Any thoughts?

microsoft-excel-2007

share|improve this question

edited Oct 15 '12 at 23:58

tom

asked Oct 15 '12 at 23:13

tomtom

42

share|improve this question

edited Oct 15 '12 at 23:58

tom

asked Oct 15 '12 at 23:13

tomtom

42

share|improve this question

edited Oct 15 '12 at 23:58

tom

asked Oct 15 '12 at 23:13

tomtom

42

asked Oct 15 '12 at 23:13

tomtom

42

asked Oct 15 '12 at 23:13

tomtom

42

1 Answer
1

active

oldest

votes

0

Slightly guessing what is required, if you sort your three columns A/B/C with columns labels in Row1 then this formula in D2 (or something similar) and copied down may suit: =IF(AND(A2=A1,OR(B2<>B1,C2<>C1)),D1+1,1). The OR ensures that Species and Date are not both duplicates, the AND requires that the Location is a duplicate of the record immediately above. IF increments the counter if so and resets to 1 if not.

share|improve this answer

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f488201%2fhow-do-i-generate-a-sequential-number-within-an-if-formula%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Slightly guessing what is required, if you sort your three columns A/B/C with columns labels in Row1 then this formula in D2 (or something similar) and copied down may suit: =IF(AND(A2=A1,OR(B2<>B1,C2<>C1)),D1+1,1). The OR ensures that Species and Date are not both duplicates, the AND requires that the Location is a duplicate of the record immediately above. IF increments the counter if so and resets to 1 if not.

share|improve this answer

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239

add a comment | 

0

Slightly guessing what is required, if you sort your three columns A/B/C with columns labels in Row1 then this formula in D2 (or something similar) and copied down may suit: =IF(AND(A2=A1,OR(B2<>B1,C2<>C1)),D1+1,1). The OR ensures that Species and Date are not both duplicates, the AND requires that the Location is a duplicate of the record immediately above. IF increments the counter if so and resets to 1 if not.

share|improve this answer

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239

add a comment | 

Slightly guessing what is required, if you sort your three columns A/B/C with columns labels in Row1 then this formula in D2 (or something similar) and copied down may suit: =IF(AND(A2=A1,OR(B2<>B1,C2<>C1)),D1+1,1). The OR ensures that Species and Date are not both duplicates, the AND requires that the Location is a duplicate of the record immediately above. IF increments the counter if so and resets to 1 if not.

share|improve this answer

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239

share|improve this answer

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239

answered Oct 15 '12 at 23:49

pnutspnuts

5,55032239