Hard coded formula fix, VBA for column ordering

[undrline via OfficeKB.com]

I set up Excel "templates" for some pretty standard database extracts. It has
a summary sheet, and a data sheet. I paste new data in the data sheet, and
voila, all the formulas update in the summary sheet. The internal customers
love this, because it allows them to use whatever meshuggah format for
summaries they want, rather than a simple pivot table. But, the cells in my
summary end up with formulas like this:

=(SUMPRODUCT(--((ISNUMBER(SEARCH("Medicare",'Closed - Complaint'!Y1:Y65534))
=FALSE)*(ISNUMBER(SEARCH("Medicaid",'Closed - Complaint'!X1:X65534))=FALSE)*
('Closed - Complaint'!A1:A65534<>"")),--(('Closed - Complaint'!AF1:
AF65534="Fully Insured")+('Closed - Complaint'!AF1:AF65534="Shared Risk")+
('Closed - Complaint'!AF1:AF65534="At Risk")),('Closed - Complaint'!K1:K65534)
)+SUMPRODUCT(--(('Closed - Appeal'!A1:A65535<>"")*('Closed - Appeal'!AL1:
AL65535<>"MCRE")*('Closed - Appeal'!AL1:AL65535<>"MCAID")*('Closed - Appeal'!
C1:C65535<>"SND")*('Closed - Appeal'!AK1:AK65535="FI")*('Closed - Appeal'!$A
$1:$A$65535="Provider Dispute")),('Closed - Appeal'!$V$1:$V$65535)))/E6

Firstly, there's gotta be a better way than such a convoluted formula. I mean,
really, it's like I'm reproducing a SQL SELECT statement in Excel-eze, with
only 1,024 characters to get the job done. How would I do this with VLookup,
for instance?

