Solved

sequential numbering

Posted on 2014-07-26
17
59 Views
Last Modified: 2014-08-03
i would like count the number value in column g.see the attached workbook
seqBook1.xls
0
Comment
Question by:Svgmassive
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40221380
Did you mean this pattern?  (Every time a non-empty value appears in column G, the value in column E increases)
1		503
1		
1		
1		
1		
1		
1		
2		515
2		
2		
2		
3		115
4		101

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40221385
If so, then put this formula in E21 and fill down
=E20+(LEN(G21)<>0)

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40221759
If you want your described pattern - where the value in F increments only if a value in G exists with a blank cell below -  insert a "1" in cell F20, then insert this formula in cell F21 and copy down:
=IF(G21="",F20,IF(G22<>"",F20,F20+1))

Regards,
-Glenn
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40223858
How about this instead in E20:

=IF(G19<>"",E19,COUNT(G$20:G20))

copied down as far as required down column E.

Thanks
Rob H
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40224331
Rob, it doesn't work. :-(  After a pair of values, it increments after the second blank cell.

See example.
EE-seqBook1.xls
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40224557
Aagh, only tried it down as far as first pair.

Thanks
Rob H
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40224627
No one is giving any attention to the first and simplest formula?  I feel like Rodney Dangerfield -- no respect at all.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40224706
Heh...I added your formula to the example workbook, too, aikimark.  Yours increments with each occurrence of a value in column G; not exactly what the user asked for.  No disrespect meant. :-)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:aikimark
ID: 40224715
@Glenn

That is why I posted a different version of the result data.  There was a discordance between the description of the problem and the posted sample data.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40224740
@aikimark,
I agree that your formula is indeed the simplest and most-logical when wanting to display a continuous, incrementing count by values in another column.  But the questioner wrote in the workbook:

I would like to do sequential numbering in column e , where the numbers fall together eg. Row 32,115 and row 33 100 I would like row 31 to have the sam sequential number as the previous

There's some transcription issues here, but re-written to match the actual example data:

I would like to do sequential numbering in column E where the numbers fall together eg. Row 31, 115 and row 32 101, I would like row 31 to have the same sequential number as the previous [value].

And it does include an example of how the sequence should be displayed that agrees with this statement.

Just need the questioner to get back in here and clear it all up for us!

-Glenn
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40224749
@Glenn

Without some algorithmic rule to determine whether the number is incremented or not, the problem is unsolvable for any instance (configuration and amount of data) but this one.  Such a solution is too trivial for anything but a manual process.
0
 

Author Comment

by:Svgmassive
ID: 40225713
glen's formula is exactly what i am looking for =IF(G21="",F20,IF(G22<>"",F20,F20+1))
i am using the formula to alternate shade the worksheet,i just use the mod function in the populated column and everything is fine
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40225732
@Svgmassive

Can you explain the algorithm?  Why does the 115 row still have the number 2 as opposed to the number 3?  I asked about this way back in comment http:#a40221380
I would like an answer.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40225767
Svgmassive, if you're looking for a contextual method for alternating shading rows, you should read my article on Conditional Formatting; it covers just this type of method.

-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40233491
Hi svgmassive,

If my original solution is acceptible, can you please properly close this question by clicking the "Accept this solution" link above that submission (not this post).  This will help ensure that future searches are meaningful to other EE members.

If you still have issues or questions, please let me know.

Thanks,
-Glenn
0
 

Author Closing Comment

by:Svgmassive
ID: 40237158
great
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40237347
@svgmassive

Please answer my question about the numbering.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now