Consecutive Numbers

D

ddecheva

New Member
Joined
Aug 2, 2016
Messages
8
Dear all,

I am struggling in finding a formula that can fulfill the following criteria

If we have a row that is like
1
1
2
2
2
3
1
1
2
2
1
2
3


I want to make it like
1
1
2
2
2
3
4
4
5
5
6
7
8

Practically after the first time of 1 any other 1 to be consecutive and the numbers after.

How do you think I can make it with a formula? Is there a way?

Thank you for your input in advance!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Put your list of numbers in column A starting at A2 then:

=IFERROR(SUMPRODUCT(--($A$2:A2<$A$1:A1),$A$1:A1),0)+A2
 
Upvote 0
Hi, here is another option you can try:


Excel 2013
AB
1Header 1Formula
211
311
422
522
622
733
814
914
1025
1125
1216
1327
1438
Sheet1
Cell Formulas
RangeFormula
B2=IF(A2=A1,B1,N(B1)+1)
 
Upvote 0
Additionally,

After I do the consecutive numbers I need to create random "Fake" 14 to 18 digit numbers, which correspond to those numbers. I will explain better with an example

1 1234567891011
1 1234567891011
2 1234567891012
2 1234567891012
2 1234567891012
3 1234567891013
4 1234567891014
5 1234567891015
5 1234567891015
6 1234567891016
7 1234567891017
8 1234567891018

The ending of the 14 digit number, however, does not need to end like the number to which correspond. The only rule is for all 1s,2s,3s and etc. to be the same.

Do you think this is feasible?
 
Upvote 0
The -- changes a TRUE/FALSE result into 1 for TRUE and 0 for FALSE which enables the multiplication with the 2nd array. It would be quite a long explanation to explain exactly what the formula doing. Do you understand sumproduct?
 
Upvote 0
All formulas are working perfect!

Yes, I do understand the SumProduct, and I did not know that (--) this Means True/False

Thank you all!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
European Roulette Bet Maker
Replies
3
Views
432
lrobbo314
lrobbo314
D
  • Question
Highlighting duplicate consecutive cells by row
Replies
2
Views
300
kvsrinivasamurthy
K
L
  • Question
Count if consecutive value is repeated 3 times.
Replies
2
Views
879
Fluff
Fluff
A
  • Question
Need a formula to calculate a MOD10 check digit for a string of 26 numbers.
Replies
2
Views
548
Georgiboy
Georgiboy
W
  • Question
Excel Formula or VBA to return the corresponding values based on several criteria
Replies
0
Views
278
woah
W

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top