Compare data in cells

  • Thread starter Thread starter Joek
  • Start date Start date
J

Joek

I have downloaded data in to excel.

In column A are the Feild names in column B are the values. However if a
value was not present in the initial report then the field did not come
across.

Example
Col A Col B
1 Item A;Item B;Item C;Item D; Item E 10;20;30;40;50
2 Item A;Item B;Item D; Item E 15;25;35;45

I need to find the relevant value for Item D in column B. so fom the
example I need the fourth value from B1 and the third value from B2. I hope
this makes sense.

Any help would be greatly appreciated
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.
 
I have downloaded data in to excel.

In column A are the Feild names in column B are the values. However if a
value was not present in the initial report then the field did not come
across.

Example
Col A Col B
1 Item A;Item B;Item C;Item D; Item E 10;20;30;40;50
2 Item A;Item B;Item D; Item E 15;25;35;45

I need to find the relevant value for Item D in column B. so fom the
example I need the fourth value from B1 and the third value from B2. I hope
this makes sense.

Any help would be greatly appreciated
Click to expand...

You can certainly do this with a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=FieldValue($C$1,A1,B1)

where

C1: Field Name that you want to extract
A1: List of semicolon delimited Field Names
B1: List of semicolon delimited Values


================================================
Option Explicit
'make case insensitive
Option Compare Text
Function FieldValue(FieldName As String, sFieldNames As String, _
sValues As String) As Variant
Dim sFn() As String, sV() As String
Dim i As Long

sFn = Split(sFieldNames, ";")
sV = Split(sValues, ";")
FieldName = WorksheetFunction.Trim(FieldName)

For i = 0 To UBound(sFn)
If FieldName = WorksheetFunction.Trim(sFn(i)) Then
Exit For
End If
Next i

If i > UBound(sFn) Then
FieldValue = CVErr(xlErrNA)
Else
FieldValue = sV(i)
End If
End Function
=================================

As written, this returns "Value" as a string. If it will always be a numeric
value, you can coerce the result to be numeric with the CDbl function.

===================
....
If i > UBound(sFn) Then
FieldValue = CVErr(xlErrNA)
Else
FieldValue = CDbl(sV(i))
End If
....
======================
--ron
 
I have downloaded data in to excel.

In column A are the Feild names in column B are the values. However if a
value was not present in the initial report then the field did not come
across.

Example
Col A Col B
1 Item A;Item B;Item C;Item D; Item E 10;20;30;40;50
2 Item A;Item B;Item D; Item E 15;25;35;45

I need to find the relevant value for Item D in column B. so fom the
example I need the fourth value from B1 and the third value from B2. I hope
this makes sense.

Any help would be greatly appreciated
Click to expand...

This can also be done with a formula, but it is somewhat more obscure:

=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(B2&";",";",CHAR(1),
LEN(LEFT(A2,SEARCH($C$1,A2)))-LEN(SUBSTITUTE(LEFT(A2,SEARCH(
$C$1,A2)),";",""))+1),FIND(CHAR(1),SUBSTITUTE(B2&";",";",
CHAR(1),LEN(LEFT(A2,SEARCH($C$1,A2)))-LEN(SUBSTITUTE(
LEFT(A2,SEARCH($C$1,A2)),";",""))+1))-1),";",REPT(" ",99)),99))

Again

C1: Field Name
A2: List of Field Names
B2: List of Field Values
--ron
 
Ron Rosenfeld said:
You can certainly do this with a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=FieldValue($C$1,A1,B1)

where

C1: Field Name that you want to extract
A1: List of semicolon delimited Field Names
B1: List of semicolon delimited Values


================================================
Option Explicit
'make case insensitive
Option Compare Text
Function FieldValue(FieldName As String, sFieldNames As String, _
sValues As String) As Variant
Dim sFn() As String, sV() As String
Dim i As Long

sFn = Split(sFieldNames, ";")
sV = Split(sValues, ";")
FieldName = WorksheetFunction.Trim(FieldName)

For i = 0 To UBound(sFn)
If FieldName = WorksheetFunction.Trim(sFn(i)) Then
Exit For
End If
Next i

If i > UBound(sFn) Then
FieldValue = CVErr(xlErrNA)
Else
FieldValue = sV(i)
End If
End Function
=================================

As written, this returns "Value" as a string. If it will always be a numeric
value, you can coerce the result to be numeric with the CDbl function.

===================
....
If i > UBound(sFn) Then
FieldValue = CVErr(xlErrNA)
Else
FieldValue = CDbl(sV(i))
End If
....
======================
--ron
Click to expand...
Thank you Ron! Absolutely Fabulous!! Respect is due!!!

This will save an enormous amount of time.

Joe
 
Thank you Ron! Absolutely Fabulous!! Respect is due!!!

This will save an enormous amount of time.

Joe
Click to expand...

You're welcome, Joe. Glad to help.

Also take a look at the formula solution in case VBA is prohibited where this
will be used.
--ron
 
Back
Top