Secondly, if my columns are out of order, it's been easier to rearrange the
100+ columns and put them in order, rather than edit all the formulas, and
possibly have something break. Let's say I were to continue using the formula
(in other words, let's say we ignore my "firstly" above) ... is there a way
to paste the dataset on a temporary worksheet, and rearrange the columns
based on the header row, to be in the same order as the header-only columns
on my data page? I'm assuming it'd take some VBA. Something like, "start on
[prompt for sheet name in inputbox], A1; if the header cell (A1) matches a
cell on the other sheet [again, prompt for destination sheet name in inputbox]
A1 thru IV1, copy the cell below through 65535, and paste it under the
corresponding cell on the other worksheet (if there's more than one, paste it
under all of them); repeat for the column to the right; cycle through all
nonblank columns on first sheet." Oh, and don't take an hour or crash or run
interference with the Office clipboard.

Hey, answering one or both of my questions would help. Thanks.

 

[Barb Reinhardt]

It kinda depends on what you want to do. What is it you want to do? If
it's complicated, you get a complicated formula. You really haven't told us
what you do want, just what you don't want.
--
HTH,
Barb Reinhardt

I set up Excel "templates" for some pretty standard database extracts. It has
a summary sheet, and a data sheet. I paste new data in the data sheet, and
voila, all the formulas update in the summary sheet. The internal customers
love this, because it allows them to use whatever meshuggah format for
summaries they want, rather than a simple pivot table. But, the cells in my
summary end up with formulas like this:

=(SUMPRODUCT(--((ISNUMBER(SEARCH("Medicare",'Closed - Complaint'!Y1:Y65534))
=FALSE)*(ISNUMBER(SEARCH("Medicaid",'Closed - Complaint'!X1:X65534))=FALSE)*
('Closed - Complaint'!A1:A65534<>"")),--(('Closed - Complaint'!AF1:
AF65534="Fully Insured")+('Closed - Complaint'!AF1:AF65534="Shared Risk")+
('Closed - Complaint'!AF1:AF65534="At Risk")),('Closed - Complaint'!K1:K65534)
)+SUMPRODUCT(--(('Closed - Appeal'!A1:A65535<>"")*('Closed - Appeal'!AL1:
AL65535<>"MCRE")*('Closed - Appeal'!AL1:AL65535<>"MCAID")*('Closed - Appeal'!
C1:C65535<>"SND")*('Closed - Appeal'!AK1:AK65535="FI")*('Closed - Appeal'!$A
$1:$A$65535="Provider Dispute")),('Closed - Appeal'!$V$1:$V$65535)))/E6

Firstly, there's gotta be a better way than such a convoluted formula. I mean,
really, it's like I'm reproducing a SQL SELECT statement in Excel-eze, with
only 1,024 characters to get the job done. How would I do this with VLookup,
for instance?

Secondly, if my columns are out of order, it's been easier to rearrange the
100+ columns and put them in order, rather than edit all the formulas, and
possibly have something break. Let's say I were to continue using the formula
(in other words, let's say we ignore my "firstly" above) ... is there a way
to paste the dataset on a temporary worksheet, and rearrange the columns
based on the header row, to be in the same order as the header-only columns
on my data page? I'm assuming it'd take some VBA. Something like, "start on
[prompt for sheet name in inputbox], A1; if the header cell (A1) matches a
cell on the other sheet [again, prompt for destination sheet name in inputbox]
A1 thru IV1, copy the cell below through 65535, and paste it under the
corresponding cell on the other worksheet (if there's more than one, paste it
under all of them); repeat for the column to the right; cycle through all
nonblank columns on first sheet." Oh, and don't take an hour or crash or run
interference with the Office clipboard.

Hey, answering one or both of my questions would help. Thanks.

 

[ShaneDevenshire]

Hi,

First thing you could do to simplify this formula is to use range names, for
example:
Range
Name
Closed - Complaint'!Y1:Y65524 Y
'Closed - Complaint'!X1:X65534 X
'Closed - Complaint'!A1:A65534 A
'Closed - Complaint'!AF1:AF65534 AF
'Closed - Complaint'!K1:K65534 K
'Closed - Appeal'!A1:A65535 AA
'Closed - Appeal'!AL1:AL65535 AL
'Closed - Appeal'!C1:C65535 AC
'Closed - Appeal'!AK1:AK65535 AK
'Closed - Appeal'!$V$1:$V$65535 V

This would simplify your formula to:

=(SUMPRODUCT(--((ISNUMBER(SEARCH("Medicare",Y))=FALSE)*'(ISNUMBER(SEARCH("Medicaid",X))=FALSE)*(A<>"")),--((AF="Fully
Insured")+(AF="Shared Risk")+(AF="At
Risk")),(K))+SUMPRODUCT(--((AA<>"")*(AL<>"MCRE")*(AL<>"MCAID")*(AC>"SND")*(AK="FI")*(AA="Provider Dispute")),(V)))/E6


You can modify

ISNUMBER(SEARCH("Medicare",Y))=FALSE

to read:

NOT(ISNUMBER(SEARCH("Medicare",Y)))

and another similar change.

You can also create defined names for

"Medicare" Me
"Medicaid" Md
"Fully Insured" FI
"Shared Risk" SR
"At Risk" AR
"Provider Dispute" PD

And a minor change (K) with K and (V) with V
Reducing your formula to:

'=(SUMPRODUCT(--
NOT(ISNUMBER(SEARCH(Me,Y)))*NOT(ISNUMBER(SEARCH(Md,X)))*(A<>"")),--((AF=FI)+(AF=SR)+(AF=AR)),K)+SUMPRODUCT(--((AA<>"")*(AL<>"MCRE")*(AL<>"MCAID")*(AC>"SND")*(AK="FI")*(AA=PD)),V))/E6

From here you can always define any part of the formula as a name, for
example
NOT(ISNUMBER(SEARCH(Me,Y))) could be defined as MeY.

I may have made a mistake with a parenthesis in my sample formulas above,
but the idea should be clear.

2. You could also consider making a custom VBA fuction out of the entire
thing.

A quick way to rearrange the columns would be to insert a row above the
incoming data and enter a formula such as =MATCH(A2,Sheet2!$A1:$AZ1,0) where
A1:AZ1 is the row on the summary sheet which contains the titles. This
formula will return the numeric position of the matches, so a 1 would mean
that column should be in the first position on the second sheet. Now do a
Left to Right sort on the data in this sheet using the MATCH row as the sort
row. (Data, Sort, Options, Sort left to right).

Also, probably can do whatever you are trying to do with your formula with a
DSUM or DCOUNT function, take a look at those.

--
Thanks,
Shane Devenshire

I set up Excel "templates" for some pretty standard database extracts. It has
a summary sheet, and a data sheet. I paste new data in the data sheet, and
voila, all the formulas update in the summary sheet. The internal customers
love this, because it allows them to use whatever meshuggah format for
summaries they want, rather than a simple pivot table. But, the cells in my
summary end up with formulas like this:

=(SUMPRODUCT(--((ISNUMBER(SEARCH("Medicare",'Closed - Complaint'!Y1:Y65534))
=FALSE)*(ISNUMBER(SEARCH("Medicaid",'Closed - Complaint'!X1:X65534))=FALSE)*
('Closed - Complaint'!A1:A65534<>"")),--(('Closed - Complaint'!AF1:
AF65534="Fully Insured")+('Closed - Complaint'!AF1:AF65534="Shared Risk")+
('Closed - Complaint'!AF1:AF65534="At Risk")),('Closed - Complaint'!K1:K65534)
)+SUMPRODUCT(--(('Closed - Appeal'!A1:A65535<>"")*('Closed - Appeal'!AL1:
AL65535<>"MCRE")*('Closed - Appeal'!AL1:AL65535<>"MCAID")*('Closed - Appeal'!
C1:C65535<>"SND")*('Closed - Appeal'!AK1:AK65535="FI")*('Closed - Appeal'!$A
$1:$A$65535="Provider Dispute")),('Closed - Appeal'!$V$1:$V$65535)))/E6

Firstly, there's gotta be a better way than such a convoluted formula. I mean,
really, it's like I'm reproducing a SQL SELECT statement in Excel-eze, with
only 1,024 characters to get the job done. How would I do this with VLookup,
for instance?

Secondly, if my columns are out of order, it's been easier to rearrange the
100+ columns and put them in order, rather than edit all the formulas, and
possibly have something break. Let's say I were to continue using the formula
(in other words, let's say we ignore my "firstly" above) ... is there a way
to paste the dataset on a temporary worksheet, and rearrange the columns
based on the header row, to be in the same order as the header-only columns
on my data page? I'm assuming it'd take some VBA. Something like, "start on
[prompt for sheet name in inputbox], A1; if the header cell (A1) matches a
cell on the other sheet [again, prompt for destination sheet name in inputbox]
A1 thru IV1, copy the cell below through 65535, and paste it under the
corresponding cell on the other worksheet (if there's more than one, paste it
under all of them); repeat for the column to the right; cycle through all
nonblank columns on first sheet." Oh, and don't take an hour or crash or run
interference with the Office clipboard.

Hey, answering one or both of my questions would help. Thanks.