# Increment values in one column based on changes in another

#### captainentropy

##### Board Regular
I have lots of very large tables that look like the table below (just the first 4 columns; edited for clarity, column 5 is what I want to achieve). There's two groupings of data: ID1 and ID2. As the values in ID2 change the numbering in ID1 resets. But I need to create another ID - ID3 - which will continue the numbering in ID1 based on the last change in ID2 and retain the ID1 grouping (if it helps conceptually, ID2 identifies an image, and ID1 represents discreet units within the images, where each row is a unique element in each unit having multiple data points - val1-3). Ultimately I need to do analysis of each unit (ID1) but I can't group by ID1 since the numbering resets for each image (ID2). I hope this makes sense. Is this doable?

 ID1 val1 val2 val3 ID2 ID3 1 0.214 476 1322 A 1 1 0.302 549 1280 A 1 2 0.214 477 1252 A 2 3 0.302 590 1772 A 3 3 0.302 602 1676 A 3 3 0.428 597 1600 A 3 4 0.214 524 1695 A 4 4 0.214 455 1250 A 4 4 0.302 502 1970 A 4 5 0.214 408 2393 A 5 5 0.302 644 1542 A 5 5 0.302 646 1919 A 5 1 0.302 775 2487 B 6 1 0.302 513 1695 B 6 1 0.478 560 2032 B 6 1 0.478 554 1476 B 6 2 0.214 555 1573 B 7 2 0.478 650 1369 B 7 3 0.302 772 1120 B 8 3 0.302 483 1354 B 8

<tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### kevatarvind

##### Well-known Member
TRY BELOW BUT FIRST SORT YOUR DATA 1ST BY ID2(ascending) THEN BY ID1 (ascending) THE USE BELOW FORMULA AND F3 FORMULA DRAG DOWN AS YOU WANT
Excel Workbook
ABCDEF
1ID1val1val2val3ID2ID3
210.2144761322A1
310.3025491280A1
420.2144771252A2
530.3025901772A3
630.3026021676A3
730.4285971600A3
840.2145241695A4
940.2144551250A4
1040.3025021970A4
1150.2144082393A5
1250.3026441542A5
1350.3026461919A5
1410.3027752487B6
1510.3025131695B6
1610.4785602032B6
1710.4785541476B6
1820.2145551573B7
1920.4786501369B7
2030.3027721120B8
2130.3024831354B8
Sheet5

Last edited:

#### captainentropy

##### Board Regular
Ugh, that was embarrassingly simple. I tried something similar but I somehow mucked it up. Thanks kevatarvind!

Replies
11
Views
169
Replies
0
Views
226
Replies
6
Views
361
Replies
10
Views
646
Replies
3
Views
181

1,195,937
Messages
6,012,414
Members
441,698
Latest member
DaveTeo

### 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.

### Which adblocker are you using?

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

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