Excel Table - Convert Range-Based Formula to Field-Based

Question

I have inherited a very large spreadsheet and am trying to migrate it to a database. The table has over 300 columns, many of which reference other columns.

By converting it to a table (ListObject) in Excel, I thought it would be easier to deconstruct the logic... basically turn the formula:

=CJ6-CY6

into

=[@[Sale Price]]-[@[Standard Cost]]

Converting it to a table worked great... unfortunately it didn't change any of the embedded formulas. They still reference the ranges.

I think I may notionally understand why -- if a formula references a value in another row, then it's no longer a primitive calculation. But for formulas that are all on the same row, I'm wondering if there is any way to convert them without manually going into each of these 300+ columns and re-writing them. Some of them are beastly. No joke, this is an example:

=IF(IF(IF(HD6="",0,IF(HD6=24,0,IF(HD6="U",((FI6-(ES6*12))*$I6),($I6*FI6)*HS6)))<0,0,IF(HD6="",0,IF(HD6=24,0,IF(HD6="U",((FI6-(ES6*12))*$I6),($I6*FI6)*HS6))))>GO6,GO6,IF(IF(HD6="",0,IF(HD6=24,0,IF(HD6="U",((FI6-(ES6*12))*$I6),($I6*FI6)*HS6)))<0,0,IF(HD6="",0,IF(HD6=24,0,IF(HD6="U",((FI6-(ES6*12))*$I6),($I6*FI6)*HS6)))))

And it's not the worst one.

If anyone has ideas, I'd welcome them. I'm open to anything. VBA included.

I have struggled with this myself and found no solution. A tip for keeping the references structured: you can reference values above/below the current row using the OFFSET formula paired with named ranges for out-of-table values. — Daghan, Dec 06 '19 at 14:50
^ But OFFSET is volatile so it's probably not a good recommendation for a very large spreadsheet. — BigBen, Dec 06 '19 at 14:52
In a table you only have to change one row. Could you not do a find and replace? — SJR, Dec 06 '19 at 15:20
@SJR -- yes, but it would be 300+ find and replace actions, one at a time. I'm trying to brute force VBA, but I was hoping there was an elegant solution somewhere — Hambone, Dec 06 '19 at 15:23
I wish the owner would have structured this as a table to begin with... but while I'm wishing, I also really would like a pony — Hambone, Dec 06 '19 at 15:24
A loop? Don't think we can tell without having more details. — SJR, Dec 06 '19 at 15:25
Yep, exactly my brute force attempt (work in progress). If the community at large doesn't know of native solution, this may be the only one. I'll post what I did *if* it works — Hambone, Dec 06 '19 at 15:30
You could consider re-thinking the approach and use lookups rather than nesting lots of IFs. — SJR, Dec 06 '19 at 15:34
Actually not sure if that will be easily achievable, that formula just looks as if it must be simplifiable. — SJR, Dec 06 '19 at 15:35
Yes, exactly! I'm actually scrapping the whole thing and moving it to a relational database... joins in lieu of nested if's and vlookups, windowing functions, views for calculated columns, etc. This is a project another coworker took on, and when he reached the point where Excel couldn't do the work he needed, he handed off what he has so far to us. We are just trying to reverse engineer what it's currently doing (so far). But it's very hard when I look at (AB6-BQ6)/Z7. — Hambone, Dec 06 '19 at 15:38
@Daghan - don't know if my solution will help you in the future, but check it out — Hambone, Dec 06 '19 at 19:34

Hambone · Answer 1 · 2019-12-06T20:32:43.153

I would never use this to teach computer science, but this is the hack that did the trick. To keep things simple, I transposed header names and the corresponding column into A17:

And then this VBA code successfully transformed each range into the corresponding column property.

Sub FooBomb()

  Dim ws As Worksheet
  Dim r, rw, translate As Range
  Dim col, row As Integer
  Dim find, anchored, repl As String

  Set ws = ActiveWorkbook.ActiveSheet
  Set rw = ws.Rows(6)
  Set translate = ws.Range("A17:B363")

  For col = 12 To 347
    Set r = rw.Cells(1, col)

    For row = 363 To 17 Step -1
      find = ws.Cells(row, 1).Value2 & "6"
      anchored = "$" & find
      repl = "[@[" & ws.Cells(row, 2).Value2 & "]]"

      r.Formula = VBA.Replace(r.Formula, anchored, repl)
      r.Formula = VBA.Replace(r.Formula, find, repl)
    Next row

  Next col

End Sub

Hard-coded and not scalable, but I'm not looking to repeat this ever again.

-- EDIT --

Word to the wise to help performance, especially with as many columns and formulas are in this spreadsheet.

Set Formula calculation to manual before
Check before the field exists before doing a replacement -- skipping happens more often than not

Program ran in a few seconds (minutes prior) before these changes:

  If InStr(r.Formula, anchored) > 0 Then
    r.Formula = VBA.Replace(r.Formula, anchored, repl)
  End If

  If InStr(r.Formula, find) > 0 Then
    r.Formula = VBA.Replace(r.Formula, find, repl)
  End If

Excel Table - Convert Range-Based Formula to Field-Based

1 Answers1