Increment values in one column based on changes in another

[captainentropy]

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>

 

[kevatarvind]

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
	A	B	C	D	E	F
1	ID1	val1	val2	val3	ID2	ID3
2	1	0.214	476	1322	A	1
3	1	0.302	549	1280	A	1
4	2	0.214	477	1252	A	2
5	3	0.302	590	1772	A	3
6	3	0.302	602	1676	A	3
7	3	0.428	597	1600	A	3
8	4	0.214	524	1695	A	4
9	4	0.214	455	1250	A	4
10	4	0.302	502	1970	A	4
11	5	0.214	408	2393	A	5
12	5	0.302	644	1542	A	5
13	5	0.302	646	1919	A	5
14	1	0.302	775	2487	B	6
15	1	0.302	513	1695	B	6
16	1	0.478	560	2032	B	6
17	1	0.478	554	1476	B	6
18	2	0.214	555	1573	B	7
19	2	0.478	650	1369	B	7
20	3	0.302	772	1120	B	8
21	3	0.302	483	1354	B	8
Sheet5

 

[captainentropy]

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