match formula question, maybe?

[achi]

hi,
I need help with a formula or coding,preferbably to do the following:
I have data that is scraped from a 3270 emulation session that parses certain infomation into an Excel sheet.
There are 6 rows with 2 columns (A13:B18).
What i need is to take the value from C13 and place it in Column D if certain conditions are met in Columns A & B.
For Column A, if there is only 1 row of data, then check the value in Column B. If the data in column B does not match the value in C13, then place C13 in the corresponding row in Column D.
However, if there are more than 1 row of data in Column A, then only certain rows will be considered.
If there is a mixture of values that appear from Column F13:G18, then only the values from Column F13 will be considered.
Column B will always have a value if Column A has a value.
I then take the value from Column D and parse it back to the 3270 session.
I have over 1000 records to look at and that's why I need to come up with a macro to ease my fingers & eyes to accomplish the above task.

I have racked my brains trying to come up with a formula. I don't even know how to record a macro such as this.

can anyone please help me with this dilemma?


thanks
achi

[achi]

scraping data to & from the 3270 session is not a problem.

[mdmackillop]

Hi Achi,
Welcome to VBAX.
A well explained problem really helps. I just hope I understood all the complexities.
I'm sure there is a formula, but I'll leave that to someone else. Here's a UDF solution. Enter the following code in a standard module. Enter "=LeaTest(A13:B13) into cell D13 and copy down.
Function LeaTest(data As Range)
Dim Chk As Long
'Check for blank
If data(1) = "" Then
LeaTest = ""
Exit Function
Else
'Check for match with Column F
On Error Resume Next
Chk = Application.WorksheetFunction.Match(data(1), Range("F13:F18"), 0)
Select Case Chk
'No match
Case 0
LeaTest = ""
'Match
Case Else
'No match with C13
If data(2) <> Range("C13") And data(2) <> "" Then
LeaTest = data(2)
'Match with C13
Else
LeaTest = ""
End If
End Select
End If
End Function


BTW, if may be possible to automate the checking further. Please post in with sample data and criteria if you would like help with this.

[vzachin]

thanks mdmackillop,

it's very interesting how you did this! i'm still at a learning stage.
i modified the coding slightly from

LeaTest = data(2)


to

LeaTest = Range("C13")


The coding works for the values in Range("F13:F18"), but I need to also consider the values in Range("G13:G18"). If the values in Range("G13:G18") appear in data(1) without any values from Range("F13:F18"),
then

If data(2) <> Range("C13") And data(2) <> "" Then
LeaTest = data(2)
'Match with C13
Else
LeaTest = ""
End If


can you help me with this?

thanks
achi

[mdmackillop]

Try

Chk = Application.WorksheetFunction.Match(data(1), Range("F13:G18"), 0)

although you say here
"if Column A contains AB,AD,C & if B13<>C13, place C13 in D13 & if B14 <>C13,place C13 in D14. A15 will not be updated"
which implies C in column A will not "update", if I understand correctly.

BTW the variables data(1) and data(2) refer to the first cell and second cell included in the function brackets, so they change in each row.

Edit. I didn't test and that type of match won't work

[mdmackillop]

There is a problem with your data in that C, D, M and V are in fact 2 characters long so a simple match may fail. It must be to do with your imported data. Try it on the spreadsheet with a Match formula.

[vzachin]

hi,

Range("F13:G18") won't work, so i move the data from Col G to Col F and made the as Range("F13:F22").
The original data in Column G was actually 2 characters, C space,D space,M space and V space.
All I need now is if C,D,M or V is in column A with one of the following:AB,AD,AE or ZZ, then C,D,M or V will not "update" and be ignored.
I think i left out this most important criteria.yikes!

thanks again for your time
achi

[mdmackillop]

If C,D,M & V are the only characters to be cheched against, it would be simpler to hard code these in an array.
Try

Function LeaTest(data As Range)
Dim Chk As Long
Dim DontUpdate, Tmp
DontUpdate = Array("C", "D", "M", "V")
'Check for blank
If data(1) = "" Then
LeaTest = ""
Exit Function
Else
'Check for match with Column F
On Error Resume Next
tmp = Application.WorksheetFunction.Match(Trim(data(1)), DontUpdate, 0)

If Not (tmp) = Empty Then
LeaTest = ""
Exit Function
End If
If Not (Application.WorksheetFunction.Match(data(1), Range("G13:G18"), 0)) = Empty Then
If data(2) <> Range("C13") And data(2) <> "" Then
LeaTest = Range("C13")
Else
LeaTest = ""
End If
End If
End If
End Function

[Shazam]

Hi achi,


Here is a formula below I'm not fully sure if it?s exactly what you're looking for.

Input formula in cell D13 and copied down.

=IF(B13=LOOKUP(REPT("z",255),C$13:C13),"",IF(SUMPRODUCT(--($F$13:$G$16=A13))>0,LOOKUP(REPT("z",255),C$13:C13),""))


Hopefully it helps you.

[achi]

hi Shazam,

Nice formula but it didn't work for me. All the values in Column A got updated.

achi

[Shazam]

Hi achi,



Can you post a couple example worksheets with the expected results?