Posts by EgoProwler

Re: If Then Conditions

Quote from ByTheCringe2

There are no formulas in that example...

F12 contains the formula - I double checked the example -- it's there.

=IF(ISNA(INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))),"OUTSIDE MATRIX",INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))*100)

I'm looking for some guidance / feedback on how to best handle this situation. See attached sheet for an example.

I have a matrix table where I perform and MATCH and INDEX based on 2 input cells, X and Y in the example. Currently, when a result falls outside the bounds of the table the words OUTSIDE MATRIX appear in the Result cell.

My desired result is to assign a fixed result when the following conditions exist, rather than the words OUTSIDE MATRIX. I'm guessing that some nested IF's will handle the problem and eliminate the need for ISNA in my current formula. Is there a more elegant solution or is the nested if the way to go?

IF X < 650 and Y > 240 THEN 1.00%
IF X > 650 and Y > 240 THEN 1.00%
IF X > 650 and Y < 240 THEN 1.25%

Thanks in advance for the help and guidance. I'll say that my brain starts to shutdown with long complex IF/THEN statements and I'm not sure how to implement it with a MATCH and INDEX also.

Re: Index And Match

Quote from Dave Hawley

I don't get it, nothing new there If the result is not found, how does the formula you show return a blank??

Was my mistake. The cell does return #N/A when the result is outside the bounds of the table.

Quote from Batman

For info, if you are doing a standard VLookup that can't find what it's looking for VLookup will return a #N/A error. In these circumstances you should use ISNA instead of ISERROR. ISNA checks for the formula answer being #N/A and if it is actions accordingly. ISERROR checks for any error result, which could be because you have coded the formula incorrectly, and is therefore best avoided unless you have no alternative.

Excellent information. Thanks! You guys are the best. I learn so much every time I come here.

Re: Index And Match

Excellent! I wasn't sure if ISERROR would be the correct solution, but it didn't seem to me that it would be because the result I was getting in my sheet wasn't NA. I have other ISERROR formulas in my sheet to handle this type of result, but didn't know if it would work in the INDEX | MATCH scenario.

Thanks, Batman!. This is exactly what I was looking for. Thanks for the info about the double calculations -- good information to know. Luckily, this calculation is only done in one result cell.

I have a formula that performs an INDEX | MATCH on a table of data based on two input cells. If the inputs are outside the bounds of the data table the result is blank.

The data tables size and contents are static. The inputs used to perform the INDEX and MATCH are [dr]*[/dr].

=INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))*100

Could this be easily solved by introducing and IF statement that if the resulting INDEX and MATCH is outside the bounds of the data table the result would appear as '0' (zero)?

Thanks in advance.

Re: Dynamic Range - Text To Columns

I ran the macro but it asks: "Do you want to replace the contents of the destination cells?"

If I choose OK it does not perform the Text To Columns properly and if I choose Cancel it says: "Run-time error '1004': TextToColumns method of Range class failed."

How would I modify this macro to handle a [dr]*[/dr] In the example below A27 is the last row in my data set, but its last row number is dynamic.

I'm using this code just after finding the last row in the dataset, but found it breaks if the last row doesn't fall in the A27 range.

I find the last row prior to executing the TextToColumns code.

Range("A65536").End(xlUp).Select

