Sequencial order

[Cellardoor]

Greetings everyone

I have one excel table which, for every country in column "Country", the column "Number" supposedly gives me a sequential number, by a countif formula

The thing is that, if i add (in "Country") the same country as the previous one, the formula repeats the number.

I cant explain it better., sorry

In the attached file, i've placed the table twice. The second has the error (cell H21 and H22) that i can't solve...

Can you help me please?

Hope its clear the explanation...Anything just ask

Thanks in advance

 

[r1c1]

@Cellardoor

In your file there is an in-consistency in the formulas.

Formula in G21 reads
=TEXT(COUNTIF(H$7:H22,H21),"_00")

Where as it should be
=TEXT(COUNTIF(H$7:H21,H21),"_00")

Once you change it, the result appears correctly.

You can do this by selecting the formula cell G7 and filling it down once again.

 

[Deepak]

Do you want auto sequence in a range then use this!!

Sub AASerialincolumnA()
Dim crfill As Long, wst As Worksheet

Set wst = ActiveSheet
crfill = wst.Range("B" & Rows.Count).End(xlUp).Row' Helper column to serial in col A

    With wst.Range("A2:A" & crfill)'change the range as needed
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, step:=1, Trend:=False
    End With
  
End Sub

 

[Cellardoor]

@r1c1

Indeed i've noticed that inconsistency. I have a macro that, after some procedures, will select G7 and fill down.

The thing is that instead of running this macro (i've created it just for this error), i need to have these "Num" corrects, because they have impact in other sheets.

If there's no option, i'll still run it - its not the fastest procedure, but the one's available

Many thanks for the Help.

@Deepak

Thanks for the command. i think i'll needed it later.

 

[r1c1]

@Cellardoor Now I understand what you mean. This seems like a feature / bug with tables.

You can use worksheet_change event to update the formulas everytime someone adds data. This could prevent the mistake. There may be another way to automatically handle this, but I cant think of any right now.

 

[Cellardoor]

Yes...a Bug...It's seems.

worksheet_change event? Right...I'll try it...and place the results

Thank you

 

[NARAYANK991]

Hi ,

I am not able to understand your problem other than the fact that the file has an error ; are you saying that this problem happens every now and then ?

I tried adding many more rows to both tables , and the problem never recurred.

There should be no need for any macro , since the formula keeps getting copied to every new row that is added.

Narayan