Condense formula

[Derek Y via OfficeKB.com]

Hello,

I have this formula and it's very long. Below is a copy of it

=IF(J_Z_1=CODECOL,INDEX(FINAL_ALL,MATCH(A2,'Final Merge Fields'!A:A,0),MATCH
("Code",FINAL_1,0)),"")

The only part of that whole entire formula that changes as I drag it down is
where it says A2. That should change to A3, A4, and so on.

I have to drag this formula down 50,000 rows in 2 worksheets. Is there
anyway I can possibly condense it by like just putting it in one cell and
telling all the other cells to get it from it except to update the A2,A3 part?


Your help is greatly appreciated.

Derek

 

[Derek Y via OfficeKB.com]

By the way I know this forumla takes forever to calculate, but thats fine
because everytime I use this sheet I will only have to have it calculate once
so thats not a problem. The problem lies in that the sheet will become well
over 100Mb when i'm done with it if i have to drag that formula all over

 

[Derek Y via OfficeKB.com]

and if this helps at all

J_Z_1 refers to cell W1 on sheet "J-Z"

CODECOL refers to cell A6 on sheet "Single Data"

Final_all refers to 1:65536 on the sheet "Final Merge"

Final_1 refers to 1:1 on sheet "final Merge"

 

[bpeltzer]

It looks like you could first define two cells that have the formulas for the
IF condition and the second match.
Ex: =(J_Z_1 = CODECOL). Define that cell reference as Valid.
And =match("Code",FINAL_1,0). Define that cell as ColNum.
Then your function code be modestly simplified as
=if(Valid,index(final_all,match(...),ColNum),"").
I *think* that you're using FinalMergeFields interchangably with FinalMerge,
in which case this simplies a bit further:
=if(Valid,vlookup(a2,FinalMergeFields,ColNum,0),"").
HTH. --Bruce

 

[Derek Y via OfficeKB.com]

Bruce, That was a huge help.

You were right in that I am using Final merge fields interchangably. The
seperate sheet is actually 'Final Merge Fields'.

I got those definitions to work just fine and it made it a bit shorter, but
your final simplification where you are using VLookup instead, I could not
get to work. All I did was this so far:

=MATCH("Code",FINAL_1,0) defined as ColNum
='CITY (A-I)'!$V$1='SINGLE DATA'!$A$6 defined as Valid

replaced those two area's of my original formula with those words.

When I make your final replacement it wont even accept it as a formula. I
guess I dont know what
FinalMergeFields is supposed to be referring to in your formula or how to
define that.

Thanks SO much for your help.

Derek

It looks like you could first define two cells that have the formulas for the
IF condition and the second match.
Ex: =(J_Z_1 = CODECOL). Define that cell reference as Valid.
And =match("Code",FINAL_1,0). Define that cell as ColNum.
Then your function code be modestly simplified as
=if(Valid,index(final_all,match(...),ColNum),"").
I *think* that you're using FinalMergeFields interchangably with FinalMerge,
in which case this simplies a bit further:
=if(Valid,vlookup(a2,FinalMergeFields,ColNum,0),"").
HTH. --Bruce

and if this helps at all

[quoted text clipped - 23 lines]

 

[Derek Y via OfficeKB.com]

P.S. here is my new shorter formula that I have thus far.

=IF(Valid,INDEX(FINAL_ALL,MATCH(A2,'Final Merge Fields'!A:A,0),ColNum),"")

 

[Derek Y via OfficeKB.com]

and

='Final Merge Fields'!$1:$65536 is defined as FINAL_ALL

 

[bpeltzer]

Ah, clearer now, and I see why that last step wouldn't produce a valid
formula ;-)
How about =if(valid,vlookup(A2,FINAL_ALL,ColNum,0),"")

 

[Derek Y via OfficeKB.com]

Works perfectly!!!!!!!!! Thank you veryyy much.

I would like to ask for just one more tid-bit of advice/help.

I know how to write macros by recording them, and editing them like after
they are recording I can see the cell numbers and whatnot and I can play
around with that stuff, no problem. But here is what I would like to do:

Have the cursor on say W2. And W2 already has that formula in it.

{=if(valid,vlookup(A2,FINAL_ALL,ColNum,0),"")}

But all the rows below it are completly blank.

I would like to be able to push CTRL SHIFT something and have excel

1) turn off automatic calculation (because it would kill it)
2) drag that formula down 60,000 rows in that column
3) calculate what should be in there (as if i would manually push F9 now)
4) copy that entire column
5) paste just VALUES for that column

but I want to be able to run this macro on any column I may be in (so X2, Y2,
etc.)

for parts 4 and 5, the following works....

Sub FreezeColumn()
With ActiveCell.Columns
.EntireColumn.Copy
.EntireColumn.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False

End Sub

and as far as parts 2 and 3

Sub Macro1()
'
Selection.AutoFill Destination:=Range("X2:X35"), Type:=xlFillDefault
Range("X2:X35").Select
Calculate
End Sub

i see that calculate kind of takes the place of my F9, but I want to make
sure that before the macro is run that the auto calculation is turned off and
turned back on after. Also I dont know how to make this work in general for
any column that is selected when that particular cell is selected.

This may help for the auto calculation and manual calculation business:

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Any additional help you could provide is appreciated.

Derek

 

[bpeltzer]

You've got the right property to turn off auto-calc and turn it back on:
Application.Calculation = xlCalculationManual
.... all your code that doesn't need calculations to be kept current ...
Application.Calculation = xlCalculationAutomatic
(BTW, when I turn auto-calc off and on, I often turn screenupdating off and
back on. That property is application.ScreenUpdating and the values are
False then True. Particularly if you're looping, it can save visual
dizziness and LOTS of time.)
As for selecting a varying range, you might do something like
Dim WhichCol as integer
and then
WhichCol = Selection.Column
Range(Cells(2, WhichCol), Cells(35, WhichCol)).Select

--Bruce