Extracting a value at an Intersection of Row and a column.

[Srikanth Ravada]

I wanted to extract a value at an intersection of row and a column from the dump which I've taken from a reporting tool.

Some Inputs:
In the attached file, data dump is in sheet1. In column A there are a list of plants in 4 digits (alphanumeric and a total of 21 plants in this case) and list of codes in 1st Row in 6 digits (a total of 49 codes in this case). A combination of one plant and code will be a cost center name. For example VJ91+COPSLA=VJ91COPSLA which is a cost center name.

Requirement:
When ever user inputs a cost center name like "VJ91COPSLA", the value at the intersection of plant VJ91 and code COPSLA should be retrieved.

Challenges:
1. The data dump taken from the reporting tool is not so formatted. The plant names have lot of spaces and so does the code names mentioned in 1st row, including inverted comas if the code starts with zero in the beginning.
2. The list of plants are not fixed. The number may go up/down.
3. The list of codes are also not fixed. The number may go up/down depending upon the data.

Initiative:
I've tried, tested and solved the problem to some extent (see sheet2) but I'm struck in pulling out the plants and codes automatically while just pasting the data into the sheet1.

The data should be prepared for each plant with combination of all 49 codes and like wise for all the other plants as well.

Final requirement:
I wanted to find out..

• either, how to pull out the plant and code names automatically with out formatting them, into separate columns, as i manually did for some in sheet2, with the help of a macro or an excel formula.
  
• or, a macro code to solve the entire problem as mentioned above.

and when the user inputs the cost center name(s) the value should be retrieved as in sheet3 for the user.

Thanks in advance!

Regards,

SRI

 

[vletm]

@Srikanth Ravada
Press [Do it]-button to get something...

 

[jindon]

I wanted to extract a value at an intersection of row and a column from the dump which I've taken from a reporting tool.

Requirement:
When ever user inputs a cost center name like "VJ91COPSLA", the value at the intersection of plant VJ91 and code COPSLA should be retrieved.


Thanks in advance!

Regards,

SRI

1) To ThisWorkbook code module.

Option Explicit

Private Sub Workbook_Open()
    Run Sheets("sheet3").CodeName & ".worksheet_activate"
End Sub

2) To Sheet3 code module.

Option Explicit

Private dic As Object

Private Sub Worksheet_Activate()
    Dim a, i As Long, ii As Long, txt As String
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    For i = 4 To UBound(a, 1)
        For ii = 3 To UBound(a, 2)
            txt = Trim$(a(i, 1)) & Trim$(a(1, ii))
            dic(txt) = a(i, ii)
    Next ii, i
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range
    Set rng = Intersect(Target, Rows("2:" & Rows.Count), Columns(1))
    If rng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In rng
        If r.Value = "" Then
            r(, 2).ClearContents
        Else
            If dic.exists(r.Value) Then
                r(, 2).Value = dic(r.Value)
            Else
                r(, 2).Value = CVErr(2042)
            End If
        End If
    Next
    Application.EnableEvents = True
End Sub

 

[Hui]

Your data in Sheet 1 Row 1 and Column 1 both have a lot of training blanks after the values

If you clean those up, then on Sheet3 B2 you can use:

=IFERROR(INDEX(Sheet1!$C$4:$AY$24,MATCH(LEFT(A2,4),Sheet1!$A$4:$A$24,0),MATCH(RIGHT(A2,6),Sheet1!$C$1:$AY$1,0)),"Not found")

 

[Srikanth Ravada]

@Srikanth Ravada
Press [Do it]-button to get something...

Hi Vletm

Thanks for macro. It worked. But only for the attached file. When i tried to do it on a diff data with same format. it throwed me an error saying variable not defined.

 

[Srikanth Ravada]

Your data in Sheet 1 Row 1 and Column 1 both have a lot of training blanks after the values

If you clean those up, then on Sheet3 B2 you can use:

=IFERROR(INDEX(Sheet1!$C$4:$AY$24,MATCH(LEFT(A2,4),Sheet1!$A$4:$A$24,0),MATCH(RIGHT(A2,6),Sheet1!$C$1:$AY$1,0)),"Not found")

Hi Hui,

Thank you very much for the reply. It worked by trimming the plant names and code names. But i do not wanted to disturb the data in sheet1.

But your formula simplified my workings in sheet2 and sheet3.

 

[vletm]

@Srikanth Ravada ... variable not defined
Do You have text 'Option Explicit' in Your another file?
If 'Yes' then You have two choices
a) You need to define all variables
b) You need to delete 'Option Explicit' -text away