[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

sequential numbering

i would like count the number value in column g.see the attached workbook
seqBook1.xls
0
Svgmassive
Asked:
Svgmassive
  • 7
  • 6
  • 2
  • +1
1 Solution
 
aikimarkCommented:
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
 
aikimarkCommented:
If so, then put this formula in E21 and fill down
=E20+(LEN(G21)<>0)

Open in new window

0
 
Glenn RayExcel VBA DeveloperCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
Rob, it doesn't work. :-(  After a pair of values, it increments after the second blank cell.

See example.
EE-seqBook1.xls
0
 
Rob HensonFinance AnalystCommented:
Aagh, only tried it down as far as first pair.

Thanks
Rob H
0
 
aikimarkCommented:
No one is giving any attention to the first and simplest formula?  I feel like Rodney Dangerfield -- no respect at all.
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
aikimarkCommented:
@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
 
Glenn RayExcel VBA DeveloperCommented:
@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
 
aikimarkCommented:
@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
 
SvgmassiveAuthor Commented:
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
 
aikimarkCommented:
@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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
SvgmassiveAuthor Commented:
great
0
 
aikimarkCommented:
@svgmassive

Please answer my question about the numbering.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now