Excel Update columns automatically

Question

I have following two excel columns. They have a parent child relationship. for instance if row 1 cell D value is 1000000000, then it may be a parent of a different row.

What I am trying to do is have an excel sheet with 1000 rows. The ID structure I recieved is incorrect. I am trying to start the New ID from 1 and increment to 10 so the next ID would be 10. So with this pattern I want to replace the existing New ID's starting from 1 and then also update the New ParentID column with the updated NewID.

I was trying to write a formula to achieve this but Im very new to excel. Would this have to be done with a macro? Any suggestions?

Current Data Example

 D           E
New ID      New Parent ID
1000000000  0
1100000000  1000000000
1100000001  1100000000
1100000002  1100000000
1100000003  1100000000
1100000004  1100000000
1100000005  1100000000
1100000006  1100000000
1100000007  1100000000
1200000000  1000000000
1200000001  1200000000
1200000002  1200000000
1200000003  1200000000
1200000004  1200000000
1200000005  1200000000
1200000006  1200000000
1200000007  1200000000
1200000008  1200000000
1200000009  1200000000
1200000010  1200000000
1200000011  1200000000
1200000012  1200000000
2000000000  0
2000000001  2000000000
2000000002  2000000000
3000000000  0
3100000000  3000000000
3100000001  3100000000
3100000002  3100000000
3100000003  3100000000
3100000004  3100000000

New Data Example Notice the new pattern. So New ID 1000000000 was replaced with 1 and where 1000000000 was being used in the NewParentID column, thats also replaced with 1. And so on.. So essentially I could easily create the incremental values in the NewID, I just dont how to update the second column with the correct id.

   New ID   New Parent ID
    1             0
    10            1
    1100000001    10
    1100000002    10
    1100000003    10
    1100000004    10
    1100000005    10
    1100000006    10
    1100000007    10

Answer 1

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.

Answer 2

You can do this with a macro is all you want is to build a large array of repeating data. I personally avoid macros and do most of my text generation in Powershell.

Now, if you want to take data and do some calculations on it, we can do that with formulas. Here are the ones I think you need:

• =IF(condition,true value, false value) - Logical tests to make decisions.
• =MOD(Data Cell,Divisor) - Returns the remainder after a division.
• =MROUND(Data Cell, Multiple) - Rounds to nearest defined multiple of number.

When we work with formulas we need to lay out our logic for what actions we want to take in order. All formulas are procedural in nature so this helps figure out what we need to do first. I'm assume that all inputs are in the "New ID" column and all outputs (aka returns) are in the Parent ID column. Looking at your data example, here is what I think you want.

1. Find IDs that are factors of "1000000000" (10^9) and return a 0.
2. Find IDs that are factors of "100000000" (10^8) and round down to a whole number that is a factor of "1000000000" (10^9).
3. Round remaining IDs to a whole number that is a factor of "100000000" (10^8)

There are big numbers to figure out so here is an example of what each step does.

• Step 1: 700000000 returns 0
• Step 2: 770000000 returns 700000000
• Step 3: 770000007 returns 770000000

If that is correct, then let's setup you functions:

Step 1

The first equation we use is the "MOD" Equation. If we divide every number by "1000000000" (10^9), we can look for any number with a remainder of zero. Each one we find is one that want to return a zero with. To do that, we need to use the "IF" Formula. Here is what your formula in Cell E2 would look like:

=IF(MOD(D2,1000000000)=0,0,"false")

This first IF parameter tests to see if the number in D2 has a remainder when divided by 10^9. If it doesn, return a zero, if not return the text "false"

Step 2

Now that we have our first group of numbers handled, we can add an IF statement to the mix to chain out logic together. We now need the "FLOOR" equation to round a number to number of significant digits. In this case, eight digits. Here is what the formula would look like alone:

=IF(MOD(D2,10000000)=0,MROUND(D2,1000000000),"false")

Then we need to chain it into the formula in step 1. Replace the "false" in step 1 with the formula in step 2.

Step 3

Finally, we just need to round down the remaining numbers. We can do this with another MROUND. Here is the formula alone:

=MROUND(D2,100000000)

And now the whole thing chained together:

=IF(MOD(D2,1000000000)=0,0,IF(MOD(D2,10000000)=0,MROUND(D2,1000000000),MROUND(D2,100000000)))

Give that a try and I hope that it works. When I run this formula against your data in Column D, I get the same numbers you have in Column E.