Sub TextToColumns()
    Selection.TextToColumns Destination:=Range("A27"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
End Sub

Thanks in advance!

Re: Find And Replace Macro

Quote from Simon Lloyd

How would you know what their "Proper" columns would be?

Using the example in the sheet I attached this is what should happen with the data. Should the splitting apart of the data be the more functional approach rather that the Find/Replace:

Once un-merged, A4 "MTD Chris CA Total:" would be split as follows into cells in the same row.

MTD --> Column A
Chris --> Column B
CA --> Column C
Total: --> Column D

Quote

Well which one was it? they cant all be correct!

MTD next cell 1234 next cell XYZ would be the correct format. Doh! I didn't delete your other example to eliminate the confusion

I know this says find/replace, but I'm also looking that there may be a more functional approach. I've been looking at it from the find/replace stand point since I started this little excercise.

Re: Find And Replace Macro

Quote from Simon Lloyd

Can you post a sample workbook?, when you say if the cell in Column A has more than MTD spread the rest amongst the other cells, how do you envisage that? i.e MTD1234XYZ should it bet MTD next cell 1 next cell 2 etc? or MTD next cell 1234 next cell XYZ?

There are too many suppositions in your description, please post your workbook to get a fuller and more useful response.

I dont think its a "Find And Replace Macro" as your thread title suggests, it could possibly be taken care of with some worksheet functions.

Regards,
Simon

Display More

Simon, thanks for your reply. I'll summarize the requirements. Line item 3 has two possible solutions that would work for my requirements.

I am looking for a Find and Replace option because it will suit my needs, however, I'm looking maybe for a more elegant approach suggestion if it exists.

• Delete blank rows. SOLVED
• Un-merge merged cells. SOLVED
• Any cell in column A that is anything other "MTD" can either be replaced with the word "MTD" or split across adjacent cells into their proper columns.
• Any cell in column D that is blank to have work "TOTAL:" placed in it. OPTIONAL based on solution used in option 3.

• End user pastes data into sheet starting at A2. The structure of the data will always be the same, only the contents of the data changes.
• Formatting of the data with VBA must be completely automatic once they paste the data then click the Format Page button.

Quote

i.e MTD1234XYZ should it bet MTD next cell 1 next cell 2 etc? or MTD next cell 1234 next cell XYZ?

Yes, you have that correct.

Re: Find And Replace Text Macro

Quote from Simon Lloyd

This little setup below asks the user to enter the text to be replaced and the text to change it to!

Code

Sub Macro1()
Dim IB, IB1 As Variant
IB = InputBox("Enter Text To Be Replaced", "Find And Replace")
IB1 = InputBox("Enter Replacement Text", "Find And Replace")
    Cells.Replace What:=IB, Replacement:=IB1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

as for your fill blanks do you only want "total" in column D if there is a value in Column A or does it not matter? when you say "40 rows or so" how many is the so is theer ather data in the rows?

Regards,
Simon

Simon and Dave, thank you for your time. Any row in column A should only have "MTD". Any blank cell in column D after the removal of blank rows and un-merging should have the words "TOTALS:". Another thing I just thought of that would be very helpful to me is taking the un-merged data in any row in Column A and splitting it's contents across it's relative cells to the right as well.

To summarize, any cell in column A that has text more than just "MTD" should be split apart and fill in the cells to the right in the same row. This may be a more elegant solution?

Unfortunately, the macro has to be completely automated -- no user intervention with Find/Replace dialogs popping up.

As far as the 40 rows of data goes this is an estimate. Basically, the end user will paste data in from another sheet and the number of rows may exceed what I have in the example. I only mentioned this because I needed the macro to only loop through rows that contain data.

The whole point of this for me is to format the data to be extracted to another worksheet using other Excel formulas (HLOOKUP, VLOOKUP, etc.).

Thanks in advance for any help.

I'm struggling with this one and can't seem to find a clear enough answer for me on the forums to accomplish this. I'll say that I have difficulty getting my hands around VBA so please be gentle

Here is what I have accomplished so far with the existing VBA I have written in the sheet. It basically removes blank rows and un-merges any merged cells.

'Removes blank rows
Public Sub DeleteBlankRows()


Dim r As Long
Dim c As Range
Dim Rng As Range


On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


If Selection.Rows.Count > 1 Then
    Set Rng = Selection
Else
    Set Rng = ActiveSheet.UsedRange.Rows
End If
For r = Rng.Rows.Count To 1 Step -1
    If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
        ActiveSheet.Rows(r).EntireRow.Delete
    End If
Next r


'Unmerge any merged cells
    With Columns("A:A")
            .UnMerge
            .HorizontalAlignment = xlLeft
    End With


EndMacro:


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub

Once this is accomplished I need to be able to replace the text "MTD Test <insert state> Total:" from each row of column A. Furthermore, I need to fill any blank cells by row in column D with the words "TOTALS:" after the previous 3 macros run.

I have successfully filled the blank cells using the following code, but I'm not sure how to implement it without setting a static range in the macro.

Sub FillBlanks()
    Range("D1:D40").SpecialCells(xlCellTypeBlanks).Value = "TOTALS"
End Sub

The sheet should only fill the first 40 rows or so with data, but I need the macros to loop through each row and make the changes then stop -- without any manual selections required.

Re: COUNTIF Multiple Criteria using ENTIRE column as selection

Quote from Aaron Blood

They're all facinating examples, and wonderfully clever, but there all basically future trainwrecks and I tend to avoid them. But that's me speaking from my background and experiences mostly in the business/finance/accounting world world.

Aaron, thanks for the reply. We all know that there is more than one way to skin a cat and based on your practical experience -- skinning the cat the way suggested hasn't worked well when building complex applications. It makes perfect sense to me, thanks for clarifying your position.

What I gather from your reply is using plain ole cell references rather than named ranges is plenty sufficient and named ranges is merely a fancy way of accomplishing the same result.

Re: COUNTIF Multiple Criteria using ENTIRE column as selection

Quote from Aaron Blood

500 rows? Why even bother with the named range overhead? Ah well, it's you're app...

Aaron, was my error. My old data set prior to this rebuild was the 500 range size. Currently, I've consolidated multiple sheets into one and there are over 3000 rows.

This is personal use only and I know nothing about how the name range will effect overhead. I'm familiar with the concept, but not how it relates to Excel. However, you've peaked my interest and I'd like you to elaborate on this 'overhead' discussion.

Re: COUNTIF Multiple Criteria using ENTIRE column as selection

Quote from norie

As far as I know you can't use whole columns with SUMPRODUCT.

If the range is constantly changing why not use a dynamic named range.

If you search the board I'm sure you'll find many examples.

I didn't even think of using a Named Range. This would actually work perfectly. The data set I'm using for any given month should never exceed 500 rows of data.

I actually made this more difficult then it needed to be. Thanks for the help!

I searched through the threads related to countif with multiple criteria to find my answer and came up empty. Is it possible to have the selection be an entire column, A:A and K:K, in this example? Rather than a defined column/row range?

=SUMPRODUCT(--('2005'!A$2:A$2344="January"),--('2005'!K$2:K$2344<575))

When I choose the entire column the result is #NUM!. This formula works for my previous years data sheet, 2005, because no more data is added to it.

However, 2006 will have data added to it on a daily basis and I don't want to have to change the selection each time to get the accurate results.

Thanks in advance!

Re: Logical Function - Math results using text and numeric data

I knew I was close! Thanks for the help - this did the trick

I'm close to getting my desired result, however, I'm stuck on one little part. I'll use row 7 as the example.

Attached sheet.

The cells in Column P can be one of three things based on a validation list: Aggregate, 70%, and 75%. I could use 100% and fix this whole little dilema, but Dealer Participation (Column Q) can never be 100%.

Q7, when the cell data in P7 = 70% or 75% gives a numeric result. However, one of the nested IF statements performs math against one of the data types in P7 that is text based - resulting in a #VALUE! error.

In a nutshell, what I'm trying to accomplish is the following. I think I'm close on my formula:

Q7 = W7+O7 if P7 = "Aggregate"
Q7 = W7 * P7

Remember, P7 can equal 70%, 75% or Aggregate. The formula bombs out when P7 equals "Aggregate".

Thanks in advance.

Re: Data Validation - Formula Trouble

Quote from EgoProwler

Excellent! This works perfect! One other question that just came to mind.

Is there any logic that can be added to address this scenario.

Flat Paid is data entered into C2 when B2-A2 = 0%. If user changes the buy and/or sell rate to be > 0%, is there a formula that will wipe out the Flat Paid cell (C2)?

OK. After some reading I see that some VBA code will handle what I want to do. However, I have no experience at all with VBA code. I've seen it, understand some of it, but not enough to hammer this snippet out to perform my action.

Based on the most recent attached example the code should handle the event as such:

• When B2-A2 = 0, data entry is allowed into C2 (Flat Paid).
  

  
  

• IF after a value is entered into C2, the difference between B2-A2 > 0, the data in C2 needs to be cleared.
  

  
  

  Basically, if the user comes back later and changes the buy/sell rates and that difference is > 0, the data entered into C2 should be wiped out.
  

Hope this makes sense and thanks for the help. You guys truly are the best!

Re: Total of 2 conditions

I think I understand what you are trying to do. For each ROW, you want to count the number of times a 1 appears in any of the columns (J5:AF5).

See attached example. It uses COUNTIF to count the number of times the number 1 appears in the range.

Re: Total of 2 conditions

Not sure that I totally understand what you want, but based on what you've explained - see attached sheet.

Uses COUNTIF