Tuesday, July 2, 2013

yogi_Start New ID Number In Column K From The Beginning On Cell Value Change In Column J

                                          Google Spreadsheet   Post  #1268
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 02, 2013
user AlexEmbryo (http://productforums.google.com/forum/?zx=aif3093lky7l#!mydiscussions/docs/nVIeoFgcE_c)
Starting new ID numberfrom the beginning when the cell value change
I have a query function that generates the small table like this (Column A):

ColA  ColB
Text1  100
Text1  101
Text1  102
Text1  103
Text2  100
Text2  101
Text2  102
Text3  100
Text3  101
Text4  100
Text4  101
Text4  102
Text4  103
Text4  104
Text4  105
Text4  106

I would need to find an arrayformula or something else that would generate the ID number to column B. This ID number should start from the 100 every time when the text changed in column A. When I change some value in this Query, these ID numbers should be generate again because texts will change.

Of course I can use IF function, but I dont want to use it. Then I would need to delete some of them, when the column length is smaller.

Any ideas?

Hi Yogi,

In the last sheet is the problem. Cells are marked with yellow.

How to generate ID numbers to column K? This number is needed when there is text in column J. IF its empty, no number. The same name is mentioned only once in one "group", because Query will order them. I need numbering starting from 100 and then 101,102,103 etc. The numbering needs to start from the beginning (100) always when the name changed in column J.