Compare data in cells

[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

 

[Don Guillett]

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.

 

[AG]

Please do post the solution on the forum. This will be useful.

 

[Ron Rosenfeld]

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

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

 

[Ron Rosenfeld]

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

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

 

[Joek]

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

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

This will save an enormous amount of time.

Joe

 

[Ron Rosenfeld]

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

This will save an enormous amount of time.

Joe

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