For a new Excel user, I'd stay away from VBA unless necessary. If I understand your question correctly, you can solve it with formulas only. I tried to make them as simple as possible.
First, I'd preserve the original data set by working on an image of the source data. Its useful for auditing purpose and it may help you better understand how it works. The columns would look like this:
ID | Parent ID | New ID | New Parent ID
----------------------------------------------------
1000000000 | 0 | |
1100000000 | 1000000000 | |
1100000001 | 1100000000 | |
1200000000 | 1000000000 | |
1200000001 | 1200000000 | |
1200000002 | 1200000000 | |
(For all examples I'll use the same trimmed down example data set. First column is column A, column headers are on line 1.)
We'll give a New ID to the child element in the New ID column and its Parent ID will be automatically valued in the New Parent ID column.
Typed-in IDs
For now, just copy-paste the ID values into the New ID column:
ID | Parent ID | New ID | New Parent ID
1000000000 | 0 | 1000000000 |
1100000000 | 1000000000 | 1100000000 |
1100000001 | 1100000000 | 1100000001 |
1200000000 | 1000000000 | 1200000000 |
1200000001 | 1200000000 | 1200000001 |
1200000002 | 1200000000 | 1200000002 |
Then in the first cell of the New Parent ID, enter the following formula: =VLOOKUP($B2,$A:$C,3,FALSE)
. This formula makes Excel look up vertically. It reads like this: "look up the value of the cell B2 (Parent ID) in the column A (ID column) and when you find an exact match, return the value in the third column (column C, New IDs)".
Given how our data is laid out, it really means "look up the Parent ID in the ID column and when you find an exact match, return its New ID". Right now the New IDs are identical to the original ID, we'll take care of that later. If you need more explanations about the use of functions (like VLOOKUP
) in formulas, click the fx
button right next the formula bar and then the "more help on this function" help link.
As soon as you type Enter, the formula will be replaced by its result and in that case, the error value #N/A
. This is because the first Parent ID can not be found in the ID column. This is logical since the top-most parent has by definition no parent.
To deal with that case, we'll upgrade our formula to =IF($B2=0,0,VLOOKUP($B2,$A:$C,3,FALSE))
. It reads "if the value in the cell B2 is 0, then return 0, else look up [...]" or "if the Parent ID is 0, then return 0, else look up its New ID".
It's time to apply this formula to the whole column, which you can do with an easy copy and paste:
ID | Parent ID | New ID | New Parent ID
1000000000 | 0 | 1000000000 | 0
1100000000 | 1000000000 | 1100000000 | 1000000000
1100000001 | 1100000000 | 1100000001 | 1100000000
1200000000 | 1000000000 | 1200000000 | 1000000000
1200000001 | 1200000000 | 1200000001 | 1200000000
1200000002 | 1200000000 | 1200000002 | 1200000000
That's where the magic happens
Now overwrite a New ID. As in your example, replace 1000000000
with 1
. You'll see the New Parent ID updates immediately with the New ID. Play again and replace 1100000000
with 10
:
ID | Parent ID | New ID | New Parent ID
1000000000 | 0 | 1 | 0
1100000000 | 1000000000 | 10 | 1
1100000001 | 1100000000 | 1100000001 | 10
1200000000 | 1000000000 | 1200000000 | 1
1200000001 | 1200000000 | 1200000001 | 1200000000
1200000002 | 1200000000 | 1200000002 | 1200000000
That was fun! But doing it a thousand times, no thank you, right?
Auto-valued IDs
That's where Excel shines, because once you've done the heavy thinking, it will do all the heavy lifting. In the cell C4, type in this formula: =C3+10
. This one adds ten to the value in the cell C3, the cell just above, effectively incrementing the New ID by ten. Copy-paste it up to the end of the column and you're all set:
ID | Parent ID | New ID | New Parent ID
1000000000 0 1 0
1100000000 1000000000 | 10 | 1
1100000001 1100000000 | 20 | 10
1200000000 1000000000 | 30 | 1
1200000001 1200000000 | 40 | 30
1200000002 1200000000 | 50 | 30
Copy-pasting of formulas
A last word of caution. I guess you'll be using the New IDs and New Parent IDs, possibly in other workbooks. In that case, you probably want to make a special pasting, to retain only the value, and not the formula.
Let's say that once you're done, you want to delete the original ID and Parent ID columns. Before doing so, copy the New ID and New Parent ID columns as you usually do. Then, instead of pasting, use the special paste
command and choose Values, then click OK. The special paste
command is available with a right click on the destination, among other places.