matching the column header and reading the value from the other sheet

[sharmila Krishnamurthy]

Hi

Request your help in fixing the code for the below task

Sheet 1
Account Header	HR	Finance	Sales	Crdit	collection
Operation cost Trichy
Peety cash of Trichy
Employee cost of Trichy
Operation cost Madurai
Peety cash of Madurai
Employee cost of Madurai

Sheet 2
Dept	Account Header	Closing Balance
HR	Operation cost Trichy	5000​
Finance	Peety cash of Trichy	7000​
Sales	Employee cost of Trichy	9344​
Crdit	Operation cost Trichy	15547​
collection	Peety cash of Trichy	15175​
HR	Employee cost of Madurai	12317​
Finance	Operation cost Madurai	5248​
Sales	Peety cash of Madurai	10201​
Crdit	Employee cost of Madurai	13988​
collection	Employee cost of Madurai	17274 ​

Sheet 1 to be populated with the closing balance in sheet1 matching the department and the account header from the sheet2

 

[vletm]

sharmila Krishnamurthy
You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

You seems to skip few basic things, which would help You and those who'll try to help You.
How to get the Best Results at Chandoo.org
eg For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.

 

[AlanSidman]

Is this what you are looking for?

Data Range

	A​	B​	C​	D​	E​	F​
1​	Account Header​	HR​	Finance​	Sales​	Crdit​	collection​
2​	Employee cost of Madurai​	12317​	​	​	13988​	17274​
3​	Employee cost of Trichy​	​	​	9344​	​	​
4​	Operation cost Madurai​	​	5248​	​	​	​
5​	Operation cost Trichy​	5000​	​	​	15547​	​
6​	Peety cash of Madurai​	​	​	10201​	​	​
7​	Peety cash of Trichy​	​	7000​	​	​	15175​

If yes is the answer, then here is the Mcode from using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Account Header", type text}, {"Closing Balance", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Dept]), "Dept", "Closing Balance")
in
    #"Pivoted Column"

 

[rahulshewale1]

hi @sharmila Krishnamurthy ,

Option Explicit

Sub lookup_Data()

Call ClearMacro

Dim myarray() As Variant
Dim loop1 As Long, lrow As Long, lcol As Long, Smallloop As Long
myarray = Sheet2.Range("A1").CurrentRegion

lrow = Sheet1.Range("A1000").End(xlUp).Row
lcol = Sheet1.Range("a1").End(xlToRight).Column
Dim Deptvalue As String, Header As String
Dim result As Long
Dim bigloop As Long
Dim rownumber As Long

For bigloop = 2 To 7


    For loop1 = 2 To 6
    
        Deptvalue = Cells(1, loop1).Value
        Header = Cells(bigloop, 1).Value
        
        For Smallloop = 1 To UBound(myarray)
    
                    
            If Deptvalue = myarray(Smallloop, 1) And Header = myarray(Smallloop, 2) Then
            
                result = myarray(Smallloop, 3)
                
                Cells(bigloop, loop1).Value = result
            
            End If
    
    
        Next Smallloop
                              
    
    Next loop1

Next bigloop

End Sub

Sub ClearMacro()
'
' Macro3 Macro
'

'
    
 
    Columns("A:F").Select
    Selection.ClearContents
    Range("A1").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Sheets("Sheet2").Range("B1:B11").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("A1"), Unique:=True
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Sheets("Sheet2").Range("A1:A11").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("BS1"), Unique:=True
    Selection.End(xlToRight).Select
    Selection.Offset(1, 0).Select
    
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlToLeft).Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Columns("G:G").EntireColumn.AutoFit
    Columns("G:G").ColumnWidth = 5.73
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("F1").Select
End Sub

 

[sharmila Krishnamurthy]

Thanks Rahul for the code snippet

Getting the error as "Run time error 1004 application defined or object defined error

Below is my code
Dim myarray() As Variant
Workbooks("Consolidated_TBV1A.xlsx").Activate
Range("A4").Select
myarray = ActiveSheet.Range(ActiveCell).CurrentRegion.Value

Please do help

 

[AlanSidman]

Did you test the Power Query Solution in post #3?

 

[rahulshewale1]

Thanks Rahul for the code snippet

Getting the error as "Run time error 1004 application defined or object defined error

Below is my code
Dim myarray() As Variant
Workbooks("Consolidated_TBV1A.xlsx").Activate
Range("A4").Select
myarray = ActiveSheet.Range(ActiveCell).CurrentRegion.Value

Please do help

HI @sharmila Krishnamurthy ,

what's is expecting output ? Please share output result so i shall you .

 

[sharmila Krishnamurthy]

output is to populate the value in another matching the Department and Particular.

I am trying the code which was given by you

In that , while assigning the array, getting the error as

Getting the error as "Run time error 1004 application defined or object defined error

 

[sharmila Krishnamurthy]

Did you test the Power Query Solution in post #3?

having no idea about the power query, hence I haven't try the power query so far and . Hne, Thanks for your response

 

[AlanSidman]

Click on the link in my signature block. PQ is a powerful piece of Excel that helps to manipulate data. Easy to learn and no coding required.

 

[sharmila Krishnamurthy]

Thank you so much, will do

 

[Marc L]

Hi,​

​

in case you are still in trouble even with PQ :​

Sheet 1 to be populated with the closing balance in sheet1 matching the department and the account header from the sheet2

… at least an attachment reflecting exactly your need would be clever as there is no sheet2 in your previous attachment !​

Better a before workbook and an after workbook for the expected result in order all is clear with nothin' to guess …​